MySQL · 2018-03-25 0

MySQL死锁案例分析(二)

一、前言

这一篇死锁特别有意思,涉及主键和辅助索引的更新以及删除的死锁分析

如果没有特别说明,隔离级别均为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
死锁分析
  1. Sess2开启一个事务,在@t2 时刻执行了根据主键的select for update,持有id=30lock_mode X record lock
  2. Sess1 执行根据c1=5的更新,虽然定位到的也是id=30的记录,但是其加锁顺序是先在idx_c1索引上加锁,顺利加锁,然后到主键上加id=30的锁,发现Sess2已经持有X的锁,需要等待
  3. 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锁,因此不需要等待Sess1idx_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=30X锁,Sess1@t3时刻申请id=30X锁,发生等待,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上的加锁,也就是使用方案四。