死锁案例之五

一 前言

死锁其实是一个很有意思也很有挑战的技术问题,大概每个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语句执行是,系统会在记录上加上 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记录锁) 。 由于uk记录已存在,返回错误DB_DUPLICATE_KEY。

Step 2. 处理错误

由于上一步检测到duplicate key,因此第一步插入的聚集索引记录需要被回row_undo_ins

Step 3. 转换操作

从InnoDB层失败返回到Server层后,收到duplicate key错误,首先检索唯一键冲突的索引,并对冲突的索引记录(及聚集索引记录)加锁

随后确认转换模式以解决冲突:

#如果发生uk冲突的索引是最后一个唯一索引、没有外键引用、且不存在delete trigger时,使用UPDATE ROW的方式来解决冲突

#否则,使用DELETE ROW + INSERT ROW的方式解决冲突, 如果是主键冲突,则会先删除在插入。

Step 4. 更新记录

在该例中a 是主键,对聚集索引和二级索引的更新,都是采用标记删除+插入新记录的方式。对于聚集索引,由于PK列发生变化,采用delete + insert 聚集索引记录的方式更新。对于二级uk索引,同样采用标记删除 + 插入的方式。

三 案例分析

3.1 准备测试环境

事务隔离级别 REPEATABLE READ

数据准备

sess1

sess2

begin; replace into ix(a,b) values(5,18);

begin;

replace into ix(a,b) values(8,10);

replace into ix(a,b) values(9,12);

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

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 139645480060672, 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 139646312843008, 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

分析

replace into ix(a,b) values(8,10); 表中没有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 139646312843008, 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 139645480060672, 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


其他死锁文章

  • 漫谈死锁
  • 死锁案例之一
  • 死锁案例之二
  • 死锁案例之三
  • 死锁案例之四

原文发表时间:2018-01-06

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Java Web

SpringBoot技术栈搭建个人博客【项目准备】

5204
来自专栏数据和云

MySQL 大表优化方案,收藏了细看!

当 MySQL 单表记录数过大时,增删改查性能都会急剧下降,可以参考以下步骤来优化。 单表优化 除非单表数据未来会一直不断上涨,否则不要一开始就考虑拆分,拆分...

3219
来自专栏Rgc

提高查询数据速度

在实际项目中,通过设计表架构时,设计系统结构时,查询数据时综合提高查询数据效率 1.适当冗余 数据库在设计时遵守三范式,同时业务数据(对数据的操作,比如资料...

3768
来自专栏Web项目聚集地

Spring MVC+Spring+Mybatis实现支付宝支付功能(图文详解)

本教程详细介绍了如何使用ssm框架实现支付宝支付功能。本文章分为两大部分,分别是「支付宝测试环境代码测试」和「将支付宝支付整合到ssm框架」,详细的代码和图文解...

2541
来自专栏微信公众号:Java团长

Spring MVC+Spring+Mybatis实现支付宝支付功能(图文详解)

本教程详细介绍了如何使用ssm框架实现支付宝支付功能。本文章分为两大部分,分别是「支付宝测试环境代码测试」和「将支付宝支付整合到ssm框架」,详细的代码和图文解...

2441
来自专栏JavaEdge

MySQL各种存储引擎介绍与适用场景1.引擎的介绍第三方存储引擎:InfobrightTokuDBXtraDB、PBXT2.常用两种引擎的选择

7516
来自专栏杨建荣的学习笔记

关于db link权限分配的苦旅(二)(r7笔记第45天)

在几天前说过关于db link分配权限的问题,当时也折磨了我好一会儿 关于db link权限分配的苦旅(一) (r7笔记第42天) ? 大体的情况还是在11...

3324
来自专栏PHP在线

MySQL 在并发场景下的问题及解决思路

对于数据库系统来说在多用户并发条件下提高并发性的同时又要保证数据的一致性一直是数据库系统追求的目标,既要满足大量并发访问的需求又必须保证在此条件下数据的安全,为...

2184
来自专栏Java架构沉思录

MySQL在并发场景下的优化手段

对于数据库系统来说在多用户并发条件下提高并发性的同时又要保证数据的一致性一直是数据库系统追求的目标,既要满足大量并发访问的需求又必须保证在此条件下数据的安全,为...

1452
来自专栏杨建荣的学习笔记

简单分析oracle的数据存储(r2笔记89天)

在数据库的存储结构中,我们知道一般来说一个表都存储在对应的数据文件里,数据文件可以分为多个段,一般来说一个表会对应一个数据段,单纯考虑数据段的时候,数据段又可以...

3108

扫码关注云+社区

领取腾讯云代金券