Mysql 事物阻塞

原创已于 2024-10-19 23:54:57 修改 · 425 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #事物 #事物阻塞 #死锁

于 2024-04-10 16:18:04 首次发布

本文介绍了如何通过SQL查询监控MySQL中事务的锁状态，生成kill命令，以及检查和调整wait_timeout和innodb_lock_wait_timeout等超时参数，以避免因等待锁导致的问题，同时给出性能调优的建议。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、查看现有事物（锁），批量生成 kill 命令

select CONCAT('kill ', trx_mysql_thread_id, ';') as kill_command, a.*
from information_schema.innodb_trx as a 
-- where trx_query is null

2、查看指定数据库的线程，及其对应的事物（锁），批量生成 kill 命令

select CONCAT('kill ', trx_mysql_thread_id, ';') as kill_command, a.*, b.*
from information_schema.processlist as a 
left join information_schema.innodb_trx as b
-- join information_schema.innodb_trx as b
on a.id = b.trx_mysql_thread_id 

where a.db = 'my_db'
-- and b.trx_query is null

3、查看事物超时时间

SHOW VARIABLES LIKE '%wait_timeout%';

4、查看锁的超时时间

SHOW VARIABLES LIKE '%innodb_lock_wait_timeout%';

lock_wait_timeout 是一个服务器级别的参数，它用于设置所有连接在等待行级锁或表级锁时可以等待的最大时间。当连接等待的时间超过了 lock_wait_timeout 的值时，MySQL 将抛出一个错误，并终止连接。默认值为 31536000 秒（即一年）。

innodb_lock_wait_timeout 是一个存储引擎级别的参数，它仅用于设置使用 InnoDB 存储引擎的连接在等待行级锁或表级锁时可以等待的最大时间。当连接等待的时间超过了 innodb_lock_wait_timeout 的值时，InnoDB 将抛出一个错误，并终止连接。默认值为 50 秒。

建议将 innodb_lock_wait_timeout 的值设置得比较小，因为这可以有效避免因等待锁而导致的超时问题。但是，也要注意不要设置得过小，以免在高并发的情况下出现频繁的超时错误。