死锁案例之十

一 前言

死锁,其实是一个很有意思也很有挑战的技术问题,大概每个DBA和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋友有所帮助。

二 案例分析

2.1 业务场景

业务开发同学要做业务逻辑迁移,由A表迁移到B表,B表承担所有的类型的业务,他们的业务逻辑是:上游发task_id列表给下游,下游的业务根据一定的逻辑进行处理,在并发更新时遇到死锁。

注意: 因为B是新表,不一定有对应task_id的值。

2.2 环境说明

MySQL 5.6.24 事务隔离级别为RR

create table  x
(id int not null auto_increment,
 c int not null default  0,
 d int not null default 0,
 primary key(id),
 key idxcd(c,d));
insert into x(c,d) values(1,0),(3,0),(5,0),
(7,0),(10,0),(12,0),(14,0),(16,0);

2.3 测试用例

2.4 死锁日志

2018-04-20 23:05:55 0x7f75cdfff700
*** (1) TRANSACTION:
TRANSACTION 2235, ACTIVE 161 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 8 row lock(s), undo log entries 1
MySQL thread id 1016626, OS thread handle 140143880890112, query id 4070003 127.0.0.1 root updating
update x set d=1 where c in (5,10)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 44 page no 4 n bits 80 
index idxcd of table `test`.`x` trx id 2235 
lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 2237, ACTIVE 36 sec updating or deleting, thread declared inside InnoDB 4997
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
MySQL thread id 1016629, OS thread handle 140143944005376, query id 4070021 127.0.0.1 root updating
update x set d=1 where c=7
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 44 page no 4 n bits 80 
index idxcd of table `test`.`x` trx id 2237 
lock_mode X locks gap before rec
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 44 page no 4 n bits 80 
index idxcd of table `test`.`x` trx id 2237 
lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (2)

2.5 分析死锁日志

首先我们要再次强调insert 插入操作的加锁逻辑。

第一阶段: 唯一性约束检查,先申请LOCK_INSERT_INTENTION

第二阶段: 如果没有唯一键冲突,新数据插入完成之后:LOCK_X + LOCK_REC_NOT_GAP

对于insert操作来说,若发生唯一约束冲突,需要对冲突的唯一索引申请加上S Next-key Lock。如果其他会话中包含已经插入记录的事务没有提交,则申请加锁出现等待,show engine innodb status中的事务列表中会提示 lock mode S waiting

从这里会发现,即使是RC事务隔离级别,也同样会存在Next-Key Lock锁,从而阻塞并发。然而,文档没有说明的是,对于检测到冲突的唯一索引,等待线程在获得S Lock之后,还需要对下一个记录进行加锁,在源码中由函数row_ins_scan_sec_index_for_duplicate进行判断.

其次 我们需要了解锁的兼容性矩阵。

从兼容性矩阵我们可以得到如下结论:

INSERT操作之间不会有冲突。 GAP,Next-Key会阻塞插入意向锁INSERT_INTENTION GAP与Record,Next-Key不会冲突 Record与Record、Next-Key之间相互冲突。 已有的Insert锁不阻止任何准备加的锁。

另外 RR事务隔离级别下,对于通过索引(唯一或者非唯一)更新或者删除不存在的记录,会申请加上gap锁。

当update 更新被索引字段时,相当于删除之后重新插入新的记录,需要重新组织索引节点。

了解上面的基础知识,我们开始对死锁日志进行分析:

T1时刻 开始事务。

T2时刻 sess2 更新c=6的值,但是c=6 不存在,申请加上(5,0)---(7,0)的X gap锁,日志提示:持有idxcd of table test.x trx id 2207 lock_mode X

T3时刻 sess1 更新2行记录c=5,c=10,并且修改d=1,ses1 会申请(5,0),(10,0) 记录之间的Next-key锁,由于sess1需要插入新的记录(5,1)sess2 持有的X GAP 锁范围内,根据锁的兼容性矩阵,GAP,Next-Key会阻塞插入意向锁INSERT_INTENTION,故日志提示:index idxcd of table test.x trx id 2235 lock_mode X locks gap before rec insert intention waiting 。

T4时刻 sess2 更新c=7对应d=1,同样相当于insert (7,1) 在(5,0),(10,0)之间。根据兼容矩阵GAP,Next-Key会阻塞插入意向锁INSERT_INTENTION 故日志提示: index idxcd of table test.x trx id 2237 lock_mode X locks gap before rec insert intention waiting 。

至此,sess2 持有gap锁阻塞sess1 插入(5,1),sess1持有Next-key 阻塞sess2 插入(7,1),循环等待出现死锁。

2.6 解决方法

其实解决方法比较简单 把组合索引 idxcd(c,d) 中的d去掉,改为idxc(c),避免GAP/Next-key 阻塞插入意向锁INSERT_INTENTION 即可,开发同学18:10分左右修改索引之后,业务不再出现死锁。

三 小结

本文的死锁算是蛮有意思的一个案例:并发多个update更新二级索引列,相当于索引节点重新组织,更新等于删除加插入,在死锁日志出现了插入意向锁。第一次猜想的时候还以为有 多个insert 操作,但是实际上只有更新动作。

最后想说关于解决死锁问题的思路:

  1. 具备扎实的锁相关的基础知识。
  2. 单单根据死锁日志其实比较难以判断具体的sql执行情况,需要和开发同学沟通好(当然开发也要提提供完整的业务逻辑),理清业务执行sql的逻辑,然后去模拟测试。

原文发表时间:2018-04-21

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏MySQL实战分享

【MySQL经典案例分析】 Waiting for table metadata lock

2018年某个周末,接到连续数据库的告警,看到too many connection的报错信息,基本上可以把问题定位在...

1K60
来自专栏Guangdong Qi

ios 未能找到使用指定主机名的服务器原因之一

84120
来自专栏Debian社区

Postgres 10 开发者新特性

目前非常流行的RDBMS PostgresSQL已经在几周前发布了它的第10个版本。由于Postgres的可靠性、节约成本、成熟,当然还有它的开源,已经21岁的...

13220
来自专栏文渊之博

SQL Server内存

背景 最近一个客户找到我说是所有的SQL Server 服务器的内存都被用光了,然后截图给我看了一台服务器的任务管理器。如图 ? 这里要说明一下任务管理器不会完...

37570
来自专栏大宽宽的碎碎念

怎么避免MYSQL误删除避免混淆开发环境的DB和生产环境的DB用事务保护使用安全更新模式对DROP和TRUNCATE慎之又慎最终的招数最终的话

374130
来自专栏CSDN技术头条

应当使用 SQLite 的五个原因

SQLite 是非常优秀的数据库,能够在真实的生产环境中完成一些真正的工作。本文将列出五个我认为在2016年应当选用 SQLite 的原因。 ? 便于管理 不知...

29180
来自专栏idba

死锁案例之七

死锁,其实是一个很有意思也很有挑战的技术问题,大概每个DBA和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋...

6420
来自专栏数据和云

细致入微:Oracle中执行计划在Shared Pool中的存储位置探秘

这两天我一直在想一个问题,那就是 Oracle 的执行计划到底存储在什么地儿?它会是一种什么样的格式? 这里我试图对这个问题做一点我自己认为的解释,这个解释可能...

31550
来自专栏数据和云

腾讯游戏DBA利刃 - SQL审核工具介绍

作者介绍 ? 韩全安(willhan) 华中科技大学,硕士,现代数据库方向。2013年毕业,就职于腾讯到今,工作项目:TMySQL、SQL审核、InnoDB列压...

1.4K60
来自专栏Java技术分享圈

杨老师课堂之JavaEE三大框架Hibernate入门第一课

7510

扫码关注云+社区

领取腾讯云代金券