一、前言
死锁其实是一个很有意思也很有挑战的技术问题,大概每个 DBA 和部分开发朋友都会在工作过程中遇见。关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋友有所帮助。本文是源于生产过程中一个死锁案例。
二、背景知识
官方文档[1]中表述:
"REPLACE is done like an INSERT if there is no collision on a unique key. Otherwise, an exclusive next-key lock is placed on the row to be replaced."
"如果没有唯一键冲突的时候,replace 操作和insert的加锁方式是一样的。但是如果有唯一键冲突的话,replace语句执行时,系统会在记录上加上 LOCK X next-key lock。"
如果觉得上面翻译比较简单,就看看下面的介绍[2]
-
create table t1(
-
a int auto_increment primary key,
-
b int,
-
c int,
-
unique key (b));
-
-
replace into t1(b,c) values (2,3)
Step 1 正常的插入逻辑
首先插入聚集索引,在上例中 a 列为自增列,由于未显式指定,每次 Insert 前都会生成一个不冲突的新值.
随后插入二级索引 b,由于其是唯一索引,在检查 duplicate key 时,加上记录锁,类型为 LOCK_X
对于普通的 INSERT 操作,当需要检查duplicate key 时,加 LOCK_S 锁,而对于 Replace into 或者 INSERT..ON DUPLICATE 操作,则加 LOCK_X 记录锁。当记录已存在,返回错误 DB_DUPLICATE_KEY。
Step 2 处理错误
由于上一步检测到 duplicate key,因此第一步插入的聚集索引记录需要回滚。
Step 3 转换操作
从 InnoDB 层失败返回到 Server 层后,收到 duplicate key 错误,首先检索唯一键冲突的索引,并对冲突的索引记录(及聚集索引记录)加锁
随后确认转换模式以解决冲突:
如果发生 uk 冲突的索引是最后一个唯一索引、没有外键引用、且不存在 delete trigger 时,使用 UPDATE ROW 的方式来解决冲突
否则,使用 DELETE ROW + INSERT ROW 的方式解决冲突, 如果是主键冲突,则会先删除在插入。
Step 4 更新记录
在该例中 a 是主键,对聚集索引和二级索引的更新,都是采用标记删除+插入新记录的方式。对于聚集索引,由于PK列发生变化,采用 delete + insert 聚集索引记录的方式更新。对于二级唯一键索引,同样采用标记删除 + 插入的方式。
三、案例分析
3.1 准备测试环境
事务隔离级别 REPEATABLE READ
数据准备
-
create table ix(id int not null auto_increment,
-
a int not null ,
-
b int not null ,
-
primary key(id),
-
idxa(a)
-
) engine=innodb default charset=utf8;
-
insert into ix(a,b) valuses(1,1),(5,10),(15,12);
死锁场景
3.2 过程分析
在每次执行一条语句之后都执行 show innodb engine status 查看事务的状态,
执行 replace into ix(a,b) values(5,8)的事务日志如下
-
---TRANSACTION 1872, ACTIVE 46 sec
-
4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
-
MySQL thread id 1156, OS thread handle 0672, query id 114 localhost msandbox
分析
replace into ix(a,b) values(5,8),因为记录 a=5 已经存在,则会对记录进行更新操作,对记录加 Next Key 锁 RECORD lock,GAP lock,
该事务产生 2 条 undo,持有 4 把锁 一把 IX 锁,1 个 a = 5 的行的行锁,2 个间隙锁 a 在 1-5,5-15 之间的间隙。
执行replace into ix(a,b) values(8,10)的事务日志如下
-
---TRANSACTION 1873, ACTIVE 3 sec inserting
-
mysql tables in use 1, locked 1
-
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s),
-
undo log entries 1
-
MySQL thread id 1155, OS thread handle 3008,
-
query id 117 localhost msandbox update
-
replace into ix(a,b) values(8,10)
-
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
-
RECORD LOCKS space id 24 page no 4 n bits 80
-
index idx_a of table `test`.`ix` trx id 1873
-
lock_mode X locks gap before rec insert intention waiting
-
---TRANSACTION 1872, ACTIVE 69 sec
-
4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
分析
表中没有 a=8 的记录,所以类似 insert into ix(a,b) values(8,10)。但是 a=8 与sess1 持有的 gap lock [5-15] 冲突,于是等待lock_mode X locks gap before rec insert intention waiting,并进入等待队列里面。这把锁是由 sess1 持有。
执行 replace into ix(a,b) values(9,12);事务日志如下执行该语句 sess2 立即报 发生死锁
-
*** (1) TRANSACTION:
-
TRANSACTION 1866, ACTIVE 8 sec inserting
-
mysql tables in use 1, locked 1
-
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
-
MySQL thread id 1155, OS thread handle 3008, query id 101 localhost msandbox update
-
replace into ix(a,b) values(8,10)
-
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
-
RECORD LOCKS space id 24 page no 4 n bits 80 index idx_a of table `test`.`ix` trx id 1866
-
lock_mode X locks gap before rec insert intention waiting
-
*** (2) TRANSACTION:
-
TRANSACTION 1865, ACTIVE 19 sec inserting
-
mysql tables in use 1, locked 1
-
5 lock struct(s), heap size 1136, 5 row lock(s),
-
undo log entries 3
-
MySQL thread id 1156, OS thread handle 0672,
-
query id 102 localhost msandbox update
-
replace into ix(a,b) values(9,12)
-
*** (2) HOLDS THE LOCK(S):
-
RECORD LOCKS space id 24 page no 4 n bits 80 index idx_a of table `test`.`ix` trx id 1865 lock_mode X
-
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
-
RECORD LOCKS space id 24 page no 4 n bits 80
-
index idx_a of table `test`.`ix` trx id 1865
-
lock_mode X locks gap before rec insert intention waiting
-
*** WE ROLL BACK TRANSACTION (1)
日志分析
-
replace into ix(a,b) values(9,12); 和插入(8,10) 类似需要申请 lock_mode X locks gap before rec insert intention waiting,并且进入申请锁的队列等待。
-
事务 T2 replace into ix(a,b) values(5,8); 该语句持有 4 把锁 一把 IX 锁,1 个 a=5 的行的行锁,2 个 a 在 1-5,5-15 之间的 GAP 锁。
-
事务 T1 replace into ix(a,b) values(8,10); a=8 与sess1 持有的 gap lock [5,15] 冲突,于是等待 lock_mode X locks gap before rec insert intention waiting,并进入等待队列里面。
-
事务 T2 replace into ix(a,b) values(9,12), a=9 也在[5-15]之间,需要等待 T1 的 insert intention lock 释放,T1 等待 T2(SQL1) ,T2(SQL2)等 T1 进而导致死锁 ,系统选择回滚事务 T1。
四、总结
分析定位到问题,怎么解决?目前给开发的建议是避免使用 replace into 方式,使用单条 select 检查 + insert 的方式 或者如果可以接受一定的死锁,可以减少并发执行改为串行。有兴趣的朋友可以自己复现,有更好的解决方法, 可以相互交流。
五、参考
[1] https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html 中阐述了各种语句的加锁方式,对死锁有兴趣的同学一定不要错过。
[2] http://mysqllover.com/?p=1312