MySQL 锁表处理

show processlist;

kill pid

show OPEN TABLES WHERE in_use>0;

 

异常描述：
Caused by: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

问题原因
在高并发的情况下，Spring事物造成数据库死锁，后续操作超时抛出异常
Mysql数据库采用InnoDB模式，默认参数：innodb_lock_wait_timeout设置锁等待的时间是50s，一旦数据库锁超过这个时间就会报错
解决办法
杀掉 Thread Id（临时）

到information_schema中查找被锁的语句

-- 查找出此问题的 trx_mysql_thread_id，然后进行KILL掉
select * from information_schema.innodb_trx;
select * from information_schema.innodb_locks;
select * from information_schema.innodb_lock_waits;

-- 模拟重现问题时，trx_mysql_thread_id = 15330615
kill 15330615;

trx_state=LOCK WAIT 即为当前出现等待的锁，
trx_mysql_thread_id 为当前事务线程ID.
kill ID 杀掉锁等待的线程。
show full processlist; 也能查询出出现锁等待的线程。

 

异常定位
可能出现锁表的原因：
1、在同一事务内先后对同一条数据进行插入和更新操作
2、分布式服务操作同一条记录
3、瞬时出现高并发现象，spring事务造成数据库死锁，后续操作超时抛出异常
4、事务A对记录C进行更新/删除操作的请求未commit时，事务B也对记录C进行更新/删除操作。此时，B会等A提交事务，释放行锁。

     当等待时间超过innodb_lock_wait_timeout设置值时，会产生“LOCK WAIT”事务。
5、数据库内存不足，导致无法执行写操作。

 

根治办法：找到锁表的事务，分析锁表原因，进行优化

1.查看当前锁超时时间
show variables like 'innodb_lock_wait_timeout';

2.查看全局锁超时时间
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';

3.更改当前锁超时时间为120秒
SET innodb_lock_wait_timeout=120;

4.更改全局锁超时时间为120秒
SET GLOBAL innodb_lock_wait_timeout=120;