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

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

P粉602998670

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

原创

354人浏览过

答案是分析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 会将完整死锁详情写入错误日志。典型内容包括：

Find JSON Path Online

Easily find JSON paths within JSON objects using our intuitive Json Path Finder

193

查看详情

两个或多个事务的 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通配符在like中怎么用_mysql通配符配合like语句的详细用法 mysql归档数据怎么压缩_mysql归档数据压缩处理的方法与工具 mysql镜像配置怎么启动_mysql镜像配置启动方法与常见问题 mysql去重怎么用临时表_mysql使用临时表去重方法介绍