MySQL锁等待查看与死锁分析工具

MySQL中查看锁等待的核心方法包括使用SHOW ENGINE INNODB STATUS、性能模式表和专用工具。首先，执行SHOW ENGINE INNODB STATUS可查看死锁和事务锁信息；其次，查询events_waits_current等性能模式表定位当前或历史锁等待事件；最后，借助pt-deadlock-logger等工具进行实时监控。优化SQL、控制事务大小、降低隔离级别及合理设计数据库可有效减少锁等待问题。

MySQL中查看锁等待，核心在于监控和诊断数据库中出现的阻塞现象。这可以通过多种方式实现，包括查看状态变量、使用性能模式表，以及借助一些专门的锁等待分析工具。

解决方案

要有效地诊断MySQL锁等待问题，需要组合使用多种方法。首先，快速查看当前状态，然后深入分析历史数据，最后，如果需要，使用专用工具进行实时监控。

如何使用SHOW ENGINE INNODB STATUS诊断锁等待？

SHOW ENGINE INNODB STATUS是诊断锁等待问题的首选命令。它提供了关于InnoDB存储引擎的详细信息，包括事务、锁、日志等。

输出结果中的LATEST DETECTED DEADLOCK部分是关键。如果存在死锁，这里会显示详细的死锁信息，包括涉及的事务ID、SQL语句、锁类型等。即使没有死锁，也可以查看TRANSACTIONS部分，了解当前活跃的事务以及它们持有的锁。

例如，你可以看到类似这样的输出：

*** (1) TRANSACTION:
TRANSACTION 1234567, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 123, OS thread handle 0x7f9b1c0a0700, query id 9876543 localhost user updating
UPDATE table1 SET column1 = 'new value' WHERE id = 1;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 456 n bits 72 index PRIMARY of table `database1`.`table1` trx id 1234567 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 7654321, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 456, OS thread handle 0x7f9b1c0b0800, query id 3456789 localhost user updating
UPDATE table1 SET column2 = 'another new value' WHERE id = 1;
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 456 n bits 72 index PRIMARY of table `database1`.`table1` trx id 7654321 lock_mode X locks rec but not gap
*** (2) WAITING FOR:
...

这段输出表明两个事务正在争夺table1上id = 1的记录锁。事务1正在等待事务2释放锁。通过分析这些信息，可以确定导致锁等待的具体SQL语句和事务。

如何利用性能模式表排查锁等待？

性能模式（Performance Schema）是MySQL 5.6及更高版本提供的一个强大的性能监控工具。它收集了关于服务器执行的各种操作的详细信息，包括锁等待。

以下是一些常用的性能模式表：

• events_waits_current: 显示当前正在等待的事件。
• events_waits_history: 显示最近完成的等待事件的历史记录。
• events_waits_history_long: 显示更长时间的等待事件历史记录。
• threads: 提供关于线程的信息，可以用来关联等待事件和具体的线程。

例如，可以使用以下查询来查找当前正在等待锁的线程：

SELECT
    event_name,
    OBJECT_NAME,
    THREAD_ID,
    SOURCE,
    TIMER_WAIT
FROM performance_schema.events_waits_current
WHERE event_name LIKE 'wait/lock/%'
ORDER BY TIMER_WAIT DESC;

这个查询会显示等待锁的事件名称、对象名称（例如表名）、线程ID、源代码位置和等待时间。通过分析这些信息，可以确定哪些线程正在等待锁，以及它们等待的时间。

此外，还可以使用events_waits_history和events_waits_history_long表来分析历史锁等待事件，找出导致锁等待的常见模式。

有哪些实用的锁等待分析工具？

除了SHOW ENGINE INNODB STATUS和性能模式表，还有一些专门的锁等待分析工具可以帮助诊断锁等待问题。

• pt-deadlock-logger: Percona Toolkit中的pt-deadlock-logger工具可以定期检查并记录死锁信息。它可以帮助你捕获偶发的死锁事件，而这些事件可能在你手动检查时已经消失。
• MySQL Enterprise Monitor: 如果你使用的是MySQL Enterprise Edition，可以使用MySQL Enterprise Monitor来监控锁等待。它提供了图形化的界面，可以方便地查看锁等待信息，并设置警报。
• 自定义脚本: 可以使用Python或其他脚本语言编写自定义脚本来定期查询性能模式表，并将结果记录到日志文件中。这可以帮助你长期监控锁等待情况，并发现潜在的问题。

选择哪种工具取决于你的具体需求和环境。对于简单的锁等待问题，SHOW ENGINE INNODB STATUS和性能模式表可能就足够了。对于更复杂的问题，或者需要长期监控的情况，使用专门的锁等待分析工具可能更有效。

如何避免锁等待？

锁等待的根本原因是资源竞争。要避免锁等待，需要减少资源竞争。

• 优化SQL语句: 确保SQL语句尽可能高效。避免全表扫描，使用索引，减少锁的持有时间。
• 控制事务大小: 避免长时间运行的事务。将大事务分解成小事务，减少锁的持有时间。
• 使用较低的隔离级别: 在允许的情况下，使用较低的隔离级别，例如READ COMMITTED。较低的隔离级别可以减少锁的冲突。
• 避免死锁: 确保事务以相同的顺序访问资源。使用SELECT ... FOR UPDATE语句显式地锁定资源。
• 合理设计数据库: 优化表结构，减少热点行。使用分区表，将数据分散到不同的物理存储上。

总之，诊断MySQL锁等待问题需要综合运用多种方法。通过查看状态变量、使用性能模式表、借助锁等待分析工具，以及优化SQL语句和数据库设计，可以有效地解决锁等待问题，提高数据库性能。