Mysql 使用 InnoDB 事务和锁定信息

本节介绍了由 Performance Schema 的 data_locks 和 data_lock_waits 表所展示的锁定信息的使用。

识别阻塞事务

有时，确定哪个事务阻塞了另一个事务是很有帮助的。包含有关 InnoDB 事务和数据锁信息的表使您能够确定哪个事务在等待另一个事务，以及正在请求哪个资源。

在这种情况下，使用以下查询来查看哪些事务在等待以及哪些事务正在阻塞它们：

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 performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
ON r.trx_id = w.requesting_engine_transaction_id;

或者，更简单地，使用 sys 模式的 innodb_lock_waits 视图：

SELECT
waiting_trx_id,
waiting_pid,
waiting_query,
blocking_trx_id,
blocking_pid,
blocking_query
FROM sys.innodb_lock_waits;

在发出会话变为空闲后识别阻塞查询

在识别阻塞事务时，如果发出查询的会话已变为空闲，则阻塞查询将报告为 NULL 值。在这种情况下，请使用以下步骤确定阻塞查询：

1. 确定阻塞事务的进程列表 ID。在 sys.innodb_lock_waits 表中，阻塞事务的进程列表 ID 是 blocking_pid 值。

2. 使用 blocking_pid，查询 MySQL Performance Schema 的 threads 表以确定阻塞事务的 THREAD_ID。例如，如果 blocking_pid 为 6，则发出此查询：

SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 6;

3. 使用 THREAD_ID，查询 Performance Schema 的 events_statements_current 表以确定线程执行的最后一个查询。例如，如果 THREAD_ID 为 28，则发出此查询：

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current
WHERE THREAD_ID = 28\G

4. 如果线程执行的最后一个查询不足以确定为何持有锁，您可以查询 Performance Schema 的 events_statements_history 表以查看线程执行的最后 10 个语句。

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_history
WHERE THREAD_ID = 28 ORDER BY EVENT_ID;

将 InnoDB 事务与 MySQL 会话相关联

有时，将内部 InnoDB 锁定信息与 MySQL 维护的会话级别信息相关联是很有用的。例如，对于给定的 InnoDB 事务 ID，您可能想知道相应的 MySQL 会话 ID 以及可能持有锁从而阻塞其他事务的会话的名称。