死锁案例之三

一 前言 死锁其实是一个很有意思也很有挑战的技术问题,大概每个DBA和部分开发朋友都会在工作过程中遇见。关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋友有所帮助。 二 背景知识 2.1 insert 锁机制 在分析死锁案例之前,我们先学习一下背景知识 insert 语句的加锁策略。我们先来看看官方定义:

  1. "An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting."

相信大部分的DBA同行都知道在事务执行insert的时候会申请一把插入意向锁(Insert Intention Lock)。在多事务并发写入不同数据记录至同一索引间隙的时候,并不需要等待其他事务完成,不会发生锁等待。 假设有一个索引记录包含键值4和7,不同的事务分别插入5和6,每个事务都会产生一个加在4-7之间的插入意向锁,获取在插入行上的排它锁,但是不会被互相锁住,因为数据行并不冲突。 但是如果遇到唯一键呢?

  1. "If a duplicate-key error occurs, a shared lock on the duplicate index record is set."

对于insert操作来说,若发生唯一约束冲突,则需要对冲突的唯一索引加上S Next-key Lock。从这里会发现,即使是RC事务隔离级别,也同样会存在Next-Key Lock锁,从而阻塞并发。然而,文档没有说明的是,对于检测到冲突的唯一索引,等待线程在获得S Lock之后,还需要对下一个记录进行加锁,在源码中由函数row_ins_scan_sec_index_for_duplicate进行判断。via (MySQL REPLACE死锁问题深入剖析 )。我们可以通过如下例子进行验证。 2.2 验证 准备环境 默认事务隔离级别为RC模式。

  1. CREATE TABLE t8 (
  2. a int AUTO_INCREMENT PRIMARY KEY,
  3. b int,
  4. c int,
  5. unique key ub(b)
  6. ) engine=InnoDB;
  7. insert into t8 values (NULL,1,2)

sess1

sess2

begin;

delete from t8 where b = 1;

begin;

insert into t8 values (NULL,1);

commit;

update t8 set c=13 where b=1;

2.3 过程分析 在每次执行一条语句之后都执行show innodb engine status查看事务的状态, 执行完 delete 语句,事务相关日志显示如下:

  1. ---TRANSACTION 462308671, ACTIVE 6 sec
  2. 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
  3. MySQL thread id 3796960, OS thread handle 0x7f78eaabe700, query id 781051370 localhost root init
  4. show engine innodb status
  5. TABLE LOCK table `test`.`t8` trx id 462308671 lock mode IX
  6. RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308671 lock_mode X locks rec but not gap
  7. RECORD LOCKS space id 232 page no 3 n bits 72 index `PRIMARY` of table `test`.`t8` trx id 462308671 lock_mode X locks rec but not gap

从日志中我们可以看到 delete语句获取了唯一索引ub和主键两个行级锁(lock_mode X locks rec but not gap) 。 执行完 insert 之后 再查看innodb engine status,事务相关日志显示如下:

  1. LIST OF TRANSACTIONS FOR EACH SESSION:
  2. ---TRANSACTION 462308676, ACTIVE 4 sec inserting
  3. mysql tables in use 1, locked 1
  4. LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
  5. MySQL thread id 3796966, OS thread handle 0x7f78ea5c4700, query id 781051460 localhost root update
  6. insert into t8 values (NULL,1,2)
  7. ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
  8. RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308676 lock mode S waiting
  9. ------------------
  10. TABLE LOCK table `test`.`t8` trx id 462308676 lock mode IX
  11. RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308676 lock mode S waiting
  12. ---TRANSACTION 462308671, ACTIVE 70 sec
  13. 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
  14. MySQL thread id 3796960, OS thread handle 0x7f78eaabe700, query id 781051465 localhost root init
  15. show engine innodb status
  16. TABLE LOCK table `test`.`t8` trx id 462308671 lock mode IX
  17. RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308671 lock_mode X locks rec but not gap
  18. RECORD LOCKS space id 232 page no 3 n bits 72 index `PRIMARY` of table `test`.`t8` trx id 462308671 lock_mode X locks rec but not gap

根据官方的介绍,并结合日志,我们可以看到insert into t8 values (NULL,1,2)在申请一把S Next-key-Lock , 显示lock mode S waiting。这里想给大家说明的是在innodb 日志中如果提示 lock mode S /lock mode X ,其实都是gap锁,如果是行记录锁 会提示but not gap ,请读者朋友们在自己分析死锁日志的时候注意。 sess1 delete语句提交之后,sess2的insert 不要提交,不要提交,不要提交。再次查看innodb engine status,事务相关日志显示如下:

  1. ------------
  2. TRANSACTIONS
  3. ------------
  4. Trx id counter 462308678
  5. Purge done for trxs n:o < 462308678 undo n:o < 0 state: running but idle
  6. History list length 1845
  7. LIST OF TRANSACTIONS FOR EACH SESSION:
  8. ---TRANSACTION 462308671, not started
  9. MySQL thread id 3796960, OS thread handle 0x7f78eaabe700, query id 781051526 localhost root init
  10. show engine innodb status
  11. ---TRANSACTION 462308676, ACTIVE 41 sec
  12. 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
  13. MySQL thread id 3796966, OS thread handle 0x7f78ea5c4700, query id 781051460 localhost root cleaning up
  14. TABLE LOCK table `test`.`t8` trx id 462308676 lock mode IX
  15. RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308676 lock mode S
  16. RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308676 lock mode S locks gap before rec

sess1中的事务因为提交已经结束。innodb中的事务列表中只剩下sess2 中的insert 的事务了。从获取锁的状态上看insert获取一把S Next-key Lock 锁和插入行之前的S GAP锁。看到这里大家是否有疑惑,官方文档说:

INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.

会对insert成功的记录加上一把X 行锁,为什么看不见呢?我们再在sess1 中执行update t8 set c=13 where b=1; 并查看事务日志

  1. ------------
  2. TRANSACTIONS
  3. ------------
  4. Trx id counter 462308679
  5. Purge done for trxs n:o < 462308678 undo n:o < 0 state: running but idle
  6. History list length 1845
  7. LIST OF TRANSACTIONS FOR EACH SESSION:
  8. ---TRANSACTION 462308678, ACTIVE 12 sec starting index read
  9. mysql tables in use 1, locked 1
  10. LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
  11. MySQL thread id 3796960, OS thread handle 0x7f78eaabe700, query id 781059217 localhost root updating
  12. update c set c=13 where b=1
  13. ------- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED:
  14. RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308678 lock_mode X locks rec but not gap waiting
  15. ------------------
  16. TABLE LOCK table `test`.`t8` trx id 462308678 lock mode IX
  17. RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308678 lock_mode X locks rec but not gap waiting
  18. ---TRANSACTION 462308676, ACTIVE 5113 sec
  19. 4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
  20. MySQL thread id 3796966, OS thread handle 0x7f78ea5c4700, query id 781059230 localhost root init
  21. show engine innodb status
  22. TABLE LOCK table `test`.`t8` trx id 462308676 lock mode IX
  23. RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308676 lock mode S
  24. RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308676 lock mode S locks gap before rec
  25. RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308676 lock_mode X locks rec but not gap

从日志中可以看到 sess2的事务持有的锁多了一把 lock_mode X locks rec but not gap,也即是 sess2 对 insert 成功的记录加上的X 行锁。 分析至此,对于并发insert造成唯一键冲突的时候 insert的加锁策略是:

  1. 第一阶段 唯一性约束检查,先申请LOCK_S + LOCK_ORDINARY
  2. 第二接入 获取阶段一的锁并且insert成功之后
  3. 插入的位置有Gap锁:LOCK_INSERT_INTENTION,为了防止其他insert 唯一键冲突。
  4. 新数据插入:LOCK_X + LOCK_REC_NOT_GAP

三 案例分析 本案例是两个事务并发insert 唯一键冲突 和gap锁一起导致的死锁案例。 3.1 环境

  1. create table t7(
  2. id int not null primary key auto_increment,
  3. a int not null ,
  4. unique key ua(a)
  5. ) engine=innodb;
  6. insert into t7(id,a) values(1,1),(5,4),(20,20),(25,12);

3.2 测试用例

T1

T2

begin;

begin;

insert into t7(id,a) values(26,10);

insert into t7(id,a) values(30,10);

insert into t7(id,a) values(40,9);

3.3 死锁日志

  1. ------------------------
  2. LATEST DETECTED DEADLOCK
  3. ------------------------
  4. 2017-09-17 15:15:03 7f78eac15700
  5. *** (1) TRANSACTION:
  6. TRANSACTION 462308661, ACTIVE 6 sec inserting
  7. mysql tables in use 1, locked 1
  8. LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
  9. MySQL thread id 3796966, OS thread handle 0x7f78ead9d700, query id 781045166 localhost root update
  10. insert into t7(id,a) values(30,10)
  11. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  12. RECORD LOCKS space id 231 page no 4 n bits 72 index `ua` of table `test`.`t7` trx id 462308661 lock mode S waiting
  13. *** (2) TRANSACTION:
  14. TRANSACTION 462308660, ACTIVE 43 sec inserting, thread declared inside InnoDB 5000
  15. mysql tables in use 1, locked 1
  16. 4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
  17. MySQL thread id 3796960, OS thread handle 0x7f78eac15700, query id 781045192 localhost root update
  18. insert into t7(id,a) values(40,9)
  19. *** (2) HOLDS THE LOCK(S):
  20. RECORD LOCKS space id 231 page no 4 n bits 72 index `ua` of table `test`.`t7` trx id 462308660 lock_mode X locks rec but not gap
  21. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  22. RECORD LOCKS space id 231 page no 4 n bits 72 index `ua` of table `test`.`t7` trx id 462308660 lock_mode X locks gap before rec insert intention waiting
  23. *** WE ROLL BACK TRANSACTION (1)

日志分析 我们从时间线维度分析: 事务T2 insert into t7(id,a) values(26,10)语句insert 成功,持有a=10 的X 行锁(X locks rec but not gap) 事务T1 insert into t7(id,a) values(30,10),因为T2 的第一条insert已经插入a=10的记录,事务T1的 insert a=10 则发生唯一约束冲突,需要申请对冲突的唯一索引加上S Next-key Lock (也即是 lock mode S waiting ) 这是一个间隙锁会申请锁住[4,10],[10,20]之间的gap区域。从这里会发现,即使是RC事务隔离级别,也同样会存在Next-Key Lock锁,从而阻塞并发。 事务T2 insert into t7(id,a) values(40,9) 该语句插入的a=9 的值在 事务T1申请的gap锁[4,10]之间,故需事务T2的第二条insert语句要等待事务T1的S-Next-key Lock锁释放,在日志中显示lock_mode X locks gap before rec insert intention waiting。 四 总结 本文案例和知识点一方面从官方文档获取,另一方面是根据何登成和姜承尧两位MySQL技术大牛的技术分享整理,算是站在巨人的肩膀上的学习总结。在研究分析死锁案例的过程中,insert 的意向锁 和 gap 锁这种类型的锁是比较难分析的,相信通过上面的分析总结大家能够学习到 insert的锁机制 ,如何加锁,如何进行 insert 方面死锁分析。

原文发表时间:2017-10-12

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

一个不懂业务的DBA不是好的DBA

编辑手记:懂业务,懂系统逻辑,你才能做一个更好的DBA。 在数据库巡检中发现一个MES生产信息数据库中一个存储过程中一条SQL单次逻辑读为2100,且执行很频繁...

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

关于奇怪的并行进程分析(二) (r6笔记第46天)

前几天的并行问题自己分析了下,也算有了一些进展,但是目前还没有找到让人信服的理由,有些读者也比较关心这个问题,所以第二篇中会把自己的分析过程写出来,第三篇中应该...

2623
来自专栏java一日一条

MySQL 性能优化的最佳 20+ 条经验

今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显。关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我 们程序员需要去关注的...

492
来自专栏java一日一条

MySQL 性能优化的最佳 20+ 条经验

今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显。关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我 们程序员需要去关注的...

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

生产环境sql语句调优实战第二篇(r2第38天)

在生产环境通过sql monitor监控到有一条sql执行效率很差。执行了大约5个小时,得到的sql monitor报告如下: Global Informat...

2907
来自专栏PHP实战技术

MySQL性能优化的最佳20+条经验

今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显。关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事...

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

分区表的一个持续改进方案(r9笔记第53天)

今天看到一个同事发了一封邮件,是关于分区的,他说目前某个表的分区需要添加,为了保险起见,让我先添加三年的。这里折射出几个问题。 1.如果没有这位开发同学提醒,我...

3134
来自专栏james大数据架构

聚合索引(clustered index) / 非聚合索引(nonclustered index)

以下我面试经常问的2道题..尤其针对觉得自己SQL SERVER 还不错的同志.. 呵呵 很难有人答得好.. 各位在我收集每个人擅长的东西时,大部分都把SQL...

6455
来自专栏好好学java的技术栈

微信支付和支付宝支付到springmvc+spring+mybatis环境全过程(支付宝和微信支付)

2542
来自专栏学习有记

聚集索引:SQL Server 进阶 Level 3

1763

扫码关注云+社区

领取腾讯云代金券