14.16.2.1 使用 InnoDB 事务和锁定信息
识别阻止的 Transaction
识别哪个 Transaction 会阻止另一个 Transaction 有时会很有帮助。包含有关InnoDB
事务和数据锁的信息的 table 使您能够确定哪个事务正在 await 另一个事务以及正在请求哪个资源。 (有关这些 table 的说明,请参见第 14.16.2 节“ InnoDB INFORMATION_SCHEMA 事务和锁定信息”。)
假设三个会话正在同时运行。每个会话都对应一个 MySQL 线程,并依次执行一个事务。当这些会话发出以下语句,但尚未提交任何事务时,请考虑系统状态:
- Session A:
BEGIN;
SELECT a FROM t FOR UPDATE;
SELECT SLEEP(100);
- Session B:
SELECT b FROM t FOR UPDATE;
- Session C:
SELECT c FROM t FOR UPDATE;
在这种情况下,使用以下查询来查看哪些事务正在 await,哪些事务正在阻止它们:
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 information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
ON r.trx_id = w.requesting_trx_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 值,请参见在发布会话变为空闲之后识别阻塞查询。
awaittrx ID | waiting thread | waiting query | 阻止 trx ID | blocking thread | blocking query |
---|---|---|---|---|---|
A4 | 6 | SELECT b FROM t FOR UPDATE | A3 | 5 | SELECT SLEEP(100) |
A5 | 7 | SELECT c FROM t FOR UPDATE | A3 | 5 | SELECT SLEEP(100) |
A5 | 7 | SELECT c FROM t FOR UPDATE | A4 | 6 | SELECT b FROM t FOR UPDATE |
在上 table 中,您可以通过“await 查询”或“阻止查询”列来标识会话。如你看到的:
-
会话 B(trx id
A4
,线程6
)和会话 C(trx idA5
,线程7
)都在 await 会话 A(trx idA3
,线程5
)。 -
会话 C 正在 await 会话 B 和会话 A。
您可以在 tableINNODB_TRX,INNODB_LOCKS和INNODB_LOCK_WAITS中查看基础数据。
下 table 显示了INFORMATION_SCHEMA.INNODB_TRX的一些示例内容。
trx id | trx state | trx started | trx 请求的锁定 ID | trxawait 开始 | trx weight | trx mysql 线程 ID | trx query |
---|---|---|---|---|---|---|---|
A3 | RUNNING | 2008-01-15 16:44:54 | NULL | NULL | 2 | 5 | SELECT SLEEP(100) |
A4 | LOCK WAIT | 2008-01-15 16:45:09 | A4:1:3:2 | 2008-01-15 16:45:09 | 2 | 6 | SELECT b FROM t FOR UPDATE |
A5 | LOCK WAIT | 2008-01-15 16:45:14 | A5:1:3:2 | 2008-01-15 16:45:14 | 2 | 7 | SELECT c FROM t FOR UPDATE |
下 table 显示了INFORMATION_SCHEMA.INNODB_LOCKS的一些示例内容。
lock id | 锁定 trx ID | lock mode | lock type | lock table | lock index | lock data |
---|---|---|---|---|---|---|
A3:1:3:2 | A3 | X | RECORD | test.t | PRIMARY | 0x0200 |
A4:1:3:2 | A4 | X | RECORD | test.t | PRIMARY | 0x0200 |
A5:1:3:2 | A5 | X | RECORD | test.t | PRIMARY | 0x0200 |
下 table 显示了INFORMATION_SCHEMA.INNODB_LOCK_WAITS的一些示例内容。
请求 trx ID | 请求的锁 ID | 阻止 trx ID | 阻止锁 ID |
---|---|---|---|
A4 | A4:1:3:2 | A3 | A3:1:3:2 |
A5 | A5:1:3:2 | A3 | A3:1:3:2 |
A5 | A5:1:3:2 | A4 | A4:1:3:2 |
在发布会话变为空闲之后识别阻塞查询
标识阻止事务时,如果发出查询的会话已变为空闲,则为阻止查询报告 NULL 值。在这种情况下,请使用以下步骤确定阻止查询:
-
标识阻止事务的进程列 tableID。在sys.innodb_lock_waitstable 中,阻止事务的流程列 tableID 为
blocking_pid
值。 -
使用
blocking_pid
,查询 MySQL Performance Schema threadstable,以确定阻塞事务的THREAD_ID
。例如,如果blocking_pid
为 6,则发出以下查询:
SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 6;
- 使用
THREAD_ID
,查询 Performance Schema events_statements_currenttable,以确定该线程执行的最后一个查询。例如,如果THREAD_ID
为 28,则发出以下查询:
SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current
WHERE THREAD_ID = 28\G
- 如果线程执行的最后一个查询不足以确定锁定原因,则可以查询 Performance Schema events_statements_historytable 以查看线程执行的最后 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 和可能持有锁并因此阻止其他事务的会话的名称。
INFORMATION_SCHEMA
table 的以下输出取自某种程度上已加载的系统。可以看出,有几个事务正在运行。
以下INNODB_LOCKS
和INNODB_LOCK_WAITS
table 显示:
-
事务
77F
(正在执行INSERT)正在 await 事务77E
,77D
和77B
提交。 -
事务
77E
(正在执行INSERT)正在 await 事务77D
和77B
提交。 -
事务
77D
(正在执行INSERT)正在 await 事务77B
提交。 -
事务
77B
(正在执行INSERT)正在 await 事务77A
提交。 -
事务
77A
正在运行,当前正在执行SELECT。 -
事务
E56
(正在执行INSERT)正在 await 事务E55
提交。 -
事务
E55
(正在执行INSERT)正在 await 事务19C
提交。 -
事务
19C
正在运行,当前正在执行INSERT。
Note
INFORMATION_SCHEMA
PROCESSLIST和INNODB_TRXtable 中显示的查询之间可能存在不一致。有关说明,请参见第 14.16.2.3 节“ InnoDB 事务和锁定信息的持久性和一致性”。
下 table 显示了运行重workload的系统的INFORMATION_SCHEMA.PROCESSLIST的内容。
ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
---|---|---|---|---|---|---|---|
384 | root | localhost | test | Query | 10 | update | INSERT INTO t2 VALUES … |
257 | root | localhost | test | Query | 3 | update | INSERT INTO t2 VALUES … |
130 | root | localhost | test | Query | 0 | update | INSERT INTO t2 VALUES … |
61 | root | localhost | test | Query | 1 | update | INSERT INTO t2 VALUES … |
8 | root | localhost | test | Query | 1 | update | INSERT INTO t2 VALUES … |
4 | root | localhost | test | Query | 0 | preparing | SELECT * FROM PROCESSLIST |
2 | root | localhost | test | Sleep | 566 | `` | NULL |
下 table 显示了运行重workload的系统的INFORMATION_SCHEMA.INNODB_TRX的内容。
trx id | trx state | trx started | trx 请求的锁定 ID | trxawait 开始 | trx weight | trx mysql 线程 ID | trx query |
---|---|---|---|---|---|---|---|
77F | LOCK WAIT | 2008-01-15 13:10:16 | 77F | 2008-01-15 13:10:16 | 1 | 876 | INSERT INTO t09 (D, B, C) VALUES … |
77E | LOCK WAIT | 2008-01-15 13:10:16 | 77E | 2008-01-15 13:10:16 | 1 | 875 | INSERT INTO t09 (D, B, C) VALUES … |
77D | LOCK WAIT | 2008-01-15 13:10:16 | 77D | 2008-01-15 13:10:16 | 1 | 874 | INSERT INTO t09 (D, B, C) VALUES … |
77B | LOCK WAIT | 2008-01-15 13:10:16 | 77B:733:12:1 | 2008-01-15 13:10:16 | 4 | 873 | INSERT INTO t09 (D, B, C) VALUES … |
77A | RUNNING | 2008-01-15 13:10:16 | NULL | NULL | 4 | 872 | SELECT b, c FROM t09 WHERE … |
E56 | LOCK WAIT | 2008-01-15 13:10:06 | E56:743:6:2 | 2008-01-15 13:10:06 | 5 | 384 | INSERT INTO t2 VALUES … |
E55 | LOCK WAIT | 2008-01-15 13:10:06 | E55:743:38:2 | 2008-01-15 13:10:13 | 965 | 257 | INSERT INTO t2 VALUES … |
19C | RUNNING | 2008-01-15 13:09:10 | NULL | NULL | 2900 | 130 | INSERT INTO t2 VALUES … |
E15 | RUNNING | 2008-01-15 13:08:59 | NULL | NULL | 5395 | 61 | INSERT INTO t2 VALUES … |
51D | RUNNING | 2008-01-15 13:08:47 | NULL | NULL | 9807 | 8 | INSERT INTO t2 VALUES … |
下 table 显示了运行重workload的系统的INFORMATION_SCHEMA.INNODB_LOCK_WAITS的内容。
请求 trx ID | 请求的锁 ID | 阻止 trx ID | 阻止锁 ID |
---|---|---|---|
77F | 77F:806 | 77E | 77E:806 |
77F | 77F:806 | 77D | 77D:806 |
77F | 77F:806 | 77B | 77B:806 |
77E | 77E:806 | 77D | 77D:806 |
77E | 77E:806 | 77B | 77B:806 |
77D | 77D:806 | 77B | 77B:806 |
77B | 77B:733:12:1 | 77A | 77A:733:12:1 |
E56 | E56:743:6:2 | E55 | E55:743:6:2 |
E55 | E55:743:38:2 | 19C | 19C:743:38:2 |
下 table 显示了运行重workload的系统的INFORMATION_SCHEMA.INNODB_LOCKS的内容。
lock id | 锁定 trx ID | lock mode | lock type | lock table | lock index | lock data |
---|---|---|---|---|---|---|
77F:806 | 77F | AUTO_INC | TABLE | test.t09 | NULL | NULL |
77E:806 | 77E | AUTO_INC | TABLE | test.t09 | NULL | NULL |
77D:806 | 77D | AUTO_INC | TABLE | test.t09 | NULL | NULL |
77B:806 | 77B | AUTO_INC | TABLE | test.t09 | NULL | NULL |
77B:733:12:1 | 77B | X | RECORD | test.t09 | PRIMARY | supremum pseudo-record |
77A:733:12:1 | 77A | X | RECORD | test.t09 | PRIMARY | supremum pseudo-record |
E56:743:6:2 | E56 | S | RECORD | test.t2 | PRIMARY | 0, 0 |
E55:743:6:2 | E55 | X | RECORD | test.t2 | PRIMARY | 0, 0 |
E55:743:38:2 | E55 | S | RECORD | test.t2 | PRIMARY | 1922, 1922 |
19C:743:38:2 | 19C | X | RECORD | test.t2 | PRIMARY | 1922, 1922 |