Lock wait timeout exceeded； try restarting transaction的解决方案

一、 背景介绍

前段时间遇到一个锁超时的问题，错误日志如下：

出现这个问题的背景是：在全表同步的时候，为了提高效率，关闭了自动提交。

statementDelete = conn.prepareStatement(deleteSql.toString());
conn.setAutoCommit(false);

在业务逻辑处理完毕之后，没有手动提交，导致事物（在数据库代码中就是thread）一直处于running的状态。

在下一个任务要用到这个表时，发现之前的事物没哟提交，就会一直等待，然后报超时错误。

二、 解决方式

1. 由于这一块代码并不是我写的，即使是我写的，那么多行，可能也不能一下子就排查出来，所以就直接采用最舒适的方式，让数据库自己记录日志。

（1）参考方式：通过修改配置文件，收集日志。

第32节：通过performance_schema获取造成死锁的事务语句 - 简书（本文由田兴椿编写） 数据库日常维护中我们经常遇到死锁的问题，由于无法获取造成死锁的事务内执行过的语句，对我们死锁的分析造成很大的困难。但是在MySQL 5.7中我们可以利用...https://www.jianshu.com/p/268889c997e8

在my.cnf中设置打开、关闭performance_schema选项随数据库启动

#设置setup_instruments表收集相关statement event
performance-schema-instrument='statement/%=ON'  
#开启events_statements_current表存储当前连接线程执行的最后一条statement event信息               
performance-schema-consumer-events-statements-current=ON   
#开启events-statements-history表默认存储每个线程最近10条statement event信息 
performance-schema-consumer-events-statements-history=ON        
#开启events-statements-history-long表默认存储最近10000条语句event信息
performance-schema-consumer-events-statements-history-long=ON   

performance-schema-consumer-statements-digest=ON       
         
#设置setup_instruments表收集transaction event
performance-schema-instrument='transaction=ON'    
#开启events_transactions_current表存储当前连接线程执行的transaction event信息              
performance-schema-consumer-events-transactions-current=ON      
#开启events_transactions_history表默认存储每个线程最近10条transaction event信息
performance-schema-consumer-events-transactions-history=ON   
#开启events_statements_history_long表默认存储最近10000条语句event信息。   
performance-schema-consumer-events-transactions-history-long=ON 

（2）通过命令的方式：

#设置是否开启日志
-- set global general_log=on;
#设置日志记录方式 table、file
-- set global log_output='table'

 2. 查询被锁的thread_id

具体可参考：https://blog.csdn.net/sunhuansheng/article/details/121907365

mysql 8之前的版本：information_schema

innodb_trx 当前运行的所有事务
innodb_locks 当前出现的锁
innodb_lock_waits 锁等待的对应关系
select * from information_schema.innodb_trx;
select * from information_schema.innodb_locks;
select * from information_schema.innodb_locks_waits;

mysql 8.0之后的版本：performance_schema

innodb_trx 当前运行的所有事务
innodb_locks 当前出现的锁
innodb_lock_waits 锁等待的对应关系
select * from information_schema.innodb_trx;
select * from performance_schema.data_locks;
select * from performance_schema.data_lock_waits;
 

通过

 select * from information_schema.innodb_trx; 

可知道，当前正在运行的thread_id是31:；

 因为该thread_id是事物未提交，处于running的状态，所以，

select * from information_schema.INNODB_LOCKS;
select * from information_schema.INNODB_LOCK_WAITS;

两个sql查无数据，没有明确的地方说是锁哪里的。

在命令界面，直接kill掉31；

 3. 重现问题

重现该问题，分析日志，查询到问题线程执行的sql语句。反过来去找代码中调用的地方。

SELECT *,CONVERT (argument USING utf8) as `sql` from mysql.general_log  where thread_id = 45 ORDER BY event_time DESC;

 结束。