基于InnoDB存储引擎的MySQL死锁介绍

大家在日常工作中肯定都遇见过数据库死锁问题，本文以一个具体的MySQL死锁例子为引子，介绍一下死锁的原理、分析和预防，并对数据库事务和锁进行了扩展介绍。

01 问题背景

某一天，张三发现一个跑批任务执行过程中频繁报出数据库死锁问题，相关日志如下：

ex=org.springframework.dao.DeadlockLoserDataAccessException:
### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error occurred while setting parameters
### SQL: UPDATE trans SET status = ? WHERE trans_id= ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ; SQL []; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

trans表结构如下：

Create Table: CREATE TABLE `trans` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `main_trans_id` varchar(32) NOT NULL COMMENT '主订单号',
  `trans_id` varchar(32) NOT NULL DEFAULT '' COMMENT '订单号',
  `status` tinyint(1) NOT NULL COMMENT '状态',
  PRIMARY KEY (`id`),
  UNIQUE KEY `mainTransId` (`main_trans_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='交易流水'

相关SQL语句的执行顺序如下：

步骤	事务A	事务B
1	BEGIN	BEGIN
2	SELECT * FROM trans WHERE main_trans_id=‘M001’ for update	SELECT * FROM trans WHERE main_trans_id=‘M002’ for update
3	UPDATE trans SET status=1 WHERE trans_id=‘T001’
4		UPDATE trans SET status=1 WHERE trans_id=‘T002’
5	COMMIT	COMMIT

02 问题分析

Q：什么是死锁？

A：当用户A持有资源a并尝试去获取资源b，用户B持有资源b并尝试去获取资源a时，AB两个用户由于互相持有对方需要的资源而发生的阻塞现象，被称为死锁。

Q：如何造成死锁？

A：满足以下4个条件：

• 互斥：一个资源每次只能被一个用户使用，多个用户不能同时同一个资源；
• 持有并等待：用户因获取资源b而阻塞时，对已经获取的资源a持有不放，并等待资源b；
• 不可剥夺：用户已经获取的资源，在未使用完之前，不能被其他用户剥夺；
• 循环等待：用户之间形成了循环等待资源的关系，比如用户A等待用户B持有的资源b，用户B等待用户A持有的资源a；

针对上面的线上问题，在步骤2中，事务A和B分别获取了main_trans_id='M001’和’M002’资源；在步骤3、4中，因为表trans没有建立字段trans_id的索引，更新语句会锁全表，即需要获取全表资源。于是，事务A持有资源main_trans_id='M001’等待资源main_trans_id=‘M002’，事务B会等待资源main_trans_id='M001’等待资源main_trans_id=‘M002’，从而造成死锁。

Q：如何预防死锁？

A：破坏互斥、持有并等待、不可剥夺、循环等待四个条件之一即可。

• 资源有序分配：将资源排序，不同用户按照相同的顺序获取资源（破坏了循环等待条件）；
• 避免大事务，减少事务持有资源的时长，降低死锁的概率；
• 建立合理的索引，减少SQL语句需要获取的资源，降低死锁的概率；
• 在一个事务中尽可能一次获取所有资源，降低死锁的概率；
  针对上面的线上问题，将步骤3、4中的更新语句改为UPDATE trans SET status=1 WHERE main_trans_id='M001’和UPDATE trans SET status=1 WHERE main_trans_id='M002’即可（破坏了循环等待）。

03 事务

事务的四要素ACID：

• 原子性（Atomicity）：每个事务都是不可分割的最小工作单元，要么全部成功，要么全部失败并回滚至事务未开始的样子；
• 一致性（Consistency）：一个事务单元需要提交之后才会被其他事务可见；
• 隔离性（Isolation）：多个事务并发执行时，不应该相互影响；
• 持久性（Durability）：已经提交的事务应该被永久保存在数据库中；

事务并发存在以下一些问题：

• 脏读（dirty read）：一个事务读取了另一个事务已修改但未提交的数据；
• 不可重复读（unrepeatable read）：在同一个事务中多次读取同一个数据，由于其他事务对该数据的修改，导致多次读取的结果不一样；
• 幻读（phantom read）：在同一个事务中多次按相同条件查询，由于其他事务的插入或删除，导致多次查询的结果不一样；

为了解决上述问题，SQL规范中定义了不同的隔离级别：

• 读未提交（read-uncommitted，RU）：一个事务中可以读取其他事务未提交的记录，会出现脏读，不可重复读，幻读；
• 读已提交（read-committed，RC）：一个事务中只能看到其他事务已提交的修改，不会出现脏读，但是会出现不可重复读，幻读；
• 可重复读（repeatable-read，RR）：一个事务中进行多次相同数据内容的查询，得到的结果是一样的，但不保证对于数据条数的查询是一样的，解决了脏读，不可重复读问题，但是仍然存在幻读问题；
• 串行化（serializable）：强制事务串行执行；

事务隔离级别	脏读	不可重复读	幻读
RU	可能	可能	可能
RC	解决	可能	可能
RR	解决	解决	可能
serializable	解决	解决	解决

隔离级别的实现方式是什么，SQL规范中并没有严格定义，不同的数据库也会有不同的实现。除了基于锁的实现，还有时间戳，多版本控制等无锁的并发控制。

MySQL默认的隔离级别是可重复读（repeatable-read），InnoDB存储引擎通过多版本快照读和间隙锁技术实现了RR隔离级别，并解决了幻读问题。

基于InnoDb存储引擎的MySQL，依赖undo log 与 ReadView 去实现MVCC（Multi-Version Concurrent Control，多版本并发控制），从而实现了读写并行。在RR隔离级别下，快照读，是读取该记录事务开始时的那个版本，因为读取的是快照数据，并不会被写操作阻塞；当前读，除了加记录锁，还会加GAP锁，用于解决幻读问题。

• 快照读（snapshot read），读取的是记录的可见版本（可能是历史版本，即最新的数据可能正在被当前执行的事务并发修改），不会对返回的记录加锁。普通的select操作（除了for update）都是快照读。
• 当前读（current read），读取的是记录的最新版本，并且会对返回的记录加锁，保证其他事务不会并发修改这条记录。insert、update、delete和select for update操作都是当前读。

undo log

InnoDb存储引擎会为每一行记录增加几个辅助字段：

• DB_TRX_ID：6字节的事务ID，用于标识最近更新这条记录的事务。
• DB_ROLL_PTR：7字节的回滚指针，记录了数据的上一个版本在undo log中的位置，用于判断当前版本数据的可见性。

undo log中记录了数据的多个版本镜像（以链表的形式组织），事务在写一条记录时会将其拷贝一份到undo log中，修改当前行的值，填写事务ID，使回滚指针指向undo log中的修改前的行。

ReadView

在事务开始后的第一个读操作时，会根据当前数据库中活跃的事务链表构造一个ReadView，即ReadView存储了一个事务开启时数据库中活跃的事务链表镜像，事务操作期间不更新，一直到事务结束时关闭。

在事务中要检索一行记录时，InnoDb存储引擎会将该行当前的事务ID与ReadView进行比较：

1. 当检索到的数据的事务ID小于事务链表中的最小值，表示这个数据在当前事务开启前就已经被其他事务修改过了，那么是可见的。
2. 当检索到的数据的事务ID=当前事务ID，表示是当前事务自己修改的数据，那么是可见的。
3. 当检索到的数据的事务ID大于事务链表中的最大值，表示这个数据在当前事务开启后到下一次查询之间又被其他的事务修改过，那么是不可见的。
4. 如果事务链表为空，表示当前事务开始的时候，没有其他任意一个事务在执行，那么是可见的。
5. 当检索到的数据的事务ID在事务链表中的最小值和最大值之间，取出DB_ROLL_PTR指针所指向的回滚版本的事务ID，然后从步骤1重新开始判断，这样总能最后找到一个可见的记录。

04 锁

按粒度的可分为表锁和行锁：

• 表锁指的是对一整张表加锁由 MySQL 服务器实现；
• 行锁指的是锁定某一行数据或某几行，或行和行之间的间隙，由存储引擎（InnoDb等）实现；

其中，行锁可以细分为记录锁、间隙锁、Next-key锁、插入意向锁：

• 记录锁（Record Locks）是最简单的行锁，永远都是加在索引上的。如果WHERE 条件中指定的列是个二级索引，那么记录锁不仅会加在这个二级索引上，还会加在这个二级索引所对应的聚簇索引上；如果SQL语句无法使用索引走全表扫描，那么会给全表的所有数据行加记录锁。
• 间隙锁（Gap Locks）是一种加在两个索引之间的锁，或者加在第一个索引之前，或者加在最后一个索引之后的间隙。间隙锁和间隙锁之间是互不冲突的，间隙锁唯一的作用就是为了防止其他事务在这个范围内插入或修改记录，保证两次读取这个范围内的记录不会变，从而不会出现幻读现象。加间隙 S 锁和加间隙 X 锁没有任何区别。
• Next-key锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。
• 插入意向锁（Insert Intention Locks）是一种特殊的间隙锁，表示插入的意向，只有在 INSERT 的时候才会有这个锁，它只会和间隙锁或Next-key锁冲突。

按模式可分为读锁、写锁、读意向锁、写意向锁、自增锁：

• 读锁，又称共享锁（Share locks，简称 S 锁），加了读锁的记录，所有的事务都可以读取，但是不能修改，并且可同时有多个事务对记录加读锁。
• 写锁，又称排他锁（Exclusive locks，简称 X 锁），或独占锁，对记录加了排他锁之后，只有拥有该锁的事务可以读取和修改，其他事务都不可以读取和修改，并且同一时间只能有一个事务加写锁。
• 意向锁为表级锁，也可分为读意向锁（IS 锁）和写意向锁（IX 锁）。当事务试图读或写某一条记录时，会先在表上加上意向锁，然后才在要操作的记录上加上读锁或写锁。这样判断表中是否有记录加锁就很简单了，只要看下表上是否有意向锁就行了。意向锁之间是不会产生冲突的，也不和 AUTO_INC 表锁冲突，它只会阻塞表级读锁或表级写锁，另外，意向锁也不会和行锁冲突，行锁只会和行锁冲突。
• AUTO_INC 锁又叫自增锁（简称 AI 锁），它是一种特殊类型的表锁，当插入的表中有自增列（AUTO_INCREMENT）时，数据库需要自动生成自增值，在生成之前，它会先为该表加 AI 锁，其他事务的插入操作阻塞，这样保证生成的自增值肯定是唯一的。

其中AUTO_INC 锁：

• AI 锁互不兼容，也就是说同一张表同时只允许有一个自增锁；
• 自增锁不是在事务结束时释放，而是在 INSERT 语句执行结束时释放，这样可以提高并发插入的性能；
• 自增值一旦分配了就会 +1，如果事务回滚，自增值也不会减回去，所以自增值可能会出现中断的情况；

	读锁	写锁	读意向锁	写意向锁	自增锁
表锁	有	有	有	有	有
记录锁	有	有	无	无	无
间隙锁	有	有	无	无	无
Next-key锁	有	有	无	无	无
插入意向锁	有	有	无	无	无

锁的兼容矩阵

第一行表示已有的锁，第一列表示要加的锁。

表锁的兼容矩阵

• 意向锁之间互不冲突；
• S 锁只和 S/IS 锁兼容，和其他锁都冲突；
• X 锁和其他所有锁都冲突；
• AI 锁只和意向锁兼容；

	IS	IX	S	X	AI
IS	兼容	兼容	兼容		兼容
IX	兼容	兼容			兼容
S	兼容		兼容
X
AI	兼容	兼容

行锁的兼容矩阵

• 插入意向锁不影响其他事务加其他任何锁，插入意向锁与间隙锁和Next-key锁冲突
• 间隙锁不和其他锁（不包括插入意向锁）冲突；
• 记录锁和记录锁冲突，Next-key锁和Next-key锁冲突，记录锁和Next-key锁相互冲突；

	记录锁	间隙锁	Next-key锁	插入意向锁
记录锁		兼容		兼容
间隙锁	兼容	兼容	兼容	兼容
Next-key锁		兼容		兼容
插入意向锁	兼容			兼容

加锁分析

针对InnoDB存储引擎，RR隔离级别下的加锁分析：

以上面的表trans为例，id为主键，main_trans_id为唯一索引，trans_id为非唯一索引，status无索引。

1 聚簇索引，查询命中

UPDATE trans SET trans_id=‘T001’ WHERE id=1

对 id=[1] 这个聚簇索引加 X 锁。

2 聚簇索引，查询未命中

UPDATE trans SET trans_id=‘T002’ WHERE id=2	

对 id=(1, 3) 这个聚簇索引加 GAP 锁。

3 辅助唯一索引，查询命中

UPDATE trans SET trans_id=‘T001’ WHERE main_trans_id='M001'	

对 main_trans_id=[‘M001’] 这个辅助索引加 X 锁；
对 id=[1] 这个聚簇索引加 X 锁；

4 辅助唯一索引，查询未命中

UPDATE trans SET trans_id=‘T002’ WHERE main_trans_id='M002'		

对 main_trans_id=(‘M001’, ‘M003’) 这个辅助索引加 GAP 锁。

5 辅助非唯一索引，查询命中

UPDATE trans SET main_trans_id=‘M003’ WHERE trans_id='T003'			

对 trans_id=(‘T001’, ‘T003’] 这个辅助索引加 Next-key 锁；
对 trans_id=(‘T003’, ‘T004’) 这个辅助索引加 GAP 锁；
对 id=[3] 这个聚簇索引加 X 锁；

6 辅助非唯一索引，查询未命中

UPDATE trans SET main_trans_id=‘M002’ WHERE trans_id='T002'			

对 trans_id=(‘T001’, ‘T003’) 这个辅助索引加 GAP 锁。

7 无索引

UPDATE trans SET status=1 WHERE status=0		

对 id 这个聚簇索引的所有记录加 X 锁，聚簇索引之间加 GAP 锁。

8 聚簇索引，范围查询

UPDATE trans SET status=1 WHERE id<=3	

对 id=(-∞,1],(1,3],(3,4] 这个聚簇索引加 Next-key 锁；

9 辅助索引，范围查询

UPDATE trans SET status=1 WHERE main_trans_id<=‘M003’

对 main_trans_id=(-∞,‘M001’],(‘M001’, ‘M003’],(‘M003, ‘M004’] 这个辅助索引加 Next-key 锁；
对 id=[1],[3],[4] 这个聚簇索引加 X 锁

10 修改索引值

UPDATE trans SET trans_id='T002' WHERE id=1

对 id=[1] 这个聚簇索引加 X 锁；
对 trans_id=[‘T001’],[‘T002’] 这个辅助索引加 X 锁；