【MySQL】死锁案例之四

一 前言
  死锁，其实是一个很有意思，也很有挑战的技术问题，大概每个DBA和部分开发同学都会在工作过程中遇见过 。关于死锁我会持续写一个系列的案例分析，希望能够对想了解死锁的朋友有所帮助。本文介绍一例三个并发insert 导致的死锁，根本原因还是在于insert 唯一键申请插入意向锁这个特殊的GAP锁。其实称呼插入意向锁 为 Insert Intention Gap Lock 更为合理。
二 案例分析
2.1 环境准备 
Percona server 5.6 RR模式

  1. CREATE TABLE `t6` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `a` int(11) DEFAULT NULL,
  4.   PRIMARY KEY (`id`),
  5.   unique KEY `idx_a` (`a`)
  6. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
  7. insert into t6 values(1,2),(2,8),(3,9),(4,11),(5,19)

sess1
sess2
sess3

begin;

insert into t6(id,a) values(6,15);
begin;

insert into t6(id,a) values(7,15);
begin;

insert into t6(id,a) values(8,15);

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

2.2 死锁日志

  1. ------------------------
  2. LATEST DETECTED DEADLOCK
  3. ------------------------
  4. 2017-09-18 10:03:50 7f78eae30700
  5. *** (1) TRANSACTION:
  6. TRANSACTION 462308725, ACTIVE 18 sec inserting, thread declared inside InnoDB 1
  7. mysql tables in use 1, locked 1
  8. LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
  9. MySQL thread id 3825465, OS thread handle 0x7f78eaef4700, query id 781148519 localhost root update
  10. insert into t6(id,a) values(7,15)
  11. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  12. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308725 lock_mode X insert intention waiting
  13. *** (2) TRANSACTION:
  14. TRANSACTION 462308726, ACTIVE 10 sec inserting, thread declared inside InnoDB 1
  15. mysql tables in use 1, locked 1
  16. 4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
  17. MySQL thread id 3825581, OS thread handle 0x7f78eae30700, query id 781148528 localhost root update
  18. insert into t6(id,a) values(8,15)
  19. *** (2) HOLDS THE LOCK(S):
  20. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308726 lock mode S
  21. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  22. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308726 lock_mode X insert intention waiting
  23. *** WE ROLL BACK TRANSACTION (2)

2.3 死锁分析
首先依然要再次强调insert 插入操作的加锁逻辑。
第一阶段: 唯一性约束检查，先申请LOCK_S + LOCK_ORDINARY
第二阶段: 获取阶段一的锁并且insert成功之后,插入的位置有Gap锁:LOCK_INSERT_INTENTION,为了防止其他insert唯一键冲突。
               新数据插入:LOCK_X + LOCK_REC_NOT_GAP
对于insert操作来说，若发生唯一约束冲突，则需要对冲突的唯一索引加上S Next-key Lock。从这里会发现，即使是RC事务隔离级别，也同样会存在Next-Key Lock锁，从而阻塞并发。然而，文档没有说明的是，对于检测到冲突的唯一索引，等待线程在获得S Lock之后，还需要对下一个记录进行加锁，在源码中由函数row_ins_scan_sec_index_for_duplicate进行判断.
其次 我们需要了解 锁的兼容性矩阵。

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

  1. INSERT操作之间不会有冲突。
  2. GAP,Next-Key会阻止Insert。
  3. GAP和Record,Next-Key不会冲突
  4. Record和Record、Next-Key之间相互冲突。
  5. 已有的Insert锁不阻止任何准备加的锁。

这个案例是三个会话并发执行的，我打算一步一步来分析每个步骤执行完之后的事务日志。
第一步 sess1 执行插入操作
insert into t6(id,a) values(6,15);

  1. ---TRANSACTION 462308737, ACTIVE 5 sec
  2. 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1
  3. MySQL thread id 3825779, OS thread handle 0x7f78eacd9700, query id 781149440 localhost root init
  4. show engine innodb status
  5. TABLE LOCK table `test`.`t6` trx id 462308737 lock mode IX

因为第一个插入的语句，所以唯一性冲突检查通过，成功插入(6,15). 此时sess1 会话持有(6,15)的LOCK_X|LOCK_REC_NOT_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."

第二步 sess2 执行插入操作
insert into t6(id,a) values(7,15);

  1. ---TRANSACTION 462308738, ACTIVE 4 sec inserting
  2. mysql tables in use 1, locked 1
  3. LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
  4. MySQL thread id 3825768, OS thread handle 0x7f78ea9c9700, query id 781149521 localhost root update
  5. insert into t6(id,a) values(7,15)
  6. ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
  7. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock mode S waiting
  8. ------------------
  9. TABLE LOCK table `test`.`t6` trx id 462308738 lock mode IX
  10. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock mode S waiting
  11. ---TRANSACTION 462308737, ACTIVE 66 sec
  12. 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
  13. MySQL thread id 3825779, OS thread handle 0x7f78eacd9700, query id 781149526 localhost root init
  14. show engine innodb status
  15. TABLE LOCK table `test`.`t6` trx id 462308737 lock mode IX
  16. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308737 lock_mode X locks rec but not gap

首先sess2的insert 申请了IX锁，因为sess1 会话已经插入成功并且持有唯一键 a=15的X 行锁 ，故而sess2 insert 进行唯一性检查，先申请LOCK_S + LOCK_ORDINARY ,事务日志列表中提示lock mode S waiting
第三部 sess3 执行插入操作
insert into t6(id,a) values(8,15);

  1. ---TRANSACTION 462308739, ACTIVE 3 sec inserting
  2. mysql tables in use 1, locked 1
  3. LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
  4. MySQL thread id 3825764, OS thread handle 0x7f78ea593700, query id 781149555 localhost root update
  5. insert into t6(id,a) values(8,15)
  6. ------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
  7. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308739 lock mode S waiting
  8. ------------------
  9. TABLE LOCK table `test`.`t6` trx id 462308739 lock mode IX
  10. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308739 lock mode S waiting
  11. ---TRANSACTION 462308738, ACTIVE 35 sec inserting
  12. mysql tables in use 1, locked 1
  13. LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
  14. MySQL thread id 3825768, OS thread handle 0x7f78ea9c9700, query id 781149521 localhost root update
  15. insert into t6(id,a) values(7,15)
  16. ------- TRX HAS BEEN WAITING 35 SEC FOR THIS LOCK TO BE GRANTED:
  17. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock mode S waiting
  18. ------------------
  19. TABLE LOCK table `test`.`t6` trx id 462308738 lock mode IX
  20. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock mode S waiting
  21. ---TRANSACTION 462308737, ACTIVE 97 sec
  22. 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
  23. MySQL thread id 3825779, OS thread handle 0x7f78eacd9700, query id 781149560 localhost root init
  24. show engine innodb status
  25. TABLE LOCK table `test`.`t6` trx id 462308737 lock mode IX
  26. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308737 lock_mode X locks rec but not gap

与会话sess2 的加锁申请流程一致，都在等待sess1释放锁资源。
第四步 sess1 执行回滚操作，sess2 不提交
sess1 rollback;
此时sess2 插入成功，sess3出现死锁，此时sess2 insert插入成功,还未提交,事务列表如下:

  1. ------------
  2. TRANSACTIONS
  3. ------------
  4. Trx id counter 462308744
  5. Purge done for trx s n:o < 462308744 undo n:o < 0 state: running but idle
  6. History list length 1866
  7. LIST OF TRANSACTIONS FOR EACH SESSION:
  8. ---TRANSACTION 462308737, not started
  9. MySQL thread id 3825779, OS thread handle 0x7f78eacd9700, query id 781149626 localhost root init
  10. show engine innodb status
  11. ---TRANSACTION 462308739, not started
  12. MySQL thread id 3825764, OS thread handle 0x7f78ea593700, query id 781149555 localhost root cleaning up
  13. ---TRANSACTION 462308738, ACTIVE 75 sec
  14. 5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
  15. MySQL thread id 3825768, OS thread handle 0x7f78eadce700, query id 781149608 localhost root cleaning up
  16. TABLE LOCK table `test`.`t6` trx id 462308738 lock mode IX
  17. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock mode S
  18. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock mode S
  19. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock_mode X insert intention
  20. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock mode S locks gap before rec

死锁的原因
 sess1 insert成功并针对a=15的唯一键加上X锁。
 sess2 执行insert 插入(6,15), 在插入之前进行唯一性检查发现和sess1的已经插入的记录重复键需要申请LOCK_S|LOCK_ORDINARY, 但与sess1 的(LOCK_X | LOCK_REC_NOT_GAP)冲突,加入等待队列,等待sess1 释放锁。
 sess3 执行insert 插入(7,15), 在插入之前进行唯一性检查发现和sess1的已经插入的记录重复键需要申请LOCK_S|LOCK_ORDINARY, 但与sess1 的(LOCK_X | LOCK_REC_NOT_GAP)冲突,加入等待队列,等待sess1 释放锁。
 sess1 执行rollback, sess1 释放索引a=15 上的排他记录锁(LOCK_X | LOCK_REC_NOT_GAP),此后 sess2和sess3 获得S锁(LOCK_S|LOCK_ORDINARY)成功,sess2和sess3都要请求索引a=15上的排他记录锁(LOCK_X | LOCK_REC_NOT_GAP),日志中提示 lock_mode X insert intention。由于X锁与S锁互斥，sess2和sess3都等待对方释放S锁，于是出现死锁，MySQL 选择回滚其中之一。

四 总结
    死锁分析是已经很有挑战的事情，尤其对于insert 唯一键冲突,要分多个阶段去申请，也要理解锁的兼容矩阵。对于这块我还有需要在学习了解的知识点，本文算是抛砖引玉，如有分析理解不正确的地方，望大家指正。

推荐文章 《MySQL insert 锁机制》 《insert into 加锁机制》

时间： 2024-10-30 12:40:16

【MySQL】死锁案例之四的相关文章

【MySQL】死锁案例之一

一 前言   死锁,其实是一个很有意思,也很有挑战的技术问题,大概每个DBA和部分开发同学都会在工作过程中遇见过 .关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋友有所帮助.二 案例分析2.1 环境说明MySQL 5.6 事务隔离级别为RR CREATE TABLE `ty` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `a` int(11) DEFAULT NULL,   `b` int(11) DEFAULT NULL,   PRI

【MySQL】死锁案例之二

一 前言    死锁,其实是一个很有意思,也很有挑战的技术问题,大概每个DBA都会在工作过程中遇见过 .关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋友有所帮助.本文源于我们的生产案例:并发申请gap锁导致的死锁案例,与之前的 死锁案例一不同,本案例是因为RR模式下两个事务中的sql可以获取同一个gap锁,导致对方事务的insert 相互等待,导致死锁的.二 案例分析测试环境准备Percona server 5.6.24  事务隔离级别为RR CREATE TABLE `t4` 

【MySQL】死锁案例之三

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

MySQL死锁的两个小案例

    最近花了些时间分析MySQL锁的内容,觉得越看越有意思. 我有个学习的习惯,有时候也不知道好还是不好,那就是喜欢直接上手练习,然后反过来练习理论.结果在学习锁的时候,感觉多多少少走了一些弯路,那就是对锁的基础的概念有一些混淆,虽然能够模拟出一些场景来,但是总是有一种隔靴搔痒的感觉,于是我就看了不少的博客,多多少少会有一些正面负面的影响,结果让我原本理解的地方又不大肯定了,所以这个时候捋一捋你学习的脉络就很重要,通过实践来得到结果,反推理论基础是好事,但是很多不明确的理解就需要通读官方文档

mysql死锁问题分析

线上某服务时不时报出如下异常(大约一天二十多次):"Deadlock found when trying to get lock;".       Oh, My God! 是死锁问题.尽管报错不多,对性能目前看来也无太大影响,但还是需要解决,保不齐哪天成为性能瓶颈.      为了更系统的分析问题,本文将从死锁检测.索引隔离级别与锁的关系.死锁成因.问题定位这五个方面来展开讨论.  图1 应用日志 1 死锁是怎么被发现的? 1.1 死锁成因&&检测方法      左图那

mysql死锁问题分析(转)

线上某服务时不时报出如下异常(大约一天二十多次):"Deadlock found when trying to get lock;".       Oh, My God! 是死锁问题.尽管报错不多,对性能目前看来也无太大影响,但还是需要解决,保不齐哪天成为性能瓶颈.     为了更系统的分析问题,本文将从死锁检测.索引隔离级别与锁的关系.死锁成因.问题定位这五个方面来展开讨论.  图1 应用日志 1 死锁是怎么被发现的? 1.1 死锁成因&&检测方法      左图那两

mysql去重案例，group_concat函数的用法

mysql 去重案例: select group_concat(distinct user_id), `tenant_id` ,  `create_time`  from `dtops_db_list` where `create_time`  >='2016-07-22 00:00:00' and `create_time` <='2016-07-28 23:59:59'  group by `tenant_id`  1.建表语句 wjj@>create table dtstack(

SQL Server并行死锁案例解析

原文:SQL Server并行死锁案例解析 并行执行作为提升查询响应时间,提高用户体验的一种有效手段被大家所熟知,感兴趣的朋友可以看我以前的博客SQL Server优化技巧之SQL Server中的"MapReduce", SQL Server优化器特性-位图过滤(Bitmap),然而正如我一直强调的,任何事物均有利弊,重点在于抉择.近日有朋友问我关于在今年7月份SQL Saturday中分享的并行执行中关于并行死锁的内容,这里我就详细解释下我举的实例中的并行死锁.      并行死锁

MySQL死锁问题分析及解决方法实例详解_Mysql

MySQL死锁问题是很多程序员在项目开发中常遇到的问题,现就MySQL死锁及解决方法详解如下: 1.MySQL常用存储引擎的锁机制 MyISAM和MEMORY采用表级锁(table-level locking) BDB采用页面锁(page-level locking)或表级锁,默认为页面锁 InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁 2.各种锁特点 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低 行级锁:开销大,加锁慢;