列名无效怎么解决_线上 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 transaction

2.2 原因追踪

2.2.1  事务状态

Mysql 事务操作会涉及到三张表

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

查询 innodb_trx 表 主要字段的含义

列名	描述
trx_id	事务Id
trx_state	事务状态RUNNING 执行中LOCKWAIT 因等待锁而阻塞ROLLING BACK 回滚中COMMITTING 提交中
trx_requested_lock_id	当前正在等待的锁的ID
trx_wait_started	开始阻塞的时间
trx_query	正在执行的SQL语句
trx_tables_locked	当前SQL语句锁定表的数量
trx_rows_locked	锁定的大致行数
trx_isolation_level	当前事务的隔离级别。

当前有两个未提交的事务，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 锁记录

主要字段含义

列名	描述
lock_id	锁id
lock_trx_id	持有锁的事务的ID
lock_mode	锁模式。S、X、IS、IX、GAP、AUTO_INC锁
lock_type	锁的类型
lock_table	锁定的表名
lock_index	索引的名称
lock_data	锁定的数据

锁在 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 锁。 下面是 MySql 的其他锁 意向锁 InnoDB为了支持多粒度(表锁和行锁)的锁并存，引入意向锁。意向锁是表级锁，分为IS锁和IX锁。

• 意向共享锁(IS)事务在请求S锁前，需要先获得对应的IS锁
• 意向排他锁 (IX)事务在请求X锁前，需要先获得对应的IX锁

锁兼容矩阵 自增锁 auto-inc lock AUTO-INC锁是事务中的一种特殊的表级锁，通过AUTO_INCREMENT的列来实现，这种锁是作用于语句的而不是事务。 记录锁 record Lock 即行锁。单条索引记录上加锁，record lock锁住的永远是索引，而非记录本身。 间隙锁 gap lock 区间锁, 仅仅锁住一个索引区间(开区间)。在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。GAP锁的目的是为了防止同一事务的两次当前读，出现幻读的情况。 临键锁 next key lock 行锁和间隙锁组合起来就叫Next-Key-Lock，左开右闭区间。默认情况下，innodb使用next-key locks来锁定记录。但当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。 插入意向锁 insert intention lock Gap Lock中存在一种插入意向锁(Insert Intention Lock)，在insert操作时产生。在多事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。      假设有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。 注：插入意向锁并非意向锁，而是一种特殊的间隙锁。 如果插入前，该间隙已经有gap锁，那么insert 会申请插入意向锁。因为了避免幻读，当其他事务持有该间隙的间隔锁，插入意向锁就会被阻塞(不用直接用gap锁，是因为gap锁不互斥)。

• innodb_lock_waits 被阻塞的锁记录

这张表里有记录就说明有事务被阻塞了。

列名	描述
requesting_trx_id	被阻塞(正在等待)的事务ID。
requested_lock_id	被阻塞的锁ID
blocking_trx_id	阻止事务的ID。
blocking_lock_id	阻塞别人的锁的ID

03. 解决方案及总结

线上遇到死锁怎么解决？最快的方式当然是 kill 事务，重启服务，根本原因还是需要看这三张表， 以后再遇到数据库死锁、事务死锁，查这三张表就差不多知道原因了。 我们该如何避免死锁呢？常规的回答都是以固定的顺序访问数据。但本案例是因为使用了  REQUIRES_NEW 导致。 使用 REQUIRES_NEW 的原因以下场景，内层事务是一个批量更新，但是又不希望因为某一条失败而影响其他的更新。

begin @t1aMapper.update()for pojo in pojos:  begin @t2  bMapper.update(pojo)  rpc.update()  commitcommit

所以一定要避免内外双层事务修改同一条数据的情况，对于 Spring 事务传播机制也要熟知其作 用。 要保证数据的最终一致性，应该写成一个Job，更新失败后不断的去补偿。