MySQL死锁案例分析(四)

一、前言

这一篇是死锁记录的第四篇,记录了一个三个会话并发情况下形成的死锁

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

二、死锁输出

2018-03-26 13:34:48 0x7f9a34407700
*** (1) TRANSACTION:
TRANSACTION 3055184, ACTIVE 3180 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 98706, OS thread handle 140299540100864, query id 3302239 127.0.0.1 root updating
update tu set c3=5 where c1=4
*** (1) 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 3055184 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 3055185, ACTIVE 3176 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 98707, OS thread handle 140300278331136, query id 3302240 127.0.0.1 root updating
update tu set c3=5 where c1=4
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 240 page no 5 n bits 72 index uniq_c1 of table `db01`.`tu` trx id 3055185 lock mode S
*** (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 3055185 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)
表中的记录
id c1 c2 c3
33 1 0 2
53 3 3 2

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

SQL 执行顺序
Time Sess 1 Sess 2 Sess 3
@t1 begin
@t2 begin
@t3 begin
@t4 insert into tu (c1,c2,c3) values(4,4,4)
@t5 insert into tu (c1,c2,c3) values(4,4,4)
@t6 insert into tu (c1,c2,c3) values(4,4,4)
@t7 commit
@t8 update tu set c3=5 where c1=4
@t9 update tu set c3=5 where c1=4

业务上的主要逻辑是首先执行插入,如果插入成功,则提交,如果插入的时候报唯一键冲突,则执行更新。上面的SQL是相当于同时三个并发在执行,Sess1 插入成功,Sess2Sess3插入失败,执行更新

死锁分析
  1. Sess2在@t5时刻执行插入,由于插入的时候遇到Sess1插入的同一条记录,还没提交,唯一键冲突,因此需要在唯一键加上一个S锁,等待
  2. Sess3在@t6时刻执行插入,由于插入的时候遇到Sess1插入的同一条记录,还没提交,唯一键冲突,因此同样需要在唯一键加上一个S锁,等待
  3. Sess1在@t7时刻执行了提交,此时Sess2Sess3都同时拿到了S锁,但是由于唯一键冲突了,插入失败
  4. Sess2在@t8时刻执行更新,由于Sess2Sess3都同时拿到了S锁,更新需要申请记录上的X锁,和Sess3在记录上持有的S锁冲突,发生等待
  5. Sess3在@t9时刻执行更新,由于Sess2Sess3都同时拿到了S锁,更新需要申请记录上的X锁,和Sess2在记录上持有的S锁冲突,形成了死锁条件。

这个死锁的根本原因是因为插入的时候遇到唯一键冲突的情况下,会申请一个S锁,然后执行报错的情况下,依然持有

三、小结

本案例的解法改成insert on duplicate key 写法

Write a Reply or Comment

电子邮件地址不会被公开。 必填项已用*标注