前言
遇到 MySQL 死锁问题, 我们应该怎么排查分析呢? 之前线上出现一个 insert on duplicate 死锁问题, 本文将基于这个死锁问题, 分享排查分析过程, 希望对大家有帮助.
死锁案发还原
表结构:
CREATE TABLE `song_rank` ( `id` int(11) NOT NULL AUTO_INCREMENT, `songId` int(11) NOT NULL, `weight` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `songId_idx` (`songId`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
隔离级别:
- MySQL> select @@tx_isolation;
- +-----------------+
- | @@tx_isolation |
- +-----------------+
- | REPEATABLE-READ |
- +-----------------+
- 1 row in set, 1 warning (0.00 sec)
数据库版本:
- +------------+
- | @@version |
- +------------+
- | 5.7.21-log |
- +------------+
- 1 row in set (0.00 sec)
关闭自动提交:
- MySQL> select @@autocommit;
- +--------------+
- | @@autocommit |
- +--------------+
- | 1 |
- +--------------+
- 1 row in set (0.00 sec)
- MySQL> set autocommit=0;
- Query OK, 0 rows affected (0.00 sec)
- MySQL> select @@autocommit;
- +--------------+
- | @@autocommit |
- +--------------+
- | 0 |
- +--------------+
- 1 row in set (0.00 sec)
表中的数据:
- MySQL> select * from song_rank;
- +----+--------+--------+
- | id | songId | weight |
- +----+--------+--------+
- | 1 | 10 | 30 |
- | 2 | 20 | 30 |
- +----+--------+--------+
- 2 rows in set (0.01 sec)
死锁案发原因:
并发环境下, 执行 insert into ... on duplicate key update... 导致死锁
死锁模拟复现:
事务一执行:
- MySQL> begin; // 第一步
- Query OK, 0 rows affected (0.00 sec)
- MySQL> insert into song_rank(songId,weight) values(15,100) on duplicate key update weight=weight+1; // 第二步
- Query OK, 1 row affected (0.00 sec)
- MySQL> rollback; // 第七步
- Query OK, 0 rows affected (0.00 sec)
事务二执行:
- MySQL> begin; // 第三步
- Query OK, 0 rows affected (0.00 sec)
- MySQL> insert into song_rank(songId,weight) values(16,100) on duplicate key update weight=weight+1; // 第四步
- Query OK, 1 row affected (40.83 sec)
事务三执行:
- MySQL> begin; // 第五步
- Query OK, 0 rows affected (0.00 sec)
- MySQL> insert into song_rank(songId,weight) values(18,100) on duplicate key update weight=weight+1; // 第六步
事务一, 事务二, 事务三执行:
步骤 | 事务一 | 事务二 | 事务三 |
---|---|---|---|
第一步 | begin; | ||
第二步 | insert into song_rank(songId,weight) values(15,100) on duplicate key update weight=weight+1; (Query OK, 1 row affected (0.00 sec) ) | ||
第三步 | begin; | ||
第四步 | insert into song_rank(songId,weight) values(16,100) on duplicate key update weight=weight+1; // 被阻塞 | ||
第五步 | begin; | ||
第六步 | insert into song_rank(songId,weight) values(18,100) on duplicate key update weight=weight+1; // 被阻塞 | ||
第七步 | rollback; | ||
结果 | Query OK, 1 row affected (40.83 sec) | ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
死锁浮出水面:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
死锁破案排查分析
遇到死锁问题时, 我们应该怎么处理呢? 分一下几个步骤
1. 查看死锁日志
当数据库发生死锁时, 可以通过以下命令获取死锁日志:
show engine innodb status;
上面例子 insert on duplicate 死锁问题的日志如下:
- *** (1) TRANSACTION:
- TRANSACTION 27540, ACTIVE 19 sec inserting
- MySQL tables in use 1, locked 1
- LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
- MySQL thread id 23, OS thread handle 14896, query id 582 localhost ::1 root update
- insert into song_rank(songId,weight) values(18,100) on duplicate key update weight=weight+1
- *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
- RECORD LOCKS space id 116 page no 4 n bits 72 index songId_idx of table `test2`.`song_rank` trx id 27540 lock_mode X
- locks gap before rec insert intention waiting
- Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
- 0: len 4; hex 80000014; asc ;;
- 1: len 4; hex 80000002; asc ;;
- *** (2) TRANSACTION:
- TRANSACTION 27539, ACTIVE 41 sec inserting, thread declared inside InnoDB 1
- MySQL tables in use 1, locked 1
- 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
- MySQL thread id 22, OS thread handle 6976, query id 580 localhost ::1 root update
- insert into song_rank(songId,weight) values(16,100) on duplicate key update weight=weight+1
- *** (2) HOLDS THE LOCK(S):
- RECORD LOCKS space id 116 page no 4 n bits 72 index songId_idx of table `test2`.`song_rank` trx id 27539 lock_mode X
- locks gap before rec
- Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
- 0: len 4; hex 80000014; asc ;;
- 1: len 4; hex 80000002; asc ;;
- *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
- RECORD LOCKS space id 116 page no 4 n bits 72 index songId_idx of table `test2`.`song_rank` trx id 27539 lock_mode X
- locks gap before rec insert intention waiting
- Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
- 0: len 4; hex 80000014; asc ;;
- 1: len 4; hex 80000002; asc ;;
2. 分析死锁日志
如何分析死锁日志呢? 分享一下我的思路
死锁日志分事务 1, 事务 2 拆分
找出发生死锁的 SQL
找出事务持有什么锁, 都在等待什么锁
SQL 加锁分析
事务 1 日志分析
从日志我们可以看到事务 1 正在执行的 SQL 为:
insert into song_rank(songId,weight) values(18,100) on duplicate key update weight=weight+1
该条语句正在等待索引 songId_idx 的插入意向排他锁:
lock_mode X locks gap before rec insert intention waiting
事务 2 日志分析
从日志我们可以看到事务 2 正在执行的 SQL 为:
insert into song_rank(songId,weight) values(16,100) on duplicate key update weight=weight+1
该语句持有一个索引 songId_idx 的间隙锁:
lock_mode X locks gap before rec
该条语句正在等待索引 songId_idx 的插入意向排他锁:
lock_mode X locks gap before rec insert intention waiting
锁相关概念补充(附):
考虑到有些读者可能对上面 insert intention 锁等不太熟悉, 所以这里这里补一小节锁相关概念.
官方文档
InnoDB 锁类型思维导图:
我们主要介绍一下兼容性以及锁模式类型的锁
1. 共享锁与排他锁:
InnoDB 实现了标准的行级锁, 包括两种: 共享锁(简称 s 锁), 排它锁(简称 x 锁).
共享锁(S 锁): 允许持锁事务读取一行.
排他锁(X 锁): 允许持锁事务更新或者删除一行.
如果事务 T1 持有行 r 的 s 锁, 那么另一个事务 T2 请求 r 的锁时, 会做如下处理:
T2 请求 s 锁立即被允许, 结果 T1 T2 都持有 r 行的 s 锁
T2 请求 x 锁不能被立即允许
如果 T1 持有 r 的 x 锁, 那么 T2 请求 r 的 x,s 锁都不能被立即允许, T2 必须等待 T1 释放 x 锁才可以, 因为 X 锁与任何的锁都不兼容.
2. 意向锁
意向共享锁( IS 锁): 事务想要获得一张表中某几行的共享锁
意向排他锁( IX 锁): 事务想要获得一张表中某几行的排他锁
比如: 事务 1 在表 1 上加了 S 锁后, 事务 2 想要更改某行记录, 需要添加 IX 锁, 由于不兼容, 所以需要等待 S 锁释放; 如果事务 1 在表 1 上加了 IS 锁, 事务 2 添加的 IX 锁与 IS 锁兼容, 就可以操作, 这就实现了更细粒度的加锁.
InnoDB 存储引擎中锁的兼容性如下表:
3. 记录锁(Record Locks)
记录锁是最简单的行锁, 仅仅锁住一行. 如:
SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE
记录锁永远都是加在索引上的, 即使一个表没有索引, InnoDB 也会隐式的创建一个索引, 并使用这个索引实施记录锁.
会阻塞其他事务对其插入, 更新, 删除
记录锁的事务数据(关键词: lock_mode X locks rec but not gap), 记录如下:
- RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
- trx id 10078 lock_mode X locks rec but not gap
- Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
- 0: len 4; hex 8000000a; asc ;;
- 1: len 6; hex 00000000274f; asc 'O;;
- 2: len 7; hex b60000019d0110; asc ;;
4. 间隙锁(Gap Locks)
间隙锁是一种加在两个索引之间的锁, 或者加在第一个索引之前, 或最后一个索引之后的间隙.
使用间隙锁锁住的是一个区间, 而不仅仅是这个区间中的每一条数据.
间隙锁只阻止其他事务插入到间隙中, 他们不阻止其他事务在同一个间隙上获得间隙锁, 所以 gap x lock 和 gap s lock 有相同的作用.
5.Next-Key Locks
Next-key 锁是记录锁和间隙锁的组合, 它指的是加在某条记录以及这条记录前面间隙上的锁.
6. 插入意向锁(Insert Intention)
插入意向锁是在插入一行记录操作之前设置的一种间隙锁, 这个锁释放了一种插入方式的信号, 亦即多个事务在相同的索引间隙插入时如果不是插入间隙中相同的位置就不需要互相等待.
假设有索引值 4,7, 几个不同的事务准备插入 5,6, 每个锁都在获得插入行的独占锁之前用插入意向锁各自锁住了 4,7 之间的间隙, 但是不阻塞对方因为插入行不冲突.
事务数据类似于下面:
- RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
- trx id 8731 lock_mode X locks gap before rec insert intention waiting
- Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
- 0: len 4; hex 80000066; asc f;;
- 1: len 6; hex 000000002215; asc " ;;
- 2: len 7; hex 9000000172011c; asc r ;;...
SQL 加锁分析:
通过分析死锁日志, 我们可以找到发生死锁的 SQL, 以及相关等待的锁, 我们再对对应的 SQL 进行加锁分析, 其实问题就迎刃而解了.
OK, 我们回到对应的 SQL,insert into song_rank(songId,weight) values(16,100) on duplicate key update weight=weight+1 执行过程到底加了什么锁呢? 加锁机制官方文档
insert 加锁策略:
insert 语句会对插入的这条记录加排他记录锁, 在加记录锁之前还会加一种 GAP 锁, 叫做插入意向 (insert intention) 锁, 如果出现唯一键冲突, 还会加一个共享记录 (S) 锁.
(SQL 加锁分析非常重要, 在这里给大家推荐一篇文章, 讲的非常好, 解决死锁之路 - 常见 SQL 语句的加锁分析)
insert on duplicate key 加锁验证
为了验证一下 insert on duplicate key 加锁情况, 我们拿上面 demo 的事务 1 和 2 在走一下流程.
事务 1:
- MySQL> begin; // 第一步
- Query OK, 0 rows affected (0.00 sec)
- MySQL> insert into song_rank(songId,weight) values(15,100) on duplicate key
- update weight=weight+1; // 第二步
- Query OK, 1 row affected (0.00 sec)
事务 2(另开窗口):
- MySQL> begin; // 第三步
- Query OK, 0 rows affected (0.00 sec)
- MySQL> insert into song_rank(songId,weight) values(16,100) on duplicate key
- update weight=weight+1; // 第四步
使用 show engine innodb status 查看当前锁请求信息, 如图:
有图可得:
事务 2 持有: IX 锁(表锁),gap x 锁, insert intention lock(在等待事务 1 的 gap 锁)
所以, insert on duplicate 执行过程会上这三把锁.
死锁原因分析
回归到本文开头介绍的死锁案发模拟现场 (事务 1,2,3) 以及死锁日志现场,
案发后事务 1 的锁:
案发后事务 2 的锁:
案发复原路线:
1. 首先, 执行事务 1 执行:
- begin;
- insert into song_rank(songId,weight) values(15,100) on duplicate key update weight=weight+1;
会获得 gap 锁(10,20),insert intention lock(插入意向锁)
2. 接着, 事务 2 执行:
- begin;
- insert into song_rank(songId,weight) values(16,100) on duplicate key update weight=weight+1;
会获得 gap 锁(10,20), 同时等待事务 1 的 insert intention lock(插入意向锁).
3. 再然后, 事务 3 执行:
- begin;
- insert into song_rank(songId,weight) values(18,100) on duplicate key update weight=weight+1;
会获得 gap 锁(10,20), 同时等待事务 1 的 insert intention lock(插入意向锁).
4. 最后, 事务 1 回滚(rollback), 释放插入意向锁, 导致事务 2,3 同时持有 gap 锁, 等待 insert intention 锁, 死锁形成!
锁模式兼容矩阵(横向是已持有锁, 纵向是正在请求的锁):
这是 MySql5.7 的一个 bug https://bugs.mysql.com/bug.php?id=52020
如何避免该 insert on duplicate 死锁问题
1. 把 insert on duplicate 改为 insert
- try{
- insert();
- }catch(DuplicateKeyException e){
- update();
- }
因为 insert 不会加 gap 锁, 所以可以避免该问题.
2. 更改 MySQL 版本
既然这是 MySql5.7 的一个 bug, 那么可以考虑更改 MySQL 版本.
3. 尽量减少使用 unique index.
gap 锁跟索引有关, 并且 unique key 和 foreign key 会引起额外的 index 检查, 需要更大的开销, 所以我们尽量减少使用不必要的索引.
本文总结(重要)
本文介绍了 MySql5.7 死锁的一个 bug. 我们应该怎样去排查死锁问题呢?
1.show engine innodb status; 查看死锁日志
2. 找出死锁 SQL
3.SQL 加锁分析
4. 分析死锁日志(持有什么锁, 等待什么锁)
5. 熟悉锁模式兼容矩阵, InnoDB 存储引擎中锁的兼容性矩阵.
参考与感谢
一条 Insert on duplicate 引发的血案 https://zhuanlan.zhihu.com/p/29349080
读 MySQL 源码再看 INSERT 加锁流程
解决死锁之路 - 了解常见的锁类型
MySQL InnoDB 锁 -- 官方文档 https://segmentfault.com/a/1190000014071758
MySQL 的锁机制 - 记录锁, 间隙锁, 临键锁 https://zhuanlan.zhihu.com/p/48269420
解决死锁之路 - 常见 SQL 语句的加锁分析
《MySQL 技术内幕》
个人公众号
如果你是个爱学习的好孩子, 可以关注我公众号, 一起学习讨论.
如果你觉得本文有哪些不正确的地方, 可以评论, 也可以关注我公众号, 私聊我, 大家一起学习进步哈.
来源: https://www.cnblogs.com/jay-huaxiao/p/11456921.html