错误信息：SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction
接口响应时间超长，报警日志中出现Lock wait timeout exceeded; try restarting transaction的错误
原因
mysql数据库采用InnoDB模式，一旦数据库锁超过innodb_lock_wait_timeout参数设置的锁等待的时间(默认50s)就会报错。
一个事务在等待另一个事务释放锁，另一个事务持有了一个资源的资源，导致另这个事务一直等待并超时。并且通过
trx_started可发现，持有锁的事务是之前开启的，一直没释放。解决：
- 查看事务：
select * from information_schema.INNODB_TRX;kill掉 线程 后正常 kill 47491; (trx_mysql_thread_id)
-- 查看事务 当前运行的所有事务 select * from information_schema.INNODB_TRX; --查看锁 当前出现的锁 select * from information_schema.INNODB_LOCKS; --查看锁等待 锁等待的对应关系 select * from information_schema.INNODB_LOCK_WAITS; # 当前线程详情 show full processlist;
查询全局等待事务锁超时时间
# 查询全局等待事务锁超时时间 SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout' SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_lock_wait_timeout | 50 | +--------------------------+-------+
修改事务锁超时时间
set innodb_lock_wait_timeout=100; set global innodb_lock_wait_timeout=100;
或者修改配置文件
# my.ini文件： innodb_lock_wait_timeout = 100
其他方法:
-- 排查事务的SQL SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX WHERE trx_query LIKE '%表名%'; KILL 掉执行的线程
## 查看当前被使用的表 show OPEN TABLES where In_use > 0; -- SHOW OPEN TABLES：列举在表缓存中当前被打开的非TEMPORARY表。 -- In_use：表当前被查询使用的次数。如果该数为零，则表是打开的，但是当前没有被使用 SELECT trx_id AS `事务ID`, trx_state AS `事务状态`, trx_requested_lock_id AS `事务需要等待的资源`, trx_wait_started AS `事务开始等待时间`, trx_tables_in_use AS `事务使用表`, trx_tables_locked AS `事务拥有锁`, trx_rows_locked AS `事务锁定行`, trx_rows_modified AS `事务更改行` FROM information_schema.innodb_trx ; SELECT lock_id AS `锁ID`, lock_trx_id AS `拥有锁的事务ID`, lock_mode AS `锁模式 `, lock_type AS `锁类型`, lock_table AS `被锁的表`, lock_index AS `被锁的索引`, lock_space AS `被锁的表空间号`, lock_page AS `被锁的页号`, lock_rec AS `被锁的记录号`, lock_data AS `被锁的数据` FROM information_schema.innodb_locks; SELECT requesting_trx_id AS `请求锁的事务ID`, requested_lock_id AS `请求锁的锁ID`, blocking_trx_id AS `当前拥有锁的事务ID`, blocking_lock_id AS `当前拥有锁的锁ID` FROM innodb_lock_waits
