记一次MySQL的Lock Wait Timeout exceeded异常

前言

一次由于间隙锁导致的 Lock wait timeout，记录一下解决过程。

异常描述

前端反映端口返回 504 Gateway Timeout，到服务器上查看对应的服务实例日志发现了以下异常：

### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction] with root cause

com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
...
...

问题排查

根据 log 定位异常的原因在于一个加了 @Transactional 注解的方法中先 Select 了一条不存在的数据，产生了间隙锁，然后调用了另一个同样加了 @Transactional 注解的方法在该表中 Insert 了一条数据，从而导致锁等待超时。

# a_id与b_id都加了普通索引，实际该条记录已被逻辑删
# Session A:
select * from test_table where a_id = 23 and b_id = 63 and del_flag = 0 for UPDATE;

# 此时b_id=66处于间隙锁范围内
# Session B:
insert into test_table(a_id,b_id,del_flag) value(23,66,0);

MySQL 默认参数 innodb_lock_wait_timeout 锁等待时间为 50s，一旦锁超过这个时间就会报 Lock wait timeout。

解决方案

Select 前进行判空，如果不存在该条数据则不进行后续的业务逻辑。

另外，对于加了 @Transactional 注解的方法，最好加上超时时间，或自动提交，同时也不要把执行时间较长的方法放到事务中。

总结

通过 show processlist 可以查看正在进行的查询和花费时间。

通过 information_schema 中的以下三张表可以快速查找导致死锁的SQL语句：

innodb_trx：当前运行的所有事务。
innodb_locks：当前出现的锁。
innodb_lock_waits：锁等待的对应关系。

关于间隙锁

间隙锁的目的是为了防止幻读，主要通过以下两个方法实现：
（1）防止间隙内有新数据被插入
（2）防止已存在的数据，更新成间隙内的数据

Innodb 自动使用间隙锁的条件：
（1）必须在RR级别下
（2）检索条件必须有索引，PS：若Where条件中的是主键索引或唯一索引，此时为行锁不会产生间隙锁。