作者:名扬
前言
阅读本文后你将收获：
- 1.对MySql中的锁有更加全面的认识。
- 2.了解什么是幻读，以及如何避免幻读 。
- 3.InnoDb 引擎关于行级锁的实现方式。
- 4.死锁产生的条件、实例及如何避免死锁。
- 5.本文中的sql语句均可直接在MySql中执行，方便自己做实验，这点很重要，只有自己动手实验过才会记忆更加深刻。
另外，由于本人水平及时间有限，文中若有纰漏，欢迎批评指正，感激不尽，当然有任何疑问也欢迎评论区留言，一起学习讨论共同进步。
相信许多同学对于MySQL锁的概念并不陌生，但又感觉理解地不是很透彻，总像蒙着一层纱。那么今天我们就一起通过实际操作来捋一捋 Mysql 的锁，读本文之前建议你先了解 隔离级别、当前读、快照读 等概念 。注：本文的操作均是基于 Mysql 8.0.22 版本 InnoDB 引擎进行的，在其他大版本下执行，结果可能存在差异。（注意📢：本文均是在（repeatable-read）隔离级别下进行的操作）
接下来我们将从 MySQL有哪些锁以及为什么要加锁展开讨论。
1.MySql 有哪些锁？
MySQL 不同的存储引擎支持不同的锁机制，所有的存储引擎都以自己的方式实现锁，服务器层无需了解存储引擎锁的具体实现。根据锁的不同粒度，MySQL 的锁可分为：全局锁、表级锁、行级锁（InnoDB）及其他（自增ID锁）。本文将重点讨论行级锁。其他只做简要描述，感兴趣的可自行搜索。
1.1 全局锁
Flush tables with read lock ：
顾名思义是锁整个数据库，全局锁的典型使用场景是数据库备份时，为了保持数据的一致性，会对数据库加全局锁，但是当数据库表使用的引擎为InnoDB时，一般使用mysqldump …… --single-transaction。如果使用MyISAM引擎进行数据备份时，则只能加全局锁了。
如果数据备份时不加全局锁，会产生怎样的数据不一致呢？这里举个栗子：数据库中含有： Wallet（用户钱包）表 及 stock （商品库存）表，当购买商品时需要同时扣减 用户钱包 及 商品库存。在数据库备份过程中，恰好发生了商品购买。而且恰好是在 wallet 表备份完成后 及 stock 备份前，发生了商品购买。由于wallet表已经完成了备份，所以此次的钱包扣减操作并没有被记录到wallet的备份文件中。但扣减库时，stock表还未备份，所以此次库存扣减记录在备份文件中， 这就导致使用备份文件恢复出来的数据库数据不一致了，钱包没有扣钱，但是库存扣减了。
由于InnoDB引擎支持快照读，所以如果在数据库备份时打一个快照（--single-transaction），则就算不加全局锁，也不会有数据不一致的问题。
1.2 表级锁：
表锁（Lock tables read）：
一般MyISAM和MEMORY存储引擎会采用表锁解决并发（这两个引擎不支持行级锁），而InnoDB引擎则同时支持表锁与行锁，并通过行级锁来提高并发性（另外，行锁使用不当时，InnoDB的行锁也会退化成表级锁，后面会介绍）。另外BDB存储引擎使用的是页面锁，页面锁的粒度介于表级锁与行级锁之间。
MDL锁（meta data lock）：
在 MySQL 5.5 版本之后引入了 MDL锁，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作DDL的时候，加 MDL 写锁。以防止在数据查询或数据更新时有表结构的变更，进而导致查询或更新的结果与预期的不一致。
设想一下，如果DDL操作 与 CURD操作之间不加锁，可以同时进行，那么在查询数据时，你的查询条件列被删除了，或者你要更新的列被删除了，是不是不论从 MySql的语句执行方面 及业务查询数据的结果来看，都会怪怪的。所以MySql 引入了MDL锁的概念。
意向锁（Intention Locks）：
为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表级别锁：
- 意向共享锁（IS）：事务在给数据行加读锁前，必须先获取该表的 IS 锁。
- 意向排他锁（IX）：事务在给数据行加写锁前，必须先获取该表的 IX 锁。
意向锁与锁之间的兼容关系如下：
Tips: X ：eXclusive 排它锁， S: Share 共享锁， IX : Intention eXclusive 意向排它锁， IS ：Intention Share 意向共享锁。
| X | IX | S | IS | |
|---|---|---|---|---|
| IS | 冲突 | 兼容 | 兼容 | 兼容 |
| IX | 冲突 | 兼容 | 冲突 | 兼容 |
| S | 冲突 | 冲突 | 兼容 | 兼容 |
| X | 冲突 | 冲突 | 冲突 | 冲突 |
一句话：意向锁的作用是 在加表锁时，可以快速的发现，是否存在行锁，而无需遍历整个索引后才知道表已经加了行锁，已无法再加表级锁。举个例子：
CREATE TABLE `wallet` (
`user` VARCHAR(32) NOT NULL ,
`balance` int(11) DEFAULT 0,
PRIMARY KEY (`user`)
) ENGINE=InnoDB;
INSERT INTO wallet VALUES ("Tom",1000);| session1 | session2 | |
|---|---|---|
| T1 | BEGIN; select * from wallet where user = 'tom' for update; 加了行锁 及 IX 锁 | |
| T2 | BEGIN; LOCK TABLES wallet READ; 加表锁时 block |
session 1 在 T1 时刻加行锁时，引擎自动在 wallet 表上加了 IX。 session 2 在 T2 在表读锁之前，引擎自动加了 IS 通过， 但在加 S 锁时 与 sesion1 加的 IX 冲突了，所以进入阻塞状态。 通过这种方式无需遍历所有索引是否存在行级锁，大幅提高了MySql并发的效率。
1.3 行级锁：
InnoDB存储引擎为了提高并发度，引入了行级锁概念，行级锁有涉及 排他锁、共享锁、行锁、间隔锁、The next key lock 等概念，后续会一一介绍。 注意📢： 后文中提到的行锁实则为行级锁。 而将只锁一行的锁叫做典型行锁。
1.4 自增ID锁
当表含有自增ID字段时（AUTO_INCREMENT），为了防止并发事务申请到相同的自增ID，所以MySQL在事务申请自增ID时加了锁。但自增ID锁不是事务性（换句话说就是锁不会在整个事务过程当中一直持有），而是在获取到自增ID后立即释放。
2.为什么要加锁？
2.1 保证数据一致性
下面举一个 因不加锁而导致数据不一致的场景。假设有一个存储用户钱包余额的wallet 表，建表语句如下：
CREATE TABLE `wallet` (
`user` VARCHAR(32) NOT NULL ,
`balance` int(11) DEFAULT 0,
PRIMARY KEY (`user`)
) ENGINE=InnoDB;
-- 插入记录
INSERT INTO wallet VALUES ("Tom",1000);场景：用户 Tom 钱包中有 1000 元。现要购买一个1000元的商品，但由于某种原因（客户端、网关重试或恶意攻击 等原因），服务器 同时收到了 两条 Tom 的购买请求。
假设业务的执行逻辑是：
- 1.查询数据库 ；
- 2.验证余额是否充足 ；
- 3.如果充足，则执行扣款 ；
- 4.如果不充足，则返回扣款失败 ；
（注意📢：本文均是在（repeatable-read）隔离级别下进行的操作）
假设业务逻辑是按照如下时序执行的，那么会出现什么问题呢？ 可以先思考下黑色的执行结果如何？
| session1 | session2 | |
|---|---|---|
| T1 | BEGIN; select * from wallet where user = 'tom'; | |
| T2 | 业务代码 校验余额是否充足 余额为1000 充足 | BEGIN; select * from wallet where user = 'tom'; |
| T3 | 业务代码 校验余额是否充足 余额为1000 充足 | |
| T4 | UPDATE wallet set balance = balance - 1000 WHERE user = 'tom'; | |
| T5 | select * from wallet where user = 'tom'; balance = ? | |
| T6 | UPDATE wallet set balance = balance - 1000 WHERE user = 'tom'; 是否会block？ | |
| T7 | select * from wallet where user = 'tom'; balance = ? | |
| T8 | commit; | |
| T9 | select * from wallet where user = 'tom'; balance = ? | |
| T10 | select * from wallet where user = 'tom'; balance = ? | |
| T11 | commit |
下表是执行结果：
| session1 | session2 | |
|---|---|---|
| T1 | BEGIN; select * from wallet where user = 'tom'; | |
| T2 | 业务代码 校验余额是否充足 余额为1000 充足 | BEGIN; select * from wallet where user = 'tom'; |
| T3 | 业务代码 校验余额是否充足 余额为1000 充足 | |
| T4 | UPDATE wallet set balance = balance - 1000 WHERE user = 'tom'; | |
| T5 | select * from wallet where user = 'tom'; balance = 1000 如果不了解为何是1000， 可自行搜索 可重复读隔离级别相关内容 | |
| T6 | UPDATE wallet set balance = balance - 1000 WHERE user = 'tom'; 是否会block？ 是，因为update语句会默认加写锁，由于session1已经加了锁，所以session 2 要等待 | |
| T7 | select * from wallet where user = 'tom'; balance = 0 | |
| T8 | commit; | 当session 1 提交后， update语句将不再阻塞，并执行成功，此时 balance 变为 -1000 |
| T9 | select * from wallet where user = 'tom'; balance = -1000 | |
| T10 | select * from wallet where user = 'tom'; balance = -1000 即使在可重复读隔离级别下，也是可以看到自己的提交的，所以是 -1000，而不是 0 | |
| T11 | commit |
大家可以看到，如果不加锁的情况下会出现余额为负的情况，导致数据不一致。
同样的场景，如果加了锁会怎样呢？
| session1 | session2 | |
|---|---|---|
| T1 | BEGIN; select * from wallet where user = 'tom' for update; | |
| T2 | 业务代码 校验余额是否充足 余额为1000 充足 | BEGIN; select * from wallet where user = 'tom' for update; 阻塞 |
| T3 | UPDATE wallet set balance = balance - 1000 WHERE user = 'tom'; | |
| T4 | commit; | session 1 提交后，session 2 不再阻塞，读到余额为0 |
| T5 | 业务代码 校验余额是否充足 余额为0 返回失败 |
如上，同样的场景下，加锁可以避免数据的不一致。
2.2 避免产生幻读
现有表student。表结构与表中记录如下所示：
CREATE TABLE `student` (
`student_id` int NOT NULL,
`name` VARCHAR(32) NOT NULL ,
`age` int(11) DEFAULT 0,
PRIMARY KEY (`student_id`),
KEY `age`(`age`) USING BTREE
) ENGINE=InnoDB;
INSERT INTO student VALUES(6,'Tom',6),(11,'Jerry',11),(18,'Jamey',18),(25,'Marvin',25);下面的操作与演示何为幻读。
| session1 | session2 | |
|---|---|---|
| T1 | BEGIN; SELECT * from student where student_id > 6 and student_id < 20; rows 2 11 Jerry 18 Jamey | |
| T2 | INSERT into student VALUES(16,'Lucy',16); | |
| T3 | SELECT * from student where student_id > 6 and student_id < 20; rows 2 11 Jerry 18 Jamey | |
| T4 | INSERT into student VALUES(16,'Lucy',16); Duplicate entry '16' for key 'student.PRIMARY' | |
| T5 | SELECT * from student where student_id > 6 and student_id < 20; rows 2 11 Jerry 18 Jamey | |
| T6 | commit; |
在可重复读（RR）隔离级别下，执行select为快照读，所以在T3时刻并无法读取到T2时刻插入的记录，但T4时刻插入student_id=16的记录时，却又出现了重复，而且就算T5时刻再次读取也无法读取数据库中的student_id=16的记录。就像产生了幻觉一样，明明查不到数据，但插入时却冲突，这种现象就称为幻读。解决幻读需要设置 Serializable 隔离级别或读数据时使用锁（当前读）。下面将讨论如何通过加锁解决幻读。
3.行级锁
3.1 For Update 锁
本节将讨论，为何行级锁可以解决幻读？又是如何解决的？下面先看一下如下语句的执行结果。依然是使用student表，表中有4行数据，建表及插入语句如下。
CREATE TABLE `student` (
`student_id` int NOT NULL,
`name` VARCHAR(32) NOT NULL ,
`age` int(11) DEFAULT 0,
PRIMARY KEY (`student_id`),
KEY `age`(`age`) USING BTREE
) ENGINE=InnoDB;
INSERT INTO student VALUES(6,'Tom',6),(11,'Jerry',11),(18,'Jamey',18),(25,'Marvin',25);| session1 | session2 | |
|---|---|---|
| T1 | BEGIN; SELECT * from student where student_id > 6 and student_id < 20 FOR UPDATE; | |
| T2 | SELECT * from student where age > 6 and age < 20; block？ no | |
| T3 | INSERT into student VALUES(17,'Lily',17); block? yes | |
| T4 | INSERT into student VALUES(21,'Lily',21); block? yes | |
| T5 | NSERT into student VALUES(26,'Lily',26); block? No |
行锁是通过对索引项加锁，而不是作用于真实记录的。所以只有通过索引查询才会使用行锁（The next key lock），没有索引则会是锁全表（退化成表级锁）。
为什么T3 与 T4 会阻塞，而T5不会？ 这就就涉及到了一个新概念 gap lock（注：gap lock 是RR级别独有的。RC级别只锁行，不锁空隙，文末会简单介绍）。间隙锁，锁的是两个值之间的空隙。如
SELECT * from student where student_id > 6 and student_id < 20 FOR UPDATE 不仅会锁满足条件的行（student_id = 11 与 student_id = 18）,而且还会锁住他们之间空隙。如下图所示：
下面是InnoDB加锁的两个原则与两个优化（此处借鉴丁奇老师的MySQL 45 讲）。这里涉及到Next-key lock概念，Next-key lock是由 gap lock 与 典型行锁组成的。如上图 在 索引 11 处 加的 Next-key lock 就是由 6-11 的gap lock 加上 11 的典型行锁 组成的，注意next-key lock 是左开右闭的，用数学标识法 类似： ( 6 , 11 ] 。下面来看一下RR级别下 InnoDb引擎加锁的原则是：
- 原则 1：加锁的基本单位是 next-key lock。next-key lock 是左开右闭的 。
- 原则 2：查找过程中访问到的对象才会加锁。
- 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁（典型行锁）。
- 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
我们可以参考2个原则与2个优化来验证下上图的加锁逻辑。
student_id = 6 未加锁 的原因是原则2。因为条件是>6 ，所以并未访问行6，所以行6并未加锁。且也满足原则1，即next-key lock 是左开右闭的。
student_id = 25未加锁的原因是优化2。next-key lock 退化为gap lock，所以25并未加锁。
student_id = 11与 student_id = 18 加锁也是因为 原则 1与原则2。
现在我们可以回答小节开篇的问题了，为何行级锁可以解决幻读？
- 因为加了锁（for update 或 lock in share mode）之后，是当前读，会读取最新的已提交的记录，所以不会产生幻觉。
- 因为在索引间加了间隙锁，所以其他事物无法插入新的记录，也就不会产生幻读了。
补充知识1：只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查 SQL 的执行计划（可以通过 explain 检查 SQL 的执行计划），以确认是否真正使用了索引。
补充知识2：更新语句默认加锁，且是当前读。在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这就是两阶段锁协议。
3.2 Lock In share mode 锁
Lock In share mode 锁也可以实现当前读， 且加锁后其他session不可以写。但当前session 可以写。 Lock In share mode 与其他事务的 for update 锁是互斥的，但与其他事务的Lock In share mode 是共享的。
如下语句的执行结果解释了上述结论。
| session1 | session2 | |
|---|---|---|
| T1 | BEGIN; | |
| T2 | INSERT into student VALUES(10,'Lily',17); | |
| T3 | SELECT * from student where student_id > 6 and student_id < 20 能否查到刚才插入的数据？ 能，因为仅仅是begin，并未真的去获取快照，而是执行sql之后，才会生成快照。（感兴趣的可以了解下快照读） | |
| T4 | INSERT into student VALUES(12,'Lily',17); | |
| T5 | SELECT * from student where student_id > 6 and student_id < 20 能否查到刚才插入的数据？ 不能，在RR级别下，此刻是快照读 | |
| T6 | SELECT * from student where student_id > 6 and student_id < 20 LOCK IN SHARE MODE; 能否查到刚才插入的数据？ 能，因为 share mode 是当前读 | |
| T7 | INSERT into student VALUES(19,'Lucy',21) 是否block？ 是，与Session 1 的锁冲突。 | |
| T8 | INSERT into student VALUES(15,'Lucy',21); 是否block？ 否，同一session 申请写锁时，不会与自己申请的读锁冲突，但一般不这样做，这样容易产生死锁，下面内容会描述 |
4.死锁
4.1 死锁的产生与避免
既然加了锁，就难免产生死锁，下面回顾下产生死锁的四个必要条件？
- （1） 互斥条件：一个资源每次只能被一个进程使用。
- （2） 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。
- （3） 不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺。
- （4） 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。
只要上面四个必要条件任一被打破，死锁就不会发生。在我们日常做开发使用锁时，避免死锁的方式通常是打破条件2 与条件 4。
打破2：申请锁失败后，要将之前申请的锁释放掉。不可以等待锁的同时再申请锁。
打破4：避免形成环路等待，一种简单的做法是将锁进行编号。如： 锁1 、锁2、 锁3。 申请锁时秉承的原则是 按序号递增的去申请锁，比如 线程A 需要 锁1 与 锁3， 要先申请1 再申请3，而不能申请3 再 申请 1 ，线程B 需要 锁 1与锁2，要先申请锁1再申请锁2，这样 线程A 与 线程B 申请的锁就不会形成环路了 。
那么MySql是如何解决死锁问题的呢，实际上MySql 是有死锁检测的，也就是说当产生死锁时MySql是可以检测出来的，当多个事务产生死锁时，MySql会选择一个代价较小的事务进行回滚。死锁检测会消耗一部分Mysql的性能，可以通过参数设置关掉，但不建议这样做。死锁检测关掉后，MySql可以通过锁超时机制变向解决死锁问题，当锁等待时间超出设定的阈值时会自动回滚事务。锁超时设置方法见附录。
下面就简单列举几种MySql 会产生死锁的场景。
4.2 死锁的几种场景
4.2.1 由于Share Mode 使用不当导致的死锁
如下语句使用的依然是student表。
| session1 | session2 | |
|---|---|---|
| T1 | BEGIN; SELECT * from student where student_id > 6 and student_id < 20 LOCK IN SHARE MODE; | BEGIN; |
| T2 | SELECT * from student where student_id > 6 and student_id < 20 LOCK IN SHARE MODE; | |
| T3 | INSERT into student VALUES(19,'Lucy',21); | |
| T4 | INSERT into student VALUES(19,'Lucy',21); dead lock |
死锁的原因：
session 1 在T1时刻申请了读锁，session 2 在T2 时刻也申请了读锁。session 2 在T3时刻申请写锁时，要等待session 1的读锁。session 1在T4时刻申请写锁时，要等待session2的读锁。形成了环路等待，导致了死锁。
所以 使用Lock in share mode 时，不建议更新记录，很容易造成死锁并产生回滚。
4.2.2 由于更新顺序不当引起的死锁
CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`v` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;| session1 | session2 | |
|---|---|---|
| T1 | BEGIN; update t set v = v+1 where id = 1; | |
| T2 | begin; update t set v = v+1 where id = 2; | |
| T3 | update t set v = v+1 where id = 2; | |
| T4 | update t set v = v+1 where id = 1; deadlock |
死锁的原因：
session 1 在 T1 时刻 将表 t 的 id=1行加了 典型行锁。 session 2 在 T2 时刻 将表t的 id = 2行加了典型行锁。 T3 时刻 session 1要申请 id= 2行的写锁，此事因为 id=2行的写锁已经被session 2 申请了，所以session 1 要等待session 2释放 id=2的写锁。T4时刻 session 2 要申请 id=1的行锁，但id=1的行锁被session 1 占用着。所以session 1与session2形成了环路等待，进而导致了死锁。
4.2.3 并发争抢产生的死锁
CREATE TABLE `t` (
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`c`)
) ENGINE=InnoDB;| session1 | session2 | session3 | |
|---|---|---|---|
| T1 | BEGIN; INSERT into t VALUES(5,5); | ||
| T2 | INSERT into t VALUES(5,5); block | ||
| T3 | INSERT into t VALUES(5,5); block | ||
| T4 | ROLLBACK; | Affected rows: 1 | Deadlock found |
关于这种情况的死锁，说实话，我并未找到合理的原因解释。如果你有合理的解释，欢迎评论区解惑。
5.总结
回顾下本篇内容，首先介绍了MySql中有哪些锁及这些锁的基本概念，让大家对锁有个全局的认知。其次，通过数据一致性与幻读场景引出为什么要加锁，以及InnoDB中的行级锁是如何工作的。最后介绍了几种产生死锁的场景。本文中的Sql 语句均可直接拷贝至MySql运行，所以建议大家都能亲自动手执行下，这样印象会更加深刻。
最后再次强调，以上实验均为RR级别。如果隔离级别改为 读已提交RC（read committed），则 for update ，in share mode 等行级锁，则退化成典型行锁，即不会加 gap lock。 如果在RC 隔离级别下执行 3.1 节例子，则会由不一样的结果，感兴趣的你不妨试一下 。
| session1 | session2 | |
|---|---|---|
| T1 | BEGIN; SELECT * from student where student_id > 6 and student_id < 20 FOR UPDATE; | |
| T2 | SELECT * from student where age > 6 and age < 20; block？ no | |
| T3 | INSERT into student VALUES(17,'Lily',17); block? no | |
| T4 | INSERT into student VALUES(21,'Lily',21); block? no | |
| T5 | NSERT into student VALUES(26,'Lily',26); block? No |
附录
查看隔离级别
show variables like 'transaction%';设置隔离级别
--可重复读
set global transaction isolation level repeatable read;
--读已提交
set global transaction isolation level read committed;查看mysql版本
select version();设置锁超时时间
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';
SET GLOBAL innodb_lock_wait_timeout=3600;
mysql
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。