MySQL异常Lock wait timeout exceeded解决办法

问题现象

最近在做数据删除的时候，MySQL抛出MySQLTransactionRollbackException异常：

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

解决过程

查询资料，得知可以到information_schema中查找被锁的语句。

information_schema 这个库里面保存了MySQL服务器所有数据库的信息，如数据库名，数据库的表，表栏的数据类型与访问权限等。

有三张需要用到的表：

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

在数据库执行查询语句查看当前事务：

select * from information_schema.INNODB_TRX;

其中，trx_state=LOCK WAIT即为当前出现等待的锁，trx_mysql_thread_id为当前事务线程ID.
如果查询出锁，可以通过kill ID杀掉锁等待的线程。
另外，通过show full processlist;也能查询出出现锁等待的线程。
可惜并未查询出当前有锁等待的线程，于是查询innodb_locks表：

select * from information_schema.innodb_locks;

报错Access denied;没有权限，转而查询show engine INNODB status;
找到关键信息如下:

=====================================
2021-08-01 10:22:52 0x7f7efb118700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 31 seconds
------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-08-01 10:01:33 0x7f5165361700
*** (1) TRANSACTION:
TRANSACTION 1484090860, ACTIVE 38013 sec starting index read
mysql tables in use 2, locked 2
LOCK WAIT 7 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 883478809, OS thread handle 140181904164608, query id 320599388 172.20.49.253 yx_rw Sending data
delete T0 from info_detail as T0 left join info as T1 on T0.infoId=T1.id where T1.uid=2375929778394112 and T0.user_id=2292495487783168
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 864682 page no 4 n bits 72 index PRIMARY of table info_detail trx id 1484090860 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 11; compact format; info bits 0

发现此问题可能为删除子表时出现联表删除导致，在子表字段上加普通索引暂时解决。

区分Lock wait timeout exceeded与Dead Lock

Lock wait timeout exceeded与Dead Lock是不一样的：

• Lock wait timeout exceeded后面提交的事务等待前面处理的事务释放锁，但是等待的时间超过了mysql的锁等待时间；
• Dead Lock两个事务互相等待对方释放相同资源的锁，形成死锁。

如何查看mysql设置的锁等待时间是多少呢？

show variables like 'innodb_lock_wait_timeout';

造成死锁的情况一般有哪些呢？

1. 执行DML操作没有commit，再执行删除操作；
2. 同一事务内先后对同一条数据进行插入和更新操作；
3. 表索引设计不当；
4. 事务处理过程太长；
5. …

参考资料：segmentfault.com