查看原文
其他

死锁案例五

杨奇龙 有赞coder 2019-06-04

文 | 杨一 on 运维

转 | 来源:公众号yangyidba


一、前言

死锁其实是一个很有意思也很有挑战的技术问题,大概每个 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]

  1. create table t1(

  2. a int auto_increment primary key,

  3. b int,

  4. c int,

  5. unique key (b));


  6. 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

数据准备

  1. create table ix(id int not null auto_increment,

  2. a int not null ,

  3. b int not null ,

  4. primary key(id),

  5. idxa(a)

  6. ) engine=innodb default charset=utf8;

  7. 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)的事务日志如下

  1. ---TRANSACTION 1872, ACTIVE 46 sec

  2. 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2

  3. 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)的事务日志如下

  1. ---TRANSACTION 1873, ACTIVE 3 sec inserting

  2. mysql tables in use 1, locked 1

  3. LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s),

  4. undo log entries 1

  5. MySQL thread id 1155, OS thread handle 3008,

  6. query id 117 localhost msandbox update

  7. replace into ix(a,b) values(8,10)

  8. ------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:

  9. RECORD LOCKS space id 24 page no 4 n bits 80

  10. index idx_a of table `test`.`ix` trx id 1873

  11. lock_mode X locks gap before rec insert intention waiting

  12. ---TRANSACTION 1872, ACTIVE 69 sec

  13. 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. *** (1) TRANSACTION:

  2. TRANSACTION 1866, ACTIVE 8 sec inserting

  3. mysql tables in use 1, locked 1

  4. LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1

  5. MySQL thread id 1155, OS thread handle 3008, query id 101 localhost msandbox update

  6. replace into ix(a,b) values(8,10)

  7. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

  8. RECORD LOCKS space id 24 page no 4 n bits 80 index idx_a of table `test`.`ix` trx id 1866

  9. lock_mode X locks gap before rec insert intention waiting

  10. *** (2) TRANSACTION:

  11. TRANSACTION 1865, ACTIVE 19 sec inserting

  12. mysql tables in use 1, locked 1

  13. 5 lock struct(s), heap size 1136, 5 row lock(s),

  14. undo log entries 3

  15. MySQL thread id 1156, OS thread handle 0672,

  16. query id 102 localhost msandbox update

  17. replace into ix(a,b) values(9,12)

  18. *** (2) HOLDS THE LOCK(S):

  19. RECORD LOCKS space id 24 page no 4 n bits 80 index idx_a of table `test`.`ix` trx id 1865 lock_mode X

  20. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

  21. RECORD LOCKS space id 24 page no 4 n bits 80

  22. index idx_a of table `test`.`ix` trx id 1865

  23. lock_mode X locks gap before rec insert intention waiting

  24. *** WE ROLL BACK TRANSACTION (1)

日志分析

  1. replace into ix(a,b) values(9,12); 和插入(8,10) 类似需要申请 lock_mode X locks gap before rec insert intention waiting,并且进入申请锁的队列等待。

  2. 事务 T2 replace into ix(a,b) values(5,8); 该语句持有 4 把锁 一把 IX 锁,1 个 a=5 的行的行锁,2 个 a 在 1-5,5-15 之间的 GAP 锁。

  3. 事务 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,并进入等待队列里面。

  4. 事务 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

扩展阅读

1. 漫谈死锁

2. 如何阅读死锁日志

3. 死锁案例一

4. 死锁案例二

5. 死锁案例三

6. 死锁案例四

-The End-

Vol.157












有赞技术团队

为 300 万商家,150 个行业,200 亿电商交易额

提供技术支持


微商城|零售|美业


微信公众号:有赞coder    微博:@有赞技术

技术博客:tech.youzan.com




The bigger the dream, 

the more important the team.

    您可能也对以下帖子感兴趣

    文章有问题?点此查看未经处理的缓存