MYSQL（03）-锁

2019-07-20 本文已影响22人小亮__

MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类

全局锁

全局锁就是对整个数据库实例加锁，mysql提供了命令FTWRL（Flush tables with read lock）来开启全局锁，当开启全局锁的时候，整个数据库只能够进行读操作，数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等），和更新类事务的语句都不能提交

应用场景：

全局锁一般应用在数据库备份的时候，这样只有读操作可以进行

疑问

为什么使用(set global readonly=true)，来设置数据库只读，他和全局锁有什么区别，因为如果使用FTWRL在客户端链接出现错误的时候，全局锁会自动释放，而（set global readonly=true）不会释放，导致只读时间长
为什么不使用mysqldump参数single-transation，在导出数据之前开启事务，通过MVCC机制保证读取的时候一致性？因为Mysql不是所有的存储引擎都支持事务，例如MYISAM

表级锁

表级锁分两类，表锁和元数据锁（MDL-meta data-lock）

表锁：可以使用lock tableName ...read/write 进行上锁，客户端断开链接的时候自动释放，也可以通过 unlock tables 手动释放
MDL：不需要显示使用，访问表的时候会自动带上，主要是DDL语句和DML查询更新语句之间进行加锁，保证表结构更新时，DML等语句堵塞

应用场景

在DDL更新表的时候，因为需要便利所有的表数据，所以一般我们执行的时候都比较小心，或者干脆交给DBA来执行，但是在DDL小表的时候，我们可能会掉以轻心，如果DDL的表比较小，但是访问量很大，那么使用不当一样会出现问题，在DDL的时候，会增加MDL锁这时，所有的查询请求都会堵塞，如果客户端有重试机制，那么请求超时马上会在创建一个session，这时会很快占用满数据库的连接，这时应该给DDL语句增加一个超时时间，如果执行的时间比较长则直接超时断开，不要影响后面的请求

行级锁

MYSQL的行锁是引擎实现的，就是对数据的行进行加锁，MYISAM不支持行锁，这也是为什么MyIsam会被Innodb替代的一个主要原因。行锁在 InnoDB 中是基于索引实现的，所以一旦某个加锁操作没有使用索引，那么该锁就会退化为表锁。下面的介绍都是基于Innodb引擎来实现的

行锁（Record Locks）

行锁也叫记录锁，就是为某行记录加锁，它封锁该行的索引记录。（SELECT * FROM table WHERE id = 1 FOR UPDATE）时，id 为 1 的记录行会被锁住。需要注意的是：id 列必须为唯一索引列或主键列，否则上述语句，中就会从行锁退化成间隙锁。

间隙锁（Gap Locks）

间隙锁基于非唯一索引，它锁定一段范围内的索引记录。当更新语句更新一定范围的数值的时候，例如update user set del_flag = 1 where age > 100 （逻辑删除年龄大于100岁的人），这时如果事务的隔离级别设置为 $\color{red}{可重复读}$ 的时候，则会增加间隙锁，其他DDL语句无法执行，当然如果事务的隔离级别如果是读提交的话，则可以成功。
详细原理：https://www.jianshu.com/writer#/notebooks/38437209/notes/51011877/preview

二阶段锁：

二阶段锁就是只锁操作分为两个阶段：加锁阶段与解锁阶段，innodb数据库的行锁的生成时机是在需要的时候增加锁，例如执行（update）更新语句执行的时候，而锁的释放是在commit的时候。举个案例，用户A消费买电影票，并记录消费日志。这时用户B也买电影表。这时用户A：

1.用户A金额表数值减少
2.影院金额表增加
3.消费日志表增加记录
用户B的执行操作
1.用户B金额表数值减少
2.影院金额表增加
3.消费日志表增加记录

他们都会对影院金额表的同一行进行操作，这时，把影院金额表的更新操作放在最后执行（1，3，2）这样的顺序执行，影院金额这行的行锁就锁的时间最少，语句的执行就会更高。总结一下就是：如果一个事务中需要锁多个行，那么尽量把最可能造成锁冲突，最可能影响并发度的锁放的申请时机放在最后

死锁和死锁检测

行解锁，当一个事务中，已经获取到了行A的锁，去获取行B的锁，而另一个事务中，已经获取到了行B的锁，等待获取行A的锁。这时就会造成死锁，如下图。解决死锁的方式有两种一种是增加锁的超时时间，另一种是增加死锁检测

死锁的超时时间

死锁的超时时间可以通过innodb_lock_wait_timeout来设置的，默认是50S。但是如果并发更新量很大，那么这么长的等待时间业务肯定是不能接受的，而如果设置的时间过短，又会影响正常的锁等待。所以对于死锁的操作还有如下的解决方案

死锁检测

死锁检测可以通过innodb_deadlock_detect=on来设置，默认是开启的。他是通过每次开启事务，都检测一下，开启的事务是否是被别的线程等待，如果出现循环等待也就是死锁的时候，直接将其中一个事务进行回滚，释放持有的一个锁。但是这种操作会占用大量的CPU资源，所以如果这种情况很多会造成数据库的CUP使用率很高，但是却没有执行多少个事务。我们可以通过以下方式解决

将操作通过中间件（redis分布式锁）。进行顺序操作，从而减少并发事务
将多个事务操作进行业务上的合并，例如影院金额行增加金额，可以在业务中对5分钟之内的数据取和然后操作数据，从而减少锁冲突的概率