一 前言
死锁，其实是一个很有意思，也很有挑战的技术问题，大概每个DBA和部分开发同学都会在工作过程中遇见过 。关于死锁我会持续写一个系列的案例分析，希望能够对想了解死锁的朋友有所帮助。
二 案例分析
2.1 环境说明
MySQL 5.6 事务隔离级别为RR
2.2 测试用例
2.3 死锁日志
2.3分析死锁日志
首先要理解的是 对同一个字段申请加锁是需要排队. S GAP 于
其次表ty中a为普通索引字段，我们根据事务执行的时间顺序来解释，这样比较好理解。
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
3.2 测试用例
3.3 死锁日志
3.4 分析死锁日志
首先我们要特别说明 delete的加锁逻辑
本例我们依然根据事务执行的时间顺序来解释，这样比较好理解。
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 索引设计。推荐文章 《 不同语句模式下的锁类型 》
如果您觉得能从本文收益，可以请北在南方一瓶饮料 ^_^
死锁，其实是一个很有意思，也很有挑战的技术问题，大概每个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);
| T2 |
T1 |
| begin; |
|
| delete from ty where a=5; |
begin; |
| |
delete from ty where a=5; |
| insert into ty(a,b) values(2,10); |
|
| |
delete from ty where a=5; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
2.3 死锁日志
- ------------------------
- LATEST DETECTED DEADLOCK
- ------------------------
- 2017-09-09 22:34:13 7f78eab82700
- *** (1) TRANSACTION:
- TRANSACTION 462308399, ACTIVE 33 sec starting index read
- mysql tables in use 1, locked 1
- LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
- MySQL thread id 3525577, OS thread handle 0x7f896cc4b700, query id 780039657 localhost root updating
- delete from ty where a=5
- *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
- RECORD LOCKS space id 219 page no 4 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 InnoDB 5000
- mysql tables in use 1, locked 1
- 5 lock struct(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 no 4 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 no 4 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)
首先要理解的是 对同一个字段申请加锁是需要排队. S GAP 于
其次表ty中a为普通索引字段，我们根据事务执行的时间顺序来解释，这样比较好理解。
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 `t2` (
- `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)
| T2 |
T1 |
| begin; |
|
| delete from ty where a=5; |
begin; |
| |
delete from ty where a=5; |
| insert into ty(a,b) values(2,10); |
|
| |
delete from ty where a=5; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
3.3 死锁日志
- ------------------------
- LATEST DETECTED DEADLOCK
- ------------------------
- 2017-09-10 00:03:31 7f78ea936700
- *** (1) TRANSACTION:
- TRANSACTION 462308445, ACTIVE 9 sec starting index read
- mysql tables in use 1, locked 1
- LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
- MySQL thread id 3526009, OS thread handle 0x7f896cc4b700, query id 780047877 localhost root updating
- delete from t2 where a=5
- *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
- RECORD LOCKS space id 221 page no 4 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 InnoDB 5000
- mysql tables in use 1, locked 1
- 4 lock struct(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 no 4 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 no 4 n bits 72 index `idxa` of table `test`.`t2` trx id 462308444 lock mode S waiting
- *** WE ROLL BACK TRANSACTION (1)
首先我们要特别说明 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.
b 找到满足条件的记录，但是记录无效(标识为删除的记录)，则对记录加next key锁(同时锁住记录本身，以及记录之前的Gap：lock_mode X);
c 未找到满足条件的记录，则对第一个不满足条件的记录加Gap锁，保证没有满足条件的记录插入(locks gap before rec)
本例我们依然根据事务执行的时间顺序来解释，这样比较好理解。
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 索引设计。推荐文章 《 不同语句模式下的锁类型 》
如果您觉得能从本文收益，可以请北在南方一瓶饮料 ^_^
