MySQL死锁实例分析

前言
死锁实际上是一个非常有趣且具有挑战性的技术问题。大概每个DBA和一些开发同学在工作中都会遇到。遇见了。我会继续写一系列关于死锁的案例分析，希望对想要了解死锁的朋友有所帮助。文章介绍了三个并发插入导致死锁的例子。根本原因在于insert unique key为插入意图锁申请了特殊的GAP锁。其实称插入更合理意向锁 插入意向间隙锁。
两个案例分析
2.1环境准备
Percona server 5.6 RR模式

sess1< /p>

2.2 死锁日志

------------------------

最新检测到的死锁

--------------------------------

2017-09 -18 10 :03:50 7f78eae30700

*** (1) 事务：

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

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

LOCK WAIT 4 个锁结构，堆大小 1184，2 个行锁，撤消日志条目 1

MySQL 线程 ID 3825465，操作系统线程句柄 0x7f78eaef4700，查询 ID 781148519 本地主机根更新

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

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

记录锁定空间 id 227 页号表 `test` 的 4 n 位 80 索引 `idx_a`。`t6` trx id 462308725 lock_mode X 插入意图等待

*** (2) TRANSACTION:

事务 462308726，活动 10 秒插入，线程在 sid 中​​声明e InnoDB 1

使用 1 个 MySQL 表，锁定 1

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

MySQL 线程 id 3825581，操作系统线程句柄 0x7f78eae30700，查询 id 781148528 localhost root update

插入 t6(id,a ) 值(8,15)

*** (2) 持有锁：

记录锁空间 id 227 第 4 页表 `test`.`t6` 的 n 位 80 索引 `idx_a` trx id 462308726 锁定模式 S

*** (2) 等待授予此锁定：

RECORD LOCKS 空间 id 227 页号 4 n 位 80 表 `test` 的索引 `idx_a`。`t6` trx id 462308726 lock_mode X 插入意图等待

*** WE ROLL BACK TRANSACTION(2)

2.3 死锁分析
首先strong>还是有必要再次强调一下插入操作的锁定逻辑。
第一阶段：唯一约束检查​​，首先申请LOCK_S + LOCK_ORDINARY
第二阶段： 获取第一阶段锁并插入成功后，插入位置有一个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函数来判断的。
其次我们需要了解锁的兼容性矩阵。

从兼容性矩阵中，我们可以得出以下结论：

INSERT 操作之间不存在冲突。

GAP、Next-Key 会阻止插入。

GAP和Record和Next-Key不会互相冲突

Record和Record和Next-Key会互相冲突。

现有的插入锁不会阻止任何要添加的锁。

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

- - -事务 462308737，活动 5 秒

1 个锁结构，堆大小 360，0 个行锁，撤消日志条目 1

MySQL 线程 id 3825779，操作系统线程句柄 0x7f78eacd9700，查询 id 781149440 localhost root init

显示引擎 innodb 状态

TABLE LOCK 表 `test`。 `t6` trx id 462308737 锁定模式 IX

由于第一个插入语句，唯一性冲突检查通过并且（6,15) 已成功插入。此时，sess1会话持有(6,15)的LOCK_X|LOCK_REC_NOT_GAP锁。请参阅“INSERT 对插入的行设置排它锁。此锁是索引记录锁，而不是下一个键锁（即没有间隙锁）并且不会阻止其他会话插入到该行之前的间隙中。插入行。”

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

---事务462308738，活动4秒插入

mysql表正在使用1，锁定1

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

MySQL 线程 ID 3825768，操作系统线程句柄 0x7f78ea9c9700，查询 ID 781149521 本地主机根更新

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

-------- TRX 已为此锁定等待 4 秒待授予：

记录锁空间 id 227 页号 4 n 位 80 表 `test` 的索引 `idx_a`。`t6` trx id 462308738 lock 模式 S 等待

------------------

TABLE LOCK 表 `test` .`t6` trx id 462308738 锁定模式 IX

RECORD LOCKS 空间 id 227 页号 4 n 位 80 表 `test` 的索引 `idx_a`。`t6` trx id 462308738 锁定模式S 等待

---事务 462308737，活动 66 秒

2 个锁结构，堆大小 360，1 个行锁，撤消日志条目1

MySQL线程ID 3825779，操作系统线程句柄0x7f78eacd9700，查询ID 781149526 localhost root init

显示引擎innodb状态

TABLE LOCK 表 `test`.`t6` trx id 462308737 锁定模式 IX

RECORD LOCKS 空间 id 227 页号 4 n 位 80 索引 `idx_a` of table `test` .`t6` trx id 462308737 lock_mode X 锁定了rec但没有锁定gap

首先，sess2的insert申请了IX锁。sess1会话已插入成功并持有X行用唯一钥匙 a=15 锁定。因此，sess2 insert会执行唯一性检查并首先申请LOCK_S + LOCK_ORDINARY。跨萨action日志列表提示lock mode S waiting
第三部分sess3执行插入操作
insert into t6(id,a)values(8,15);

---事务462308739，活动3秒插入

mysql表正在使用1，锁定1

LOCK WAIT 2 个锁结构，堆大小 360，1 个行锁，撤消日志条目 1

MySQL 线程 id 3825764，操作系统线程句柄 0x7f78ea593700，查询 id 781149555 localhost根更新

插入t6(id,a)值(8,15)

----- TRX已等待3要授予此锁的秒数：

记录锁空间 id 227 页号 4 n 位 80 表 `test` 的索引 `idx_a`。`t6` trx id 462308739 锁定模式 S 等待

--- --------------

表锁表`test`。`t6` trx id 462308739 锁定模式 IX

RECORD LOCKS 空间 id 227 页号 4 n 位 80 表 `test` 的索引 `idx_a`。`t6` trx id 462308739 锁定模式 S 等待

---交易 462308738，活跃 35 秒插入

mysql表正在使用1，锁定1

LOCK WAIT 2个锁结构，堆大小360，1个行锁，撤消日志条目 1

MySQL 线程 id 3825768，操作系统线程句柄 0x7f78ea9c9700，查询 id 781149521 localhost root 更新

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

------- TRX 已等待 35 秒以获取此锁定：

记录LOCKS 空间 id 227 页号 4 n 位 80 表 `test` 的索引 `idx_a`。`t6` trx id 462308738 锁定模式 S 等待

--- ------ ---------

TABLE LOCK 表 `test`.`t6` trx id 462308738 锁定模式 IX

RECORD LOCKS 空间id 227 页号 4 n 位 80 表 `test`.`t6` 的索引 `idx_a` trx id 462308738 锁定模式 S 等待

---事务 462308737 ，活动 97 秒

2 个锁结构，堆大小 360，1 个行锁，撤消日志条目 1

MySQL 线程 ID 3825779，操作系统线程句柄0x7f78eacd9700，查询id 781149560 localhost root init

显示引擎innodb status

TABLE LOCK 表 `test`.`t6` trx id 462308737 锁定模式 IX

RECORD LOCKS 空间 id 227 页号 4 n 位80 index `idx_a` of table `test`.`t6` trx id 462308737 lock_mode X 锁定rec但不锁定gap

与会话sess2添加锁申请流程相同，都是在等待sess1来释放锁资源。
第四步sess1执行回滚操作，sess2不提交
sess1回滚；
此时sess2插入成功，sess3死锁。此时，sess2 insert 被插入。成功了，还没提交。交易列表如下：

---------------------

交易

------------

Trx id 计数器 462308744

已完成 trx s n: o < 462308744 撤消 n:o < 0 状态：正在运行但空闲

历史列表长度 1866

事务列表每个会话：

---交易 462308737，未开始

MySQL 线程 id 3825779，操作系统线程句柄 0x7f78eacd9700，查询 id 781149626 localhost root init

显示引擎 innodb 状态

- --TRANSACTION 462308739，未启动

MySQL线程id 3825764，OS线程句柄0x7f78ea593700，查询id 781149555本地主机根清理

---TRANSACTION 462308738，活动 75 秒

5 个锁结构，堆大小 1184，3 个行锁，撤消日志条目 1

MySQL线程 id 3825768，操作系统线程句柄 0x7f78eadce700，查询 id 781149608 localhost root 清理

记录锁空间 id 227 页号 4 n 位 80 表“test”的索引“idx_a”。 `t6` trx id 462308738 锁定模式 S

RECORD LOCKS 空间 id 227 页号 4 n 位 80 表 `test` 的索引 `idx_a`。`t6` trx id 462308738 锁定模式 S

记录锁空间 id 227 页号 4 n 位 80 表 `test` 的索引 `idx_a`。`t6` trx id 462308738 lock_mode X 插入意图ion

RECORD LOCKS 空间 id 227 页号 4 n 位 80 表 `test` 的索引 `idx_a`。`t6` trx id 462308738 锁定模式 S 锁定记录之前的间隙

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

《MySQL死锁实例分析》的学习到此结束，希望能够解决大家的疑惑。理论与实践相结合，能够更好的帮助大家学习，去尝试吧！如果您想继续了解更多相关知识，请继续关注网站。小编会继续努力，给大家带来更多实用的文章！