如何在mysql中分析锁等待日志-mysql教程-PHP中文网

如何在mysql中分析锁等待日志

P粉602998670

发布： 2025-10-16 19:28:02

原创

320人浏览过

答案是分析MySQL锁等待需开启InnoDB监控，通过错误日志和系统表定位阻塞源。首先启用innodb_print_all_deadlocks及Performance Schema的锁监控，再查询information_schema.INNODB_TRX和data_lock_waits表获取当前事务与锁等待关系，结合二者可确定被阻塞SQL及持有锁的事务；同时检查错误日志中的死锁记录，分析冲突SQL、索引使用与隔离级别；最后通过优化长事务、索引访问和隔离级别降低锁争用。

如何在mysql中分析锁等待日志

分析 MySQL 中的锁等待日志，核心是理解 InnoDB 的锁机制，并结合系统表和错误日志来定位阻塞源头。MySQL 本身不会默认记录详细的锁等待日志，但可以通过开启相关配置并查询性能模式（Performance Schema）或 information_schema 中的表来获取锁信息。

启用锁监控功能

要分析锁等待，首先要确保 MySQL 实例启用了必要的监控选项：

开启 innodb_print_all_deadlocks：将死锁信息写入错误日志，便于后续分析。在配置文件中添加：
innodb_print_all_deadlocks = ON

启用 Performance Schema 中的锁相关消费者：
可通过以下语句检查并开启：

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%wait%';

登录后复制

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'wait/synch/innodb/innodb_mutex';

登录后复制

查看当前锁等待状态

通过查询 information_schema.INNODB_TRX 和 performance_schema.data_lock_waits 表，可以实时查看正在发生的锁等待：

查询当前运行的事务及其阻塞情况：

SELECT 
  trx_id, trx_mysql_thread_id, trx_query, trx_state, 
  trx_wait_started, trx_requested_lock_id 
FROM information_schema.INNODB_TRX 
ORDER BY trx_wait_started;

登录后复制

查看具体的锁等待关系：

SELECT 
  requesting_trx_id, requested_lock_id, blocking_trx_id, blocking_lock_id
FROM performance_schema.data_lock_waits;

登录后复制

结合事务表与锁表，定位哪个 SQL 被阻塞以及谁在持有锁：

SELECT 
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM information_schema.innodb_trx b
JOIN performance_schema.data_lock_waits w ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

登录后复制

分析错误日志中的死锁信息

当发生死锁且 innodb_print_all_deadlocks 开启时，MySQL 会将完整死锁详情写入错误日志。典型内容包括：

如知AI笔记

如知笔记——支持markdown的在线笔记，支持ai智能写作、AI搜索，支持DeepseekR1满血大模型

查看详情

两个或多个事务的 ID、执行的 SQL 语句
每个事务已持有的锁和试图申请的锁
InnoDB 自动回滚的事务及其原因

从日志中可判断出：哪条 SQL 触发了锁冲突、涉及的索引或记录、事务隔离级别是否合理。例如，频繁出现对主键或二级索引的间隙锁等待，可能说明应用存在长事务或未使用索引的查询。

优化与建议

根据锁等待分析结果，可采取以下措施减少锁争用：

缩短事务长度，避免在事务中执行耗时操作（如网络调用）
确保 DML 操作走索引，避免全表扫描导致大量行锁
合理设置隔离级别，如将非必要场景从可重复读（REPEATABLE READ）降为读已提交（READ COMMITTED）

监控长时间运行的事务：

SELECT * FROM information_schema.INNODB_TRX WHERE TIME_TO_SEC(timediff(now(), trx_started)) > 60;

登录后复制

基本上就这些。只要打开了对应开关，结合系统表和日志，就能清晰看到谁在等锁、被谁阻塞、为什么发生。关键是及时发现异常事务并优化 SQL 和事务设计。

以上就是如何在mysql中分析锁等待日志的详细内容，更多请关注php中文网其它相关文章！

大家都在看：

mysql中如何排查客户端连接异常如何在mysql中配置服务自动启动如何在mysql中使用事务处理日志数据如何在mysql中定期审计用户权限如何在mysql中优化安装参数