线上 MySql 事务死锁，应该怎么排查解决？ _MySql

01. MySQL 事务死锁现象及原因初步判断
做IT的几乎每天都接触 MySql，但是 Mysql 事务死锁却并不常见，前段时间就让我遇到了。异常日志如下

文章插图

从日志看是发生了 Lock wait timeout exceeded 异常。
Lock wait timeout exceeded：后提交的事务等待前面处理的事务释放锁，但是在等待的时候超过了mysql的锁等待时间，就会引发这个异常。

PreparedStatementCallback; SQL [UPDATE sf_wx_keyword_ruleSET status = ?,last_update_time = last_update_timeWHERE id = ?];Lock wait timeout exceeded;try restarting transaction;

发生异常的代码主要逻辑如下

文章插图

文章插图

分析后其实是因为一个处理流程里开了两个事务，并更新的同一条数据，导致的事务间死锁。
外层方法通过@Transactional 开启了事务1（@t1），对 sf_wx_keyword_rule 一条数据做更新，内层方法通过 REQUIRES_NEW 又开启了一个新事务2（@t2），并对sf_wx_keyword_rule 的同一条数据做更新。

begin @t1;UPDATE table SET status = ? WHERE id = 1begin @t2;UPDATE table SET status = ? WHERE id = 1commit @t2;commit @t1;

结论：由于 @t1 和 @t2 更新的是同一条数据，所以 @t2 的执行需要依赖 @t1 的提交，而@t1 的提交又需要 @t2 执行完。所以两个事务互相等待对方提交导致死锁。
02. 复现及深层原因追踪2.1 复现
为了搞清楚事务死锁，及死锁期间 MySql 的数据状态，新建 test1 表重复上述操作

文章插图

文章插图

过了大概 30s @t2 返回锁超时，与异常日志一致。
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction2.2 原因追踪2.2.1 事务状态Mysql 事务操作会涉及到三张表

//当前正在执行的每个事务的信息information_schema.innodb_trx//当前事务持有的锁记录information_schema.innodb_locks// 当前被阻塞的事务锁记录information_schema.innodb_lock_waits

查询 innodb_trx 表

文章插图

主要字段的含义

文章插图

当前有两个未提交的事务，trx_id=21245712 状态为 LOCK WAIT，这条事务产生了一个 id为 21245712:565:3:2 (innodb_locks 表的id) 的锁，也就是该事务的 LOCK因为被阻塞而导致事务超时。
trx_id = 21245684 是执行完 SQL 还未提交的事务。
2.2.2 MySql 锁

innodb_locks InnoDB 锁记录

文章插图

主要字段含义

文章插图

锁在 MySql 事务里是非常主要的，上面的事务就是通过 Primary (主键) 在 Record (行) 上加的X (写) 锁，先加的 X 锁会成功，后加的 X 锁就会被阻塞。下面详细了解一下几个主要的锁。
基本锁
InnoDB 行级锁，分为共享锁（S）和独占锁（X）

共享锁（Sharaed Locks: S锁），或叫读锁
mysql允许拿到S锁的事务读一行
加了S锁记录，允许其他事务再加S锁，不允许其他事务再加X锁
语法：select ... lock in share mode;
独占锁（Exclusive Locks:X锁）或叫写锁
mysql允许拿到X锁的事务更新或删除一行
加了X锁的记录，不允许其他事务再加X锁或S锁
语法：select … for update;

所以出现上述事务死锁超时的原因是 UPDATE 会在记录上加 X 锁，阻塞了另一个事务对同一数据加的 X 锁。
延伸一下，有 X 锁之后，我们还能正常的读数据吗？答案是可以的。
select * from test1;普通的 SELECT 语句上没有加锁，只有 select ... lock in share mode; 才会加 S 锁。