MySQL死锁案例分析

本文针对上一篇《MySQL优化案例分享》文章中提到的线上业务产生的一个死锁问题进行展开讨论，主要针对两个update操作导致的死锁的场景，借此机会正好总结下MySQL锁及分析下产生死锁的原因和解决方案；

首先，针对MySQL中提供的锁种类做一个简单的总结，关于更多MySQL锁相关的内容可参考官方文档；

MySQL InnoDB存储引擎提供了如下几种锁：

1、共享/排他锁（S/X锁）

共享锁（S Lock）：允许事务读取一行数据，多个事务可以拿到一把S锁（即读读并行）；

排他锁（X Lock）：允许事务删除或更新一行数据，多个事务有且只有一个事务可以拿到X锁（即写写/写读互斥）；

2、意向锁（Intention Lock）

意向锁是一种表级别的锁，意味着事务在更细的粒度上进行加锁。

意向共享锁（IS Lock）：事务想要获得一张表中某几行的共享锁；

意向排他锁（IX Lock）：事务想要获得一张表中某几行的排他锁；

举个例子，事务1在表1上加了S锁后，事务2想要更改某行记录，需要添加IX锁，由于不兼容，所以需要等待S锁释放；如果事务1在表1上加了IS锁，事务2添加的IX锁与IS锁兼容，就可以操作，这就实现了更细粒度的加锁。

3、插入意向锁（Insert Intention Lock）

插入意向锁是间隙锁的一种，专门针对insert操作的。即多个事务在同一个索引、同一个范围区间内插入记录时，如果插入的位置不冲突，则不会阻塞彼此；

举个例子：在可重复读隔离级别下，对PK ID为10-20的数据进行操作：

事务1在10-20的记录中插入了一行：

insert into table value(11, xx)

事务2在10-20的记录中插入了一行：

insert into table value(12, xx)

由于两条插入的记录不冲突，所以会使用插入意向锁，且事务2不会被阻塞。

4、自增锁（Auto-inc Locks）

自增锁是一种特殊的表级别锁，专门针对事务插入AUTO-INCREMENT类型的列。

即一个事务正在往表中插入记录时，其他事务的插入必须等待，以便第1个事务插入的行得到的主键值是连续的。

举个例子：在可重复读隔离级别下，PK ID为自增主键

表中已有主键ID为1、2、3的3条记录。

事务1插入了一行：

insert into table value(‘aa’)

得到一条（4,’aa’）的记录，未提交；

此时，事务2中插入了一行：

insert into table value(‘bb’)

这时会被阻塞，即用到了插入意向锁的概念。

5、记录锁（Record Locks）- locks rec but not gap

记录锁是的单个行记录上的锁，会阻塞其他事务对其插入、更新、删除；

6、间隙锁(Gap Lock)

间隙锁锁定记录的一个间隔，但不包含记录本身。

举个例子：

假如数据库已有ID为1、6两条记录，现在想要在ID in （4，10）之间更新数据的时候，会加上间隙锁，锁住[4,5] [7,10] ,(不包含已有记录ID=5本身)

那么在更新ID=5的记录（只有一条记录）符合条件；

如果不加间隙锁，事务2有可能会在4、10之间插入一条数据，这个时候事务1再去更新，发现在(4,10)这个区间内多出了一条“幻影”数据。

间隙锁就是防止其他事务在间隔中插入数据，以导致“不可重复读”。

7、临键锁（Next-Key Lock）= Gap Lock + Record Lock

临建锁是记录锁与间隙锁的组合，即：既包含索引记录，又包含索引区间，主要是为了解决幻读。

案例分析

MySQL版本：MySQL 5.7

隔离级别：RC

死锁日志：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-04-30 17:24:43 0x7facbf829700
*** (1) TRANSACTION:
TRANSACTION 10021140, ACTIVE 59 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 1111, OS thread handle 140379923085056, query id 52685 127.0.0.1 dba_admin updating
update locktest set addr='nanjin' where name='test'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 69 page no 4 n bits 72 index idx_name of table `wjqtest`.`locktest` trx id 10021140 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 10021141, ACTIVE 35 sec starting index read
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 1112, OS thread handle 140379924109056, query id 52686 127.0.0.1 dba_admin updating
update locktest set addr='nanjin' where name='test' and ctime=0
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 69 page no 4 n bits 72 index idx_name of table `wjqtest`.`locktest` trx id 10021141 lock mode S locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 69 page no 4 n bits 72 index idx_name of table `wjqtest`.`locktest` trx id 10021141 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)

错误日志中同样可以看到死锁信息记录：

2021-04-30T17:24:43.164029Z 1112 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.

那么我们通过对上述操作，结合死锁日志进行分析：

Session1的lock in share mode获取到S锁（lock mode S locks rec but not gap），Session1的lock in share mode获取到S锁（lock mode S locks rec but not gap），
Session1执行update操作申请X锁，等待Session2释放（lock_mode X locks rec but not gap waiting），Session2执行update操作同样申请X锁，等待Session1释放（lock_mode X locks rec but not gap waiting），从而导致死锁产生；Session2死锁后回滚并抛出错误ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

在该场景下，将update操作改为delete会得到同样的效果，同样也会产生死锁；

那么为什么会出现死锁呢？

两个事务同时对一条数据进行更新的时候（可能并发也可能非并发），两个update语句都拿到了S锁，但是升级X锁的时候，出现问题，因为S锁升级X锁，在隔离级别是RC的情况下，必须等所有的S锁释放才能S锁升X锁，所以两个事务相互等待，导致死锁了。

那么针对这种情况该如何解决呢？

MySQL之上加了一层redis缓存锁，防止多个事务同时更新一个数据，如果有其他的解决方法，欢迎大家留言讨论；