死锁案例一

电玩女神 2022-11-29 12:42 219阅读 0赞

### 来源：公众号yangyidba    ###

### 一、前言 ###

死锁，其实是一个很有意思也很有挑战的技术问题，大概每个 DBA 和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析，希望能够对想了解死锁的朋友有所帮助。

### 二、案例分析 ###

2.1 环境说明

MySQL 5.6 事务隔离级别为 RR。

CREATE TABLE `ty` (
      `id`int(11) NOT NULL AUTO_INCREMENT,
      `a`int(11) DEFAULT NULL,
      `b`int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idxa` (`a`)
    ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4
    insert into ty(a,b) values(2,3),(5,4),(6,7);

#### 2.2 测试用例 ####

![format_png][]

#### 2.3 死锁日志 ####

------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    2017-09-0922:34:137f78eab82700
    *** (1) TRANSACTION:
    TRANSACTION 462308399, ACTIVE 33 sec starting index read
    mysql tables inuse1, locked 1
    LOCK WAIT 2lockstruct(s), heap size 360, 1 row lock(s)
    MySQL thread id 3525577, OS thread handle 0x7f896cc4b700, query id 780039657 localhost root updating
    deletefrom ty where a=5
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 219 page no4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308399 lock_mode X waiting
    *** (2) TRANSACTION:
    TRANSACTION 462308398, ACTIVE 61 sec inserting, thread declared inside InnoDB5000
    mysql tables inuse1, locked 1
    5lockstruct(s), heap size 1184, 4 row lock(s), undo log entries 2
    MySQL thread id 3525490, OS thread handle 0x7f78eab82700, query id 780039714 localhost root update
    insert into ty(a,b) values(2,10)
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 219 page no4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308398 lock_mode X
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 219 page no4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308398 lock_mode X locks gap before rec insert intention waiting
    *** WE ROLL BACK TRANSACTION (1)

#### 2.3 分析死锁日志 ####

首先要理解的是 对同一个字段申请加锁是需要排队。

其次表 ty 中索引 idxa 为非唯一普通索引，我们根据事务执行的时间顺序来解释，这样比较好理解。

a. 根据死锁日志显示事务 2 也即 sess1 执行的事务，根据 HOLDS THE LOCK(S) 显示 sess1 先执行 delete from ty where a=5 ，该事务持有索引 a=5 的行锁 lock\_mode X ,因为是 RR 隔离级别，所以 sess1 还持有两个 gap 锁\[1,2\]-\[2,5\], \[2,5\]-\[3,6\] 。

b. 事务1的日志也即 sess2 执行的事务,申请对 a=5 加锁，一个 rec lock 和两个 gap 锁,因为 sess1 中 delete 还没释放，故 sess2 的事务 1 等待 sess1 的事务 2 释放 a=5的锁资源。

c. 然后根据 WAITING FOR THIS LOCK TO BE GRANTED，提示事务 2 insert 语句正在等待 lock\_mode X locks gap before rec insert intention waiting，因为 insert 语句 \[4,2\] 介于 gap 锁\[1,2\]-\[2,5\]之间，所以有了提示 "lock\_mode X locks gap",insert 语句必须等待前面 sess2 中 delete 获取锁并且释放锁。于是，sess2(delete) 等待sess1(delete) ,sess1(insert)等待 sess2(delete),循环等待，造成死锁。

问题：如果 sess1 执行 insert into ty(a,b) values(5,10); sess2会遇到死锁吗？

#### 三、案例二 ####

#### 3.1 索引为唯一键 ####

MySQL 5.6 事务隔离级别为 RR。

CREATE TABLE `ty` (
      `id`int(11) NOT NULL AUTO_INCREMENT,
      `a`int(11) DEFAULT NULL,
      `b`int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      unique KEY `idxa` (`a`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
    insert into t2(a,b) values(2,3),(5,4),(6,7)

#### 3.2 测试用例 ####

![format_png 1][]

#### 3.3 死锁日志 ####

------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    2017-09-1000:03:317f78ea936700
    *** (1) TRANSACTION:
    TRANSACTION 462308445, ACTIVE 9 sec starting index read
    mysql tables inuse1, locked 1
    LOCK WAIT 2lockstruct(s), heap size 360, 1 row lock(s)
    MySQL thread id 3526009, OS thread handle 0x7f896cc4b700, query id 780047877 localhost root updating
    deletefrom t2 where a=5
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 221 page no4 n bits 72 index `idxa` of table `test`.`t2` trx id 462308445 lock_mode X waiting
    *** (2) TRANSACTION:
    TRANSACTION 462308444, ACTIVE 17 sec inserting, thread declared inside InnoDB5000
    mysql tables inuse1, locked 1
    4lockstruct(s), heap size 1184, 3 row lock(s), undo log entries 2
    MySQL thread id 3526051, OS thread handle 0x7f78ea936700, query id 780047890 localhost root update
    insert t2(a,b) values(5,10)
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 221 page no4 n bits 72 index `idxa` of table `test`.`t2` trx id 462308444 lock_mode X locks rec but not gap
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 221 page no4 n bits 72 index `idxa` of table `test`.`t2` trx id 462308444lock mode S waiting
    *** WE ROLL BACK TRANSACTION (1)

#### 3.4 分析死锁日志 ####

首先我们要特别说明 delete 的加锁逻辑

a. 找到满足条件的记录，并且记录有效，则对记录加X锁，No Gap 锁(lock\_mode X locks rec but not gap);

b. 找到满足条件的记录，但是记录无效(标识为删除的记录)，则对记录加 next key 锁(同时锁住记录本身，以及记录之前的Gap：lock\_mode X);

c. 未找到满足条件的记录，则对第一个不满足条件的记录加 Gap 锁，保证没有满足条件的记录插入(locks gap before rec)

其次需要大家注意的是对比两个死锁案例会发现，sess1 事务持有的锁类型发生了变化 delete 持有的锁变为 lock\_mode X locks rec but not gap 。insert 语句持有的锁变为 lock mode S waiting。原因是因为测试表结构发生了变化字段 a 由普通索引变为唯一键，RR 模式下对唯一键操作是没有 gap 锁的，而且 insert 写入含有唯一键的数据是会申请 GAP 锁的特殊情况 Insert Intention Lock。

本例我们依然根据事务执行的时间顺序来解释，这样比较好理解。

a. 根据死锁日志显示事务 2 也即 sess1 执行的事务，根据 HOLDS THE LOCK(S)显示 sess1 先执行 delete from ty where a=5 ，该事务持有索引 a=5 的行锁 lock\_mode X locks rec but not gap。因为本例中 a 是唯一键，故没有 gap 锁。

b. 事务 1 的日志也即 sess2 执行的事务,申请对 a=5 加锁(X Next-key Lock)，一个 rec lock 但是因为 sess1 中 delete 已经执行完成，记录无效没有被删除，锁还没释放，故 sess2 的事务 1 等待 sess1 的事务 2 释放 a=5 的锁资源，日志中提示  lock\_mode X waiting。

c. 然后根据  WAITING FOR THIS LOCK TO BE GRANTED，提示事务 2 insert 语句正在等待 lock mode S waiting，为什么这次是 S 锁呢？因为 a 字段是一个唯一索引，所以 insert 语句会在插入前进行一次 duplicate key 的检查，需要申请 S 锁防止其他事务对 a 字段进行重复插入。而插入意向锁与 T1 已经 insert 语句必须等待前面  sess2 中 delete 获取 a=5 的行锁并且释放锁。

于是，**sess2(delete) 等待sess1(delete) ,sess1(insert)等待sess2(delete),循环等待，造成死锁**。

### 四、小结 ###

本文研究了 RR 事务隔离级别下，普通索引与唯一键两种情况的死锁场景。如何避免解决此类死锁？推荐使用 RC 隔离级别+ ROW BASE BINLOG . 但是对于 RC/RR 模式下 ，insert 遇到唯一键冲突的时候的死锁不可避免。需要开发在设计表结构的时候 减少 unique 索引设计。

扩展阅读

1. [漫谈死锁][Link 1]

2. 如何阅读死锁日志

![format_png 2][]

全文完。

Enjoy MySQL :)

--------------------

叶老师的「MySQL核心优化」大课已升级到MySQL 8.0，扫码开启MySQL 8.0修行之旅吧

![format_png 3][]

[format_png]: /images/20221124/3dc2b99b012241c2ac6c47b15657f70d.png
[format_png 1]: /images/20221124/590c59e3da2d4872b9c283a034d67858.png
[Link 1]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b542f8a4cdd39f6195aceb447b7dda3c043af8a00e4048abc66f1150d122762ba93220940&idx=1&mid=2653933125&scene=21&sn=54e4c1a12223c45e4232e2227d7d19da#wechat_redirect
[format_png 2]: /images/20221124/561fe69b132f4edfb6e6300bc1c42237.png
[format_png 3]: /images/20221124/d9d50ff6226648c1a9d1168433785af6.png