MySQL案例：使用sys.innodb_lock_waits视图诊断锁等待问题

视图结构

SELECT * FROM sys.innodb_lock_waits;

sys.innodb_lock_waits 视图的列及其含义如下：

• wait_started: 锁等待开始的时间。
• wait_age: 锁等待的持续时间（以秒为单位）。
• object_schema: 涉及的数据库名。
• object_name: 涉及的表名。
• index_name: 涉及的索引名。
• lock_type: 锁类型（如 RECORD、TABLE 等）。
• lock_mode: 锁模式（如 S、X、IS、IX 等）。
• lock_status: 锁状态（如 GRANTED、WAITING）。
• waiting_trx_id: 请求锁的事务 ID。
• waiting_query: 请求锁的 SQL 查询。
• waiting_pid: 请求锁的进程 ID。
• waiting_lock_id: 请求的锁 ID。
• waiting_lock_type: 请求的锁类型。
• waiting_lock_mode: 请求的锁模式。
• blocking_trx_id: 持有锁的事务 ID。
• blocking_query: 持有锁的 SQL 查询。
• blocking_pid: 持有锁的进程 ID。
• blocking_lock_id: 被请求的锁 ID。
• blocking_lock_type: 持有的锁类型。
• blocking_lock_mode: 持有的锁模式。
• sql_kill_blocking_query: 可以用来终止持有锁的查询的 SQL 命令。
• sql_kill_blocking_connection: 可以用来终止持有锁的连接的 SQL 命令。

示例输出

执行 SELECT * FROM sys.innodb_lock_waits; 返回以下结果：

wait_started	wait_age	object_schema	object_name	index_name	lock_type	lock_mode	lock_status	waiting_trx_id	waiting_query	waiting_pid	waiting_lock_id	waiting_lock_type	waiting_lock_mode	blocking_trx_id	blocking_query	blocking_pid	blocking_lock_id	blocking_lock_type	blocking_lock_mode	sql_kill_blocking_query	sql_kill_blocking_connection
2023-10-01 10:00:00	10	test_db	users	PRIMARY	RECORD	X	WAITING	123456	UPDATE users SET age = 30 WHERE id = 1	1001	123456-1	RECORD	X	654321	SELECT * FROM users WHERE id = 1	1002	654321-1	RECORD	X	KILL QUERY 1002	KILL CONNECTION 1002

解释

• wait_started: 锁等待开始的时间是 2023-10-01 10:00:00。
• wait_age: 锁等待已经持续了 10 秒。
• object_schema: 涉及的数据库名是 test_db。
• object_name: 涉及的表名是 users。
• index_name: 涉及的索引名是 PRIMARY。
• lock_type: 锁类型是 RECORD。
• lock_mode: 锁模式是 X（独占锁）。
• lock_status: 锁状态是 WAITING。
• waiting_trx_id: 请求锁的事务 ID 是 123456。
• waiting_query: 请求锁的 SQL 查询是 UPDATE users SET age = 30 WHERE id = 1。
• waiting_pid: 请求锁的进程 ID 是 1001。
• waiting_lock_id: 请求的锁 ID 是 123456-1。
• waiting_lock_type: 请求的锁类型是 RECORD。
• waiting_lock_mode: 请求的锁模式是 X（独占锁）。
• blocking_trx_id: 持有锁的事务 ID 是 654321。
• blocking_query: 持有锁的 SQL 查询是 SELECT * FROM users WHERE id = 1。
• blocking_pid: 持有锁的进程 ID 是 1002。
• blocking_lock_id: 被请求的锁 ID 是 654321-1。
• blocking_lock_type: 持有的锁类型是 RECORD。
• blocking_lock_mode: 持有的锁模式是 X（独占锁）。
• sql_kill_blocking_query: 可以用来终止持有锁的查询的 SQL 命令是 KILL QUERY 1002。
• sql_kill_blocking_connection: 可以用来终止持有锁的连接的 SQL 命令是 KILL CONNECTION 1002。

使用场景

• 诊断锁等待问题：通过查看 sys.innodb_lock_waits 视图，可以快速识别出哪些事务正在等待锁，以及哪些事务持有锁。
• 优化查询性能：了解锁等待的具体情况，可以帮助你优化查询，减少锁冲突，提高数据库性能。
• 终止长时间等待的查询：使用 sql_kill_blocking_query 和 sql_kill_blocking_connection 列提供的命令，可以终止导致锁等待的查询或连接。