一前言
这一篇死锁特别有意思, 涉及主键和辅助索引的更新以及删除的死锁分析
如果没有特别说明, 隔离级别均为 RR
二死锁输出
- 2018-03-25 12:20:16 0x7f9a34469700
- *** (1) TRANSACTION:
- TRANSACTION 3044459, ACTIVE 7 sec starting index read
- mysql tables in use 1, locked 1
- LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
- MySQL thread id 92021, OS thread handle 140299577640704, query id 3114328 127.0.0.1 root updating
- update t set c2=8 where c1=5
- *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
- RECORD LOCKS space id 232 page no 3 n bits 80 index PRIMARY of table `db01`.`t` trx id 3044459 lock_mode X locks rec but not gap waiting
- *** (2) TRANSACTION:
- TRANSACTION 3044456, ACTIVE 14 sec updating or deleting, thread declared inside InnoDB 4999
- mysql tables in use 1, locked 1
- 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
- MySQL thread id 92050, OS thread handle 140300278732544, query id 3114329 127.0.0.1 root updating
- delete from t where id=30
- *** (2) HOLDS THE LOCK(S):
- RECORD LOCKS space id 232 page no 3 n bits 80 index PRIMARY of table `db01`.`t` trx id 3044456 lock_mode X locks rec but not gap
- *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
- RECORD LOCKS space id 232 page no 4 n bits 80 index idx_c1 of table `db01`.`t` trx id 3044456 lock_mode X locks rec but not gap waiting
- *** WE ROLL BACK TRANSACTION (1)
表中的记录
id | c1 | c2 |
---|---|---|
24 | 3 | 4 |
25 | 3 | 4 |
26 | 3 | 4 |
27 | 3 | 4 |
30 | 5 | 8 |
其中 id 为主键, c1 为普通索引 KEY idx_c1 (c1)
SQL 执行顺序
Time | Sess 1 | Sess 2 |
---|---|---|
@t1 |
| begin |
@t2 |
| select * from t where id=30 for update |
@t3 | update t set c2=8 where c1=5 |
|
@t4 |
| delete from t where id=30 |
死锁分析
Sess2 开启一个事务, 在 @t2 时刻执行了根据主键的 select for update , 持有 id=30 的
lock_mode X record lock
Sess1 执行根据 c1=5 的更新, 虽然定位到的也是 id=30 的记录, 但是其加锁顺序是先在 idx_c1 索引上加锁, 顺利加锁, 然后到主键上加 id=30 的锁, 发现 Sess2 已经持有 X 的锁, 需要等待
Sess2 执行 id=30 的记录删除, 由于事务本身已经持有了主键上的锁, 删除记录同时要对索引 idx_c1 上的记录加上
lock_mode X record lock
, 发现该锁已经被 Sess1 持有, 形成了死锁条件, Sess1 报错, 发生回滚
这个死锁的根本原因是因为一个是通过主键加锁, 一个是通过辅助索引加锁
三拓展案例分析:
我们分散下思维, 继续分析执行根据主键或者执行其他语句是否会造成死锁
拓展案例一:
Time | Sess 1 | Sess 2 |
---|---|---|
@t1 |
| begin |
@t2 |
| select * from t where id=30 for update |
@t3 | update t set c2=8 where c1=5 |
|
@t4 |
| update t set c2=8 where id=30 |
分析:
@t1 到 @t3 的逻辑和上文分析的一样, 不一样的是 Sess2 在 @t4 执行的 SQL, 这个案例是不会发生死锁的, 因为 Sess2 执行的更新的字段是 c2 , 不涉及到 c1 , 因此不需要去申请 idx_c1 上的 X 锁, 因此不需要等待 Sess1 在 idx_c1 上的加的 X 锁
拓展案例二:
Time | Sess 1 | Sess 2 |
---|---|---|
@t1 |
| begin |
@t2 |
| select * from t where id=30 for update |
@t3 | update t set c2=8 where c1=5 |
|
@t4 |
| update t set c1=6 where id=30 |
分析:
和案例一唯一不同的是 Sess2 在 @t4 时刻更新的字段是 c1 , 导致需要等待 Sess1 在 @t3 时刻获取的 idx_c1 上的 X 锁, 造成了死锁
拓展案例三:
Time | Sess 1 | Sess 2 |
---|---|---|
@t1 |
| begin |
@t2 |
| select * from t where id=30 for update |
@t3 | update t set c2=8 where id=30 |
|
@t4 |
| update t set c1=6 where id=30 |
分析:
Sess2 在 @t2 时刻持有了 id=30 的 X 锁, Sess1 在 @t3 时刻申请 id=30 的 X 锁, 发生等待, Sess2 在 @t3 时刻根据 id=30 更新 c1 字段, 虽然 c1 字段在 idx_c1 索引里面, 但是 Sess1 并没有持有对应的 idx_c1 上的锁, 不需要发生等, Sess2 执行成功
拓展案例四:
Time | Sess 1 | Sess 2 |
---|---|---|
@t1 |
| begin |
@t2 |
| select * from t where id=30 for update |
@t3 | update t set c2=8 where id=30 |
|
@t4 |
| delete from t where id=30 |
分析:
和案例三基本一样, Sess2 也是顺利执行成功
四小结
根据不同的索引加锁也很容易造成死锁, 因此推荐的避免死锁方案是把 Sess1 原本使用复制索引的更新改成基于主键进行更新, 从而避免了 idx_c1 上的加锁, 也就是使用方案四
来源: https://www.thinksaas.cn/group/topic/839168/