面试题：MySQL中如何监控锁竞争？

Question

请阐述在MySQL数据库环境下，常用的监控锁竞争的方式与工具，以及通过这些方式和工具能获取到哪些关于锁竞争的关键信息。

Answer 1

常用监控锁竞争的方式与工具

1. SHOW STATUS语句：通过执行SHOW STATUS LIKE 'innodb_row_lock%'; ，可查看InnoDB引擎行锁相关状态信息。
2. INFORMATION_SCHEMA表：例如INFORMATION_SCHEMA.INNODB_TRX表，用于查看当前执行的事务；INFORMATION_SCHEMA.INNODB_LOCKS表，可查看当前持有或等待的锁；INFORMATION_SCHEMA.INNODB_LOCK_WAITS表，查看锁等待的相关信息。
3. Percona Toolkit：其中pt - query - digest工具可以分析查询日志，帮助发现可能导致锁竞争的慢查询。
4. MySQL Enterprise Monitor：这是MySQL官方的监控工具，能图形化展示锁竞争等各种数据库状态信息。

可获取的关键信息

1. 行锁状态信息：通过SHOW STATUS LIKE 'innodb_row_lock%';能获取诸如InnoDB_row_lock_current_waits（当前正在等待锁的数量）、InnoDB_row_lock_time（从系统启动到现在锁定总时长）、InnoDB_row_lock_time_avg（每次等待锁的平均时长）等信息，帮助了解行锁等待情况。
2. 事务相关信息：从INFORMATION_SCHEMA.INNODB_TRX表可获取正在执行的事务ID、事务开始时间、执行该事务的SQL语句等，判断事务执行情况，是否因锁等待而长时间未完成。
3. 锁相关信息：INFORMATION_SCHEMA.INNODB_LOCKS表能展示锁的类型（如共享锁、排他锁）、锁所在的数据库、表、索引信息，以及持有锁的事务ID等，了解锁的具体情况。INFORMATION_SCHEMA.INNODB_LOCK_WAITS表可获取等待锁的事务ID、等待的锁信息以及持有该锁的事务ID，明确锁等待关系。
4. 慢查询信息：pt - query - digest分析查询日志，能找出执行时间长可能引发锁竞争的SQL语句，包括查询语句内容、执行次数、平均执行时间等，帮助优化查询以减少锁竞争。
5. 图形化信息：MySQL Enterprise Monitor以图形化方式展示锁竞争趋势，如锁等待次数随时间变化曲线等，直观呈现数据库锁竞争整体态势。