MySQL · 2018-03-25 1

MySQL死锁案例分析(三)

一、前言

这一篇是死锁记录的第三篇,是在多并发的情况下容易出现的死锁

如果没有特别说明,隔离级别均为RR

二、死锁输出

2018-03-25 13:04:28 0x7f9a34469700
*** (1) TRANSACTION:
TRANSACTION 3045094, ACTIVE 11 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 92021, OS thread handle 140299565065984, query id 3120391 127.0.0.1 root update
insert into tu (c1,c2,c3) values(2,1,2)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 240 page no 6 n bits 72 index uniq_c2 of table `db01`.`tu` trx id 3045094 lock_mode X insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 3045095, ACTIVE 8 sec inserting, thread declared inside InnoDB 5000
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 3120434 127.0.0.1 root update
insert into tu (c1,c2,c3) values(2,1,2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 240 page no 6 n bits 72 index uniq_c2 of table `db01`.`tu` trx id 3045095 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 240 page no 5 n bits 72 index uniq_c1 of table `db01`.`tu` trx id 3045095 lock mode S waiting
*** WE ROLL BACK TRANSACTION (2)
表中的记录
id c1 c2 c3
33 1 0 1
53 3 3 3

其中id为主键, c1c2 都是唯一索引,UNIQUE KEY uniq_c1 (c1), UNIQUE KEY uniq_c2 (c2)

SQL 执行顺序
Time Sess 1 Sess 2
@t1 begin
@t2 begin
@t3 update tu set c3=2 where c2=1
@t4 update tu set c3=2 where c2=1
@t5 insert into tu (c1,c2,c3) values(2,1,2)
@t6 insert into tu (c1,c2,c3) values(2,1,2)
死锁分析
  1. 我将其业务逻辑简化,这个事务里面的功能是同步数据,在同步的时候,首先是更新,如果发现更新的affect rows0,那么执行插入,如果插入失败,再执行更新。因此存在并发的情况下,两个事务都执行了更新,affect rows0
  2. Sess1@t3 时刻执行了更新,affect rows0,在c2(0,3)区间中加了GAP
  3. Sess2@t4 时刻执行了更新,affect rows0,同样在在c2(0,3)区间中加了GAP锁,不会发生等待
  4. Sess1@t5 时刻执行了插入,由于插入的时候需要申请插入意向锁(insert intention lock),而insert intention lock锁和已存在的GAP是冲突的,也就是Sess1 需要等待Sess2@t4 持有的GAP锁,发生了等待
  5. Sess2@t6 时刻执行了插入,由于插入的时候需要申请插入意向锁(insert intention lock),同样需要等待Sess1@t3 持有的GAP锁,导致了死锁的发生。

这个死锁的根本原因是因为更新了一条不存在的记录,而IX锁又是和已存在的GAP锁冲突,形成了死锁的条件

三、小结

本案例的解法 insert on duplicate key