MySQL死锁举例分析

本文主要介绍“MySQL死锁的实例分析”。在日常操作中，相信很多人对MySQL死锁的实例分析问题有所怀疑。边肖查阅了各种资料，整理出简单易用的操作方法，希望能帮你解答“MySQL死锁的实例分析”的疑惑！接下来，请和边肖一起学习！

一 前言

死锁，其实是一个很有意思也很有挑战性的技术问题。可能每个DBA和一些开发学生都会在工作过程中遇到。我会继续写一系列关于死锁的案例研究，希望对想了解死锁的朋友有所帮助。本文介绍了一个由三个并发插入导致死锁的案例。根本原因是INSERT的唯一键适用于插入方向锁的特殊GAP锁。实际上，更合理的说法是插入意图差距锁。二 案例分析2.1 环境准备

Percona服务器5.6 RR模式

会话1

会话2

会话3

开始；

插入t6(id，a)值(6，15)；

开始；

插入t6(id，a)值(7，15)；

开始；

插入t6(id，a)值(8，15)；

回滚；

错误1213 (40001):尝试获取锁时发现死锁；尝试重新启动事务

2.2 死锁日志

-

最新检测到的死锁

-

2017-09-18 10:03:50 7f 78 AE 30700

*** (1)交易：

事务462308725，活动18秒插入，线程在InnoDB 1中声明

正在使用的mysql表1，已锁定1

锁定等待4个锁定结构，堆大小1184，2行锁定，撤消日志条目1

MySQL线程id 3825465，OS线程句柄0x7f78eaef4700，查询id 781148519 localhost根更新

插入t6(id，a)值(7，15)

*** (1)等待授予此锁：

RECORD LOCKS空间id 227第4页n位80索引` idx_a `表`测试

`.`t6` trx id 462308725 lock_mode X insert intention waiting

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 选择回滚其中之一。

到此，关于“MySQL死锁举例分析”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注网站，小编会继续努力为大家带来更多实用的文章！