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

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+树定位到页，再通过页内的稀疏目录定位到行的过程&