欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

线上MySQL死锁分析——索引设置不当导致的死锁

程序员文章站 2022-04-17 14:39:11
...

文章目录

1. 背景

9月4号负责的系统接入了在线诊断分析平台，其中的运行时Java异常追踪工具能够捕获并上报线上异常。接入后发现系统会频繁的产生org.springframework.dao.DeadlockLoserDataAccessException异常，具体异常信息为：

Caused by: org.springframework.dao.DeadlockLoserDataAccessException:
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: UPDATE iot_vehicle_alarm SET alarm_type = ?, vehicle_number = ?, sensor_code = ?, status = ?, end_time = ?, update_user = ?, update_time = now(), sys_version = sys_version + 1 WHERE status=0 AND vehicle_number = ? AND sensor_code = ? AND alarm_type = ? AND begin_time < ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; SQL []; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:262) ~[spring-jdbc-4.3.25.RELEASE.jar:4.3.25.RELEASE]
	... 67 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
	at sun.reflect.GeneratedConstructorAccessor160.newInstance(Unknown Source) ~[?:?]
	... 67 more

上述业务日志表明，MySQL发生了死锁，导致死锁的SQL是一条UPDATE语句，同时死锁所在的其中一个事务被回滚掉了。

PS:

运行时Java异常追踪工具的基本原理

利用Java虚拟机工具接口（JVMTI）提供的调试功能，可以查看、修改程序运行时状态、设置一些回调函数。

运行时异常追踪工具通过注册Exception回调函数，捕获异常发生事件。获取线程的调用栈及本地变量，并利用广度优先算法获取本地变量引用的对象。最后将信息汇总发送到服务端。

一般来说，为了均衡故障诊断和业务性能，会限制变量抓取的深度和广度，同时也会设置数据上报缓冲区。

2. MySQL InnoDB的锁机制

分析死锁前需要先了解下MySQL InnoDB引擎的锁机制。详见：MySQL InnoDB的锁与算法

2.1 MySQL中的锁类型

InnoDB中的锁有latch和lock，lock用于保护数据库的内容，比如表、页、行，其加锁的对象是事务。

MySQL在Server层有全局锁和表级锁，行锁由存储引擎自己实现，比如InnoDB支持行锁而MyISAM就不支持。

全局锁

全局锁对整个数据库实例加锁。

表级锁

表级锁主要分为两种：表锁和元数据锁（meta data lock，MDL）。元数据锁的作用是防止DDL和CRUD操作并发冲突，避免在读取或者更新表数据期间，表结构发生变更，导致数据无法对齐。

行锁

行锁是对数据库表中行记录的锁，InnoDB中的行锁是作用在所使用到的索引树上（不看WHERE条件）的， 通过锁住索引树中的行实现。

InnoDB中行锁有三种算法：

  • Record Lock：单个行记录的锁，有两种类型：
    • 共享锁（S）：读锁
    • 排他锁（X）：写锁
  • Gap Lock：间隙锁，锁定一个范围，但不锁定记录本身。间隙锁之间互不冲突，与间隙锁冲突的是往这个间隙里插入记录这个操作（插入意向锁），Gap Lock解决了幻读问题。
  • Next-Key Lock：Record Lock + Gap Lock，是一个左开右闭区间的锁，即锁定一个范围也锁定记录本身。

2.2 行锁的加锁规则

两阶段锁

由于数据库事先不知道会访问到哪些数据，无法对使用到的数据进行一次性加锁。所以，在InnoDB中行锁是在需要的时候，在查找过程中访问到相应的行时才会进行加锁。但并不会立即释放，而是要到事务结束的时候在进行统一进行释放。这就是两阶段锁协议，逐行加锁、统一释放。

加锁规则

在MySQL5.7默认可重复读RR的隔离级别下，行锁的加锁规则是：

  1. 行锁的默认算法是Next-Key Lock，是一个左开右闭的区间，锁住当前记录及其左区间。
  2. 锁是在需要的时候，在查找过程中访问到相应的行时才会进行加锁。
  3. 在索引树上进行等值查询时（即通过B+树定位到页，再通过页内的稀疏目录定位到行的过程），若加锁的对象是唯一索引，则Next-Key Lock会退化为Record Lock；若查询条件没有命中行，则Next-Key Lock退化为Gap Lock。
  4. 在索引树上进行等值扫描时（通过链表顺序访问叶子节点行记录），行锁算法是默认的Next-Key Lock，若扫描终止时最后一个记录不满足条件时，则Next-Key Lock退化为Gap Lock。
  5. 在索引树上进行范围扫描时，行锁不退化。
  6. Next-Key Lock加锁顺序：Gap Lock + Record Lock

PS:

对于使用到的索引，在索引树*问到的行均需要加锁，不看WHERE条件。

使用二级索引时，若需回表，则还需要在聚集索引上进行加锁。

2.3 死锁检测机制

并发系统中不同线程出现资源循环依赖并进入无限等待的状态，称之为死锁，即互相持有对方所需要的锁。

InnoDB主要采用两种方式来预防死锁：超时获取+基于等待图的主动检测。

超时获取

当获取锁的等待超过一定时间时，自动退出等待并回滚事务。超时获取的主要缺点在于时间阈值不好确定。

基于等待图（wait-for graph）的主动检测

根据事务所持有的锁、以及尝试获取锁的信息，绘制事务之间的等待图，若图中存在回路，则说明存在死锁。此时，InnoDB会主动回滚undo量最少的事务。

等待图是一种主动检测策略，在每个事务请求锁并发生等待时，均会将其放入等待图中，并判断是否会产生回路。

InnoDB采用深度优先算法对等待图进行回路检测。

等待图的缺点在于会耗费较多的CPU资源。

3. 本文案例分析

3.1 分析InnoDB status日志

InnoDB status日志用以查看InnoDB存储引擎的运行时状态，主要包括内存状态、AHI状态、LOG信息、IO线程信息、锁信息等等。

如果曾经产生过死锁，那么LATEST DETECTED DEADLOCK章节将会展示最新的一次死锁信息，包括：涉及哪些事务、每个事务尝试执行的SQL、他们拥有和请求的锁，以及InnoDB最终决定回滚那个事务以打破死锁。

查看LATEST DETECTED DEADLOCK日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-09-04 22:21:05 0x7f4daa0cc700
*** (1) TRANSACTION:
TRANSACTION 38409539, ACTIVE 0 sec starting index read
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 20262659, OS thread handle 139971528328960, query id 6900388725 10.176.239.186 ccmp_rw Searching rows for update
UPDATE iot_vehicle_alarm SET alarm_type = 6, vehicle_number = '鄂AGM582', sensor_code = '1', status = 1,  end_time = '2020-09-04 21:48:55',  update_user = 'system', update_time = now(), sys_version = sys_version + 1
        WHERE status=0 AND vehicle_number = '鄂AGM582' AND sensor_code = '1' AND alarm_type = 6 AND begin_time < '2020-09-04 21:48:55'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 60 page no 10023 n bits 760 index idx_vehicle_number of table `ccmp`.`iot_vehicle_alarm` trx id 38409539 lock_mode X waiting
Record lock, heap no 391 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 9; hex e9848241474d353832; asc    AGM582;;
 1: len 8; hex 8000000001e13546; asc       5F;;

*** (2) TRANSACTION:
TRANSACTION 38409537, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
773 lock struct(s), heap size 73936, 3107 row lock(s), undo log entries 1
MySQL thread id 20265849, OS thread handle 139971542173440, query id 6900388720 10.176.239.186 ccmp_rw Searching rows for update
UPDATE iot_vehicle_alarm SET alarm_type = 6, vehicle_number = '鄂AGM582', sensor_code = '03', status = 1, end_time = '2020-09-04 21:48:55', update_user = 'system', update_time = now(),sys_version = sys_version + 1
        WHERE status=0 AND vehicle_number = '鄂AGM582' AND sensor_code = '03' AND alarm_type = 6 AND begin_time < '2020-09-04 21:48:55'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 60 page no 10023 n bits 760 index idx_vehicle_number of table `ccmp`.`iot_vehicle_alarm` trx id 38409537 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 34 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 9; hex e9848241474d353832; asc    AGM582;;
 1: len 8; hex 8000000001e23978; asc       9x;;

 ...

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 60 page no 206870 n bits 608 index idx_vehicle_number of table `ccmp`.`iot_vehicle_alarm` trx id 38409537 lock_mode X waiting
Record lock, heap no 533 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 9; hex e9848241474d353832; asc    AGM582;;
 1: len 8; hex 8000000001e62ba7; asc       + ;;

*** WE ROLL BACK TRANSACTION (1)

日志中WAITING FOR THIS LOCK TO BE GRANTED表明发生死锁的两个事务在执行UPDATE语句时，循环等待车牌号索引树上的行锁。

UPDATE语句的业务含义是根据车牌号和探头编码解除该探头之前的所有报警，由于一辆车上的多个探头可能同时报警，所以两个UPDATE语句存在并发的可能性。但是，此时疑问有二：

  1. 两个UPDATE语句的车牌号一样，那么在车牌号索引树上的加锁顺序也是一致的，不存在交叉加锁，怎么会互相持有对方的锁呢；
  2. 为什么走了车牌号索引，这个UPDATE语句所操作的行记录完全不一样，倘若走了车牌号+探头编码索引，就不存在并发冲突了。

3.2 Explain 死锁SQL、查看表的索引信息

第二个疑问比较好验证，EXPLAIN 一下 UPDATE 语句，SHOW INDEX看一下表的索引信息.

EXPLAIN信息

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE iot_vehicle_alarm - ref idx_vehicle_number,idx_begin_time idx_vehicle_number 50 const 1567 0.05 Using where

索引信息

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
iot_vehicle_alarm 0 PRIMARY 1 id A 1976408 - - BTREE
iot_vehicle_alarm 1 idx_vehicle_number 1 vehicle_number A 19217 - - BTREE
iot_vehicle_alarm 1 idx_vehicle_team_code 1 vehicle_team_code A 112 - - YES BTREE
iot_vehicle_alarm 1 idx_type 1 type A 3 - - YES BTREE
iot_vehicle_alarm 1 idx_begin_time 1 begin_time A 1238122 - - BTREE
iot_vehicle_alarm 1 idx_end_time 1 end_time A 1293957 - - YES BTREE
iot_vehicle_alarm 1 idx_event_id 1 event_id A 1976408 - - BTREE

表中没有探头相关的索引，所以MySQL选择了车牌号这个索引。

3.3 查看业务代码

InnoDB status 日志和表的索引情况都无法解释UPDATE时为什么会产生循环等待，遂从业务代码里寻找线索。

这个SQL所在业务代码的作用是，在车探头产生了一个新类型报警的时候，会新增一条报警，并解除之前其它类型的报警。这里涉及了一个INSERT操作和一个UPDATE操作，并且在INSERT和UPDATE之间存在2个网络请求（发送MQ），拉长了事务的执行时间。

猜测

一个事务中同时有INSERT和UPDATE操作，猜想是不是插入意向锁和间隙锁的冲突导致了死锁。

假设两个事务的执行顺序为：

  1. 第一个事务执行了INSERT操作，此时需要在车牌号索引上加上插入意向锁，随后执行其它网络请求；
  2. 第二个事务和第一个事务一样，也在执行INSERT操作后去执行其它网络请求。
  3. 第一个事务继续执行UPDATE操作，由于使用的是非唯一索引的车牌号索引，所以会尝试对所有扫描到的行加上Next-Key Lock，此时间隙锁与插入意向锁冲突了，该事务陷入锁等待：等待被授予车牌号索引树上的间隙锁，即等待第二个事务释放插入意向锁。
  4. 第二个事务也开始执行UPDATE操作，与第一个事务相同的是，等待第一个事务释放插入意向锁。

由于InnoDB的两阶段加锁规则，已经持有的锁会直到事务结束才会释放，所以这两个事务会一直陷入循环等待，也就是产生了死锁。

3.4 实验验证猜想

按照猜想的执行顺序，在测试环境测试，两个事务分别交叉执行以下SQL：

START TRANSACTION;
INSERT ...
UPDATE ...

其中UPDATE的条件除了探头不一样外，其它全部一致。

执行顺序及结果

事务1 事务1
start
start
insert
insert
update（阻塞等待）
update（发生死锁）

同猜想的一致，发生了死锁，参看此时的InnoDB status日志，同线上产生了相同的死锁日志。

------------------------
LATEST DETECTED DEADLOCK
------------------------
200908  9:04:00
*** (1) TRANSACTION:
TRANSACTION 212884CB4, ACTIVE 47 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1248, 26 row lock(s), undo log entries 1
MySQL thread id 31643531, query id 504879635 192.168.162.16 eclp Searching rows for update
/* ApplicationName=IntelliJ IDEA 2020.1.2 */ UPDATE iot_vehicle_alarm
SET alarm_type     = 6, vehicle_number = '京AD33506', sensor_code    = 't1', status         = 1, end_time       = '2020-09-04 21:48:55', update_user    = 'system',update_time    = now(), sys_version    = sys_version + 1
WHERE status = 0 AND vehicle_number = '京AD33506' AND sensor_code = 't1' AND alarm_type = 6 AND begin_time < '2020-09-04 21:48:55'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 37807 page no 4 n bits 136 index `idx_vehicle_number` of table `cctmp`.`iot_vehicle_alarm` trx id 212884CB4 lock_mode X waiting
Record lock, heap no 67 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 10; hex e4baac41443333353036; asc    AD33506;;
 1: len 8; hex 80000000000001e3; asc         ;;

*** (2) TRANSACTION:
TRANSACTION 212884D84, ACTIVE 40 sec starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 31643954, query id 504882785 192.168.162.16 eclp Searching rows for update
/* ApplicationName=IntelliJ IDEA 2020.1.2 */ UPDATE iot_vehicle_alarm
SET alarm_type     = 6, vehicle_number = '京AD33506', ensor_code    = 't2', status         = 1, end_time       = '2020-09-04 21:48:55', update_user    = 'system', update_time    = now(), sys_version    = sys_version + 1
WHERE status = 0 AND vehicle_number = '京AD33506' AND sensor_code = 't2' AND alarm_type = 6 AND begin_time < '2020-09-04 21:48:55'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 37807 page no 4 n bits 136 index `idx_vehicle_number` of table `cctmp`.`iot_vehicle_alarm` trx id 212884D84 lock_mode X locks rec but not gap
Record lock, heap no 67 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 10; hex e4baac41443333353036; asc    AD33506;;
 1: len 8; hex 80000000000001e3; asc         ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 37807 page no 4 n bits 136 index `idx_vehicle_number` of table `cctmp`.`iot_vehicle_alarm` trx id 212884D84 lock_mode X waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 10; hex e4baac41443333353036; asc    AD33506;;
 1: len 8; hex 8000000000000020; asc         ;;

*** WE ROLL BACK TRANSACTION (2)

3.5 问题解决

发生死锁的事务并不存在实际上的并发冲突，因为他们操作的是完全不同的行记录，探头编码是不同的。但是由于索引设置的粒度过粗，导致使用了车牌号索引，在索引树上扫描了所有该车牌号的行记录并加上了锁，产生了共享资源竞争。

问题的解决方法也很简单，删除车牌号索引，增加车牌号+探头编码联合索引。

再次进行测试，第一个事务的UPDATE操作不阻塞等待，第二个事务的UPDATE也不死锁。

4. 如何预防死锁

降低锁粒度、只锁定必须的资源

减少锁的持有时间

将容易发生锁竞争的SQL语句放在事务的最后（两阶段锁协议）

以相同的顺序访问多张表

大事务化小事务

参考

InnoDB Standard Monitor and Lock Monitor Output

MySQL InnoDB的锁与算法

相关标签： 日常 mysql

上一篇： JUC练习，以及死锁和Lock（锁)的学习记录

下一篇： 分享: 解决mysql死锁的思路

推荐阅读