死锁案例五

朱雀 2022-12-08 05:07 210阅读 0赞

###  ###

### 来源：公众号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\]

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);

#### 死锁场景 ####

![format_png][]

#### 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)

**日志分析**

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

本文转自杨奇龙老师的公众号（yangyidba），他长期关注于数据库技术以及性能优化，故障案例分析，数据库运维技术知识分享，个人成长和自我管理等主题，欢迎扫码关注。

![format_png 1][]

**扩展阅读**

*  [死锁案例之四][Link 1]  
    
 *  [死锁案例之三][Link 2]  
    
 *  [死锁案例之二][Link 3]  
    
 *  [死锁案例之一][Link 4]
 *  [漫谈死锁][Link 5]
 *  [如何阅读死锁日志][Link 6]

全文完。

Enjoy MySQL :)

知数堂新课程K8S上线了

扫码开启新的学习之旅吧

![format_png 2][]

[format_png]: /images/20221123/def960cca7054e008a86627baf867dc6.png
[format_png 1]: /images/20221123/b045989169c2484aa4a86a5121244153.png
[Link 1]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b49018a4cc017643fed70ce3316e4dd07042fe286f51a9bbbc906aedfa2d48b50870aedb3&idx=1&mid=2653934443&scene=21&sn=b60c700cbf90d84a48b060b9ed1fcaa5#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b49468a4cc050c699592a4970a33c61db386589f6199b8d9d6a0b1ac4eb3aabc5d52aa3dc&idx=1&mid=2653934380&scene=21&sn=3266e158add079c382329644e65d5318#wechat_redirect
[Link 3]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b49628a4cc0742336c9684c704012198da94e28aff48be179b553e2557cd2a125fd13232d&idx=1&mid=2653934344&scene=21&sn=9b1a4ec019b19dd8a8367e34e3b11ee6#wechat_redirect
[Link 4]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b48b38a4cc1a57f5e3bdbacb09c3ced1bf70484aca7de764c651e2ce40688ad7aa90704b3&idx=1&mid=2653934297&scene=21&sn=1b4415f14d1350a00cf4866817d2395e#wechat_redirect
[Link 5]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b542f8a4cdd39f6195aceb447b7dda3c043af8a00e4048abc66f1150d122762ba93220940&idx=1&mid=2653933125&scene=21&sn=54e4c1a12223c45e4232e2227d7d19da#wechat_redirect
[Link 6]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b48b38a4cc1a57362dba651734cd5aea259b9ebd770685f54927f7877f98f3279c5e4c072&idx=2&mid=2653934297&scene=21&sn=c5d2afc9fab595801f640ab616f03bde#wechat_redirect
[format_png 2]: /images/20221123/df74ee6e8b9245b8aa2e5d1db45b3d46.png