MySQL的锁与锁机制

在IT行业这个发展更新速度很快的行业，只有不停止的学习，才不会被行业所淘汰。如果你是数据库学习者，那么本文《MySQL的锁与锁机制》就很适合你！本篇内容主要包括MySQL的锁与锁机制，希望对大家的知识积累有所帮助，助力实战开发！

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

全局锁

全局锁指的是对整个数据库实例加锁，MySQL提供了FLUSH TABLE WITH READ LOCK，用于给全整个数据库实例加读锁。这个命令执行后，整个实例就变成只读了，增删改的DML语句与建表增加索引等DDL语句都会被堵塞。可以使用UNLOCK TABLES解锁。
整个数据库实例都变成自读了，想想就多么可怕，但是在MyISAM时代，由于MyISAM不支持事务当我们需要做全库备份的时候，只能使用这个语句，使得备份期间整个库只能是只读的，从而使得备份出来的库是一致性的。
但是对于INNODB由于支持事务，在可重复读级别下，事务开始后，会创建一个一致性视图，那么备份出来的数据，就是一致的。我们可以使用mysqldump工具添加–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

表锁

可以通过如下的语句添加表锁：

LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] ... 
lock_type: { READ [LOCAL] | [LOW_PRIORITY] WRITE } 
UNLOCK TABLES

读锁与读锁直接可以并发，读锁与写锁、写锁之间只能互斥。对表加读锁后，自己也不能对其进行修改；自己和其他线程只能读取该表。 当对某个表执加上写锁后（lock table t2 write），该线程可以对这个表进行读写，其他线程对该表的读和写都受到阻塞。现今MySQL都使用了INNODB作为默认的存储引擎了，MyISAM已经很少使用了，更多细节就不在赘述。

元数据锁（meta data lock，MDL)

MDL不需要手动加锁，当访问数据库表的时候（增删改查的DML语句），会自动加上读锁；当修改数据库表的时候（DDL语句），会给数据库加上写锁。MDL锁是为了保证数据的一致性，想想如果我们在查数据库表的时候，如果通过DML语句删除了表的一列，那么数据库应该返回什么呢？与表锁类似，读锁与读锁之间可以并发，读锁与写锁、写锁之间只能互斥。但是如果在DML期间都要加写锁，那么在持有写锁期间整个库不可读写，如果DDL是一个大表，那么是多么可怕的，为此MySQL引入了在线DDL。
更多online DDL的细节参见MySQL官方文档：online-ddl-index-operations
在线DDL只能解决DDL期间堵塞增删改查的问题，但是DDL在总是需要短暂的获取MDL写锁的，那么对于热点表，在获取到写锁前，需要等待其他事务提交或者回滚。那么对于热点表，在这之后的DDL语句都会被堵塞这也是没法接受的。此时有如下选择：
1、如果热点表有明显的业务高峰期与低峰期，可以选择在低峰期执行online DDL；
2、只能使用先在备库执行DDL(需临时关闭binlog)，执行完后进行主备切换，然后在旧的主库执行DDL(同样需临时关闭binlog)；
3、InfoQ上的gh-ost或者github/gh-ost
另外需要说明的是，虽然online DDL在DDL期间可以执行DML语句，但是DDL本身是一个重IO与CPU的操作，还是要选择业务低峰期执行。

INNODB行锁

MySQL的行锁是由存储引擎层实现的，MySQL本身并不支持。并不是所有的存储引擎都会提供行锁，MyISAM就没有提供行锁功能，对于不支持行数的存储引擎，当我们需要修改表数据的时候，只能通过添加表锁来实现，从而严重影响并发。这也是INNODB替换MyISAM的原因之一。

两阶段锁协议

两阶段锁协议是指，在事务的执行过程中，当需要给行加锁的时候，才自动加上锁，但是直到事务提交了，锁才释放。这个告诉我们，对于并发冲突越严重的语句应该越放到事务的后面来执行。同时这也是避免大事务的原因之一，大事务会导致MySQL长时间占有锁，从而影响系统的并发。

死锁与死锁检测

正如如下的语句，当我们的SQL语句存在交叉锁的时候，就会死锁。

如上的2个事务的锁在互相等待，从而发生了死锁。INNODB提供了如下的2种策略：
1、直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
2、发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

通过超时时间来设置，设置长了，如果真发生了死锁，语句等半天了才退出；设置短了，又存在误删的情况。
为此我们一般打开死锁检测。但是打开死锁检测后，在严重并发场景下又会导致CPU占用高，如果并发是1000，当发生锁资源冲突的时候，每次执行SQL语句都要扫描全部等待同一行锁的语句，1000的并发就需要扫描1000乘以1000次=100W，时间复杂度是O(n*n)。

锁冲突策略

当出现锁冲突的时候，我们可以通过如下的2种策略：
1、控制并发，我们可以使用令牌桶等限流算法（更多限流算法参加我的博客《限流--高并发系统中的流量控制》），在数据库中间件控制并发，也可以在业务层控制并发；
2、提高并发度，比如某个热点商品的库存，我们可以通过将库存拆分成多行，每次扣减库存的时候，随机选择一行就行扣减，当选取的库存不足时，可以拒绝下单或者再次重新选择或者重新分配总行数与每一行的数量等策略。

INNODB加锁规则

如下的加锁规则是参照林晓斌在极客时间的专栏《MySQL实战45讲》，更多细节请参照他的专栏，非常值得推荐。加锁的规则包括2个原则，2个优化与一个bug。
2个原则是：
1、加锁的基本单元是next-key lock，其是前开后闭的区间；
2、查找过程中访问到的对象才会加锁。
2个优化是：
1、唯一索引上的等值查询，next-key lock会退化成行锁（需要存在那个行）；
2、普通索引上的等值查询，向右遍历时且最后一个值不满足等值条件时，next-key lock会退化成间隙锁。
一个bug：
1、唯一索引的范围查询，会遍历到第一个不满足条件为止。
需要注意的是如上的加锁规则是针对于可重复读级别的；对于读提交，去掉上面的间隙锁的部分，以及语句执行完成后，只有满足条件的行，才会加行锁，其他的都释放掉了。这就是为什么说读提交的性能更高了，因为其加锁的范围更小。
间隙锁指的是，在两个元素之间的间隙加上一个锁，防止在间隙中插入元素。比如对于表：

CREATE TABLE t
(
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`n` INT UNSIGNED NOT NULL,
`m` INT UNSIGNED NOT NULL,
PRIMARY KEY (`id`),
KEY `n` (`n`)
)ENGINE=InnoDB;
INSERT INTO t(`id`,`n`,`m`) VALUES(10,10,10),(20,20,20),(30,30,30),(40,40,40);

那么对于如下的SQL语句

BEGIN;
SELECT * FROM t WHERE n=5 FOR UPDATE;
COMMIT; 

那么在可重复读下，为了避免幻读问题，InnoDB会在(0,10)这个间隙之间添加一个间隙锁。我想通过如上的例子，你现在已经知道了什么是间隙锁了，但是什么是next-key lock呢？还是通过举一个例子来说明：

BEGIN;
SELECT * FROM t WHERE n=10 FOR UPDATE;
COMMIT; 

那么InnoDB会在(0,10)之间添加一个间隙锁，在10这一行添加行锁，这个间隙锁与行锁就组成了next-key lock (0,10]。为此next-key lock指的是间隙锁加上间隙锁的右边界的行锁。
那么间隙锁与间隙锁，间隙锁与行锁之间是什么关系呢？从上面的定义中可以看出间隙锁其实堵塞的是往这个间隙中插入元素的行为。为此间隙锁与间隙锁之间没有并发冲突，间隙锁与行锁也没有并发冲突，但是间隙锁会堵塞住往这个间隙中插入元素。
下面我们通过一些例子来加深对上面这个加锁规则的理解，需要注意的是如下的例子还是在可重复读级别下的。

1、唯一索引的等值查询，且行不存在

事务1告诉MySQL需要查询id=5的行且锁住，但是InnoDB只在主键索引树上找到了(0,10)的间隙，于是就在主键索引树的这个间隙上加上(5,10]的next-key lock，而这个是一个等值查询，根据优化二退化成(0,10)的间隙锁，为此事务二被间隙锁锁住了，事务三可以正常运行。

2、唯一索引上的等值查询，且行存在

根据加锁原则，事务1找到了id=10的行，会加10的行锁与(0,10]的next-keylock，但是根据优化一，事务1只会在主键索引树上加id=10的行锁，为此事务2可以正常执行，事务3会堵塞住。

3、唯一索引的范围查询

根据加锁原则与一个bug，事务1会在主键索引树加(0,10)的间隙锁、id=10行锁、(10,20]的next-key lock。为此总的加锁范围是(0,20]，从而把事务2、事务3与事务4都锁住了，事务5可以正常运行。

4、但是如果事务1加上limit 1的限制

由于事务1找到id=10的行后，就停止了搜索。为此总的加锁范围是主键索引树上的(0,10]了，事务4可以执行成功。为此如果知道SQL语句确切返回的行数，可以添加limit限制，减少锁的范围。

5、覆盖索引的范围查询

根据加锁原则，事务1会在n索引树加(0,10)的间隙锁、n=10行锁、(10,20]的next-key lock，为此总的加锁范围是(0,20]，而事务2修改的是主键索引上的id=10的行上m的值，为此可以正常运行。而事务3，修改的是主键索引上的id=10的行上m与n的值，为此要在n索引树上，删除n=10的行且添加n=11与id=10的行，为此会被堵塞。但是事务1如果把“LOCK IN SHARE MODE”改成“FOR UPDATE”或者把“SELECT id”改成”SELECT *”，那么事务2也会把锁住，因为他们会把主键索引树上的id=10的行加锁行锁。这个例子说明了锁是加在索引树上的，且访问到的行才会加锁。

总结：如上的例子中，如果是在读提交上，那么加锁的范围需要去掉间隙锁，同时语句执行完后，只加锁了匹配到的行的行锁。读提交下的加锁范围比可重复读小很多，这也是为什么现在读提交用的越来越广泛了。但是在某些场景下还是要用到可重复读，比如金融领域的对账系统。

INNODB死锁分析

根据加锁原则，事务1与事务2都会在主键索引树上加间隙锁(0,10)。那么事务1与事务2都被对方的间隙锁堵住。那么我们如何查看死锁信息呢？可以通过如下的语句查看：

SHOW ENGINE Innodb STATUS;

输出的文本中有如下的内容

 LATEST DETECTED DEADLOCK
 ------------------------
 2020-09-28 20:43:30 7fc4459b7700
 *** (1) TRANSACTION:
 TRANSACTION 6872141811, ACTIVE 7 sec inserting
 mysql tables in use 1, locked 1
 LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s)
 MySQL thread id 36767219, OS thread handle 0x7fc42d7b6700, query id 400555196 172.18.102.216 root update
 INSERT INTO t(`id`,`n`,`m`) VALUES(6,6,6)
 *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 9795 page no 3 n bits 80 index `PRIMARY` of table `institution_rms`.`t` trx table locks 2 total table locks 3  trx id 6872141811 lock_mode X locks gap before rec insert intention waiting lock hold time 7 wait time before grant 0 
 *** (2) TRANSACTION:
 TRANSACTION 6872141739, ACTIVE 8 sec inserting
 mysql tables in use 1, locked 1
 3 lock struct(s), heap size 1184, 2 row lock(s)
 MySQL thread id 36766484, OS thread handle 0x7fc4459b7700, query id 400556851 172.18.102.216 root update
 INSERT INTO t(`id`,`n`,`m`) VALUES(5,5,5)
 *** (2) HOLDS THE LOCK(S):
 RECORD LOCKS space id 9795 page no 3 n bits 80 index `PRIMARY` of table `institution_rms`.`t` trx table locks 1 total table locks 3  trx id 6872141739 lock_mode X locks gap before rec lock hold time 8 wait time before grant 0 
 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 9795 page no 3 n bits 80 index `PRIMARY` of table `institution_rms`.`t` trx table locks 1 total table locks 3  trx id 6872141739 lock_mode X locks gap before rec insert intention waiting lock hold time 0 wait time before grant 0 
 *** WE ROLL BACK TRANSACTION (2)

如下的日志说明事务1在等待间隙锁

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 9795 page no 3 n bits 80 index `PRIMARY` of table `institution_rms`.`t` trx table locks 2 total table locks 3  trx id 6872141811 lock_mode X locks gap before rec insert intention waiting lock hold time 7 wait time before grant 0 

如下的日志说明事务2在持有间隙锁

*** (2) HOLDS THE LOCK(S):
 RECORD LOCKS space id 9795 page no 3 n bits 80 index `PRIMARY` of table `institution_rms`.`t` trx table locks 1 total table locks 3  trx id 6872141739 lock_mode X locks gap before rec lock hold time 8 wait time before grant 0 

如下的日式说明事务2在等待间隙锁

 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 9795 page no 3 n bits 80 index `PRIMARY` of table `institution_rms`.`t` trx table locks 1 total table locks 3  trx id 6872141739 lock_mode X locks gap before rec insert intention waiting lock hold time 0 wait time before grant 0 

如下的日志说明MySQL回滚了事务2

到这里，我们也就讲完了《MySQL的锁与锁机制》的内容了。个人认为，基础知识的学习和巩固，是为了更好的将其运用到项目中，欢迎关注golang学习网公众号，带你了解更多关于mysql的知识点！