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语句和数据库设计，可以有效地解决锁等待问题，提高数据库性能。
