InnoDB INFORMATION_SCHEMA事务和锁定信息
注意本节描述了性能模式
data_locks
和data_lock_waits
表所公开的锁定信息,这些信息取代了MySQL 8.0中的INFORMATION_SCHEMA
INNODB_LOCKS
和INNODB_LOCK_WAITS
表。对于用旧的条款类似的讨论INFORMATION_SCHEMA
桌,看到的InnoDB INFORMATION_SCHEMA交易及锁定信息中的MySQL 5.7参考手册。
一个INFORMATION_SCHEMA
表和两个Performance Schema表使您可以监视InnoDB
事务并诊断潜在的锁定问题:
INNODB_TRX
:此INFORMATION_SCHEMA
表提供有关内部当前正在执行的每个事务的信息InnoDB
,包括事务状态(例如,它是正在运行还是正在等待锁),事务何时开始以及事务正在执行的特定SQL语句。data_locks
:此性能模式表中的每个保留锁行和每个被锁定等待释放保留锁的锁请求都包含一行:- 有一行各举行了锁,无论是持有锁的事务的状态(
INNODB_TRX.TRX_STATE
是RUNNING
,LOCK WAIT
,ROLLING BACK
或COMMITTING
)。 InnoDB中等待另一个事务释放锁(
INNODB_TRX.TRX_STATE
isLOCK WAIT
)的每个事务都恰好被一个阻塞锁定请求阻塞。该阻塞锁请求是针对另一个事务以不兼容模式持有的行锁或表锁。锁定请求始终具有与阻止请求的保持锁定模式不兼容的模式(读与写,共享与独占)。在其他事务提交或回滚之前,被阻止的事务无法继续进行,从而释放请求的锁。对于每个被阻止的事务,
data_locks
包含一行,描述该事务已请求的每个锁以及正在等待的锁。
- 有一行各举行了锁,无论是持有锁的事务的状态(
data_lock_waits
:此性能模式表指示哪些事务正在等待给定的锁,或者给定事务正在等待的锁。该表为每个被阻止的事务包含一个或多个行,指示它已请求的锁以及阻止该请求的所有锁。该REQUESTING_ENGINE_LOCK_ID
值表示一个事务请求的锁,该BLOCKING_ENGINE_LOCK_ID
值表示阻止第一个事务继续进行的锁(由另一个事务持有)。对于任何给定的阻止交易,中的所有行都data_lock_waits
具有的相同值REQUESTING_ENGINE_LOCK_ID
和的不同值BLOCKING_ENGINE_LOCK_ID
。
有关上述表的更多信息,请参见“ INFORMATION_SCHEMA INNODB_TRX表”,“ data_locks表”和“ data_lock_waits表”。
使用InnoDB事务和锁定信息
注意本节描述了性能模式
data_locks
和data_lock_waits
表所公开的锁定信息,这些信息取代了MySQL 8.0中的INFORMATION_SCHEMA
INNODB_LOCKS
和INNODB_LOCK_WAITS
表。对于用旧的条款类似的讨论INFORMATION_SCHEMA
表,请参见使用InnoDB的事务和锁定信息中的MySQL 5.7参考手册。
识别阻塞交易
识别哪个交易会阻止另一个交易有时会很有帮助。包含有关InnoDB
事务和数据锁的信息的表使您能够确定哪个事务正在等待另一个事务以及正在请求哪个资源。(有关这些表的描述,请参见“ InnoDB INFORMATION_SCHEMA事务和锁定信息”。)
假设三个会话正在同时运行。每个会话都对应一个MySQL线程,并依次执行一个事务。这些会话发出以下语句时,请考虑系统状态,但没有一个提交事务:
分会A:
BEGIN ;SELECT aFROM tFOR UPDATE ;SELECT SLEEP(100);B场:
SELECT bFROM tFOR UPDATE ;C场:
SELECT cFROM tFOR UPDATE ;
在这种情况下,使用以下查询来参见哪些事务正在等待,哪些事务正在阻止它们:
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_queryFROM performance_schema.data_lock_waits wINNER JOIN information_schema.innodb_trx bON b.trx_id = w.blocking_engine_transaction_idINNER JOIN information_schema.innodb_trx rON 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_queryFROM sys.innodb_lock_waits;
如果为阻塞查询报告了NULL值,请参阅在发布会话变为空闲后识别阻塞查询。
等待trx ID | 等待线程 | 等待查询 | 阻止trx ID | 阻塞线程 | 封锁查询 |
---|---|---|---|---|---|
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 |
在上表中,您可以通过“等待查询”或“阻止查询”列来标识会话。如你看到的:
- 会话B(trx id
A4
,thread6
)和会话C(trx idA5
,thread7
)都在等待会话A(trx idA3
,thread5
)。 - 会话C正在等待会话B和会话A。
您可以在INFORMATION_SCHEMA
INNODB_TRX
表以及性能模式data_locks
和data_lock_waits
表中看到基础数据。
下表显示了该表的一些示例内容INNODB_TRX
。
trx ID | trx状态 | trx开始 | trx请求的锁定ID | trx等待开始 | trx重量 | trx mysql线程ID | trx查询 |
---|---|---|---|---|---|---|---|
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 |
下表显示了该表的一些示例内容data_locks
。
锁号 | 锁定trx ID | 锁定模式 | 锁类型 | 锁模式 | 锁表 | 锁索引 | 锁定数据 |
---|---|---|---|---|---|---|---|
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 |
下表显示了该表的一些示例内容data_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值。在这种情况下,请使用以下步骤确定阻止查询:
- 标识阻止事务的进程列表ID。在
sys.innodb_lock_waits
表中,阻塞事务的进程列表ID是该blocking_pid
值。 使用
blocking_pid
,查询MySQL Performance Schemathreads
表以确定THREAD_ID
阻塞事务的。例如,如果ablocking_pid
为6,则发出以下查询:SELECT THREAD_IDFROM performance_schema.threadsWHERE PROCESSLIST_ID = 6;使用
THREAD_ID
,查询“性能模式”events_statements_current
表以确定该线程执行的最后一个查询。例如,如果的THREAD_ID
值为28,则发出以下查询:SELECT THREAD_ID, SQL_TEXTFROM performance_schema.events_statements_currentWHERE THREAD_ID = 28\G如果线程执行的最后一个查询没有足够的信息确定锁定原因,则可以查询“性能模式”
events_statements_history
表以参见线程执行的最后10条语句。SELECT THREAD_ID, SQL_TEXTFROM performance_schema.events_statements_historyWHERE THREAD_ID = 28ORDER BY EVENT_ID;
将InnoDB事务与MySQL会话相关联
有时,将内部InnoDB
锁定信息与MySQL维护的会话级信息相关联很有用。例如,对于给定的InnoDB
事务ID ,您可能想知道对应的MySQL会话ID和可能持有锁并因此阻止其他事务的会话的名称。
该INFORMATION_SCHEMA
INNODB_TRX
表以及性能模式data_locks
和data_lock_waits
表的以下输出是从某种程度上已加载的系统中获取的。可以看出,有几个事务正在运行。
下面data_locks
和data_lock_waits
表显示:
- 交易
77F
(执行的INSERT
)正在等待交易77E
,77D
以及77B
提交。 - 事务
77E
(正在执行INSERT
)正在等待事务77D
并77B
提交。 - 事务
77D
(正在执行INSERT
)正在等待事务77B
提交。 - 事务
77B
(正在执行INSERT
)正在等待事务77A
提交。 - 事务
77A
正在运行,当前正在执行SELECT
。 - 事务
E56
(正在执行INSERT
)正在等待事务E55
提交。 - 事务
E55
(正在执行INSERT
)正在等待事务19C
提交。 - 事务
19C
正在运行,当前正在执行INSERT
。
注意
INFORMATION_SCHEMA
PROCESSLIST
和INNODB_TRX
表中显示的查询之间可能存在不一致。有关说明,请参见“ InnoDB事务和锁定信息的持久性和一致性”。
下表显示了PROCESSLIST
运行大量工作负载的系统的表内容。
ID | 用户 | 主办 | D B | 命令 | 时间 | 州 | 信息 |
---|---|---|---|---|---|---|---|
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 |
下表显示了INNODB_TRX
运行大量工作负载的系统的表内容。
trx ID | trx状态 | trx开始 | trx请求的锁定ID | trx等待开始 | trx重量 | trx mysql线程ID | trx查询 |
---|---|---|---|---|---|---|---|
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 … |
trx ID | trx状态 | trx开始 | trx请求的锁定ID | trx等待开始 | trx重量 | trx mysql线程ID | trx查询 |
---|
下表显示了data_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 |
下表显示了data_locks
运行大量工作负载的系统的表内容。
锁号 | 锁定trx ID | 锁定模式 | 锁类型 | 锁模式 | 锁表 | 锁索引 | 锁定数据 |
---|---|---|---|---|---|---|---|
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 |
注意本节描述了性能模式
data_locks
和data_lock_waits
表所公开的锁定信息,这些信息取代了MySQL 8.0中的INFORMATION_SCHEMA
INNODB_LOCKS
和INNODB_LOCK_WAITS
表。对于用旧的条款类似的讨论INFORMATION_SCHEMA
桌,看到 InnoDB的锁锁等待信息中的MySQL 5.7参考手册。
当一个事务在一个表中更新了一行,或将其锁定SELECT FOR UPDATE
,InnoDB
建立在该行锁的列表或队列。同样,InnoDB
为表级锁维护表上的锁列表。如果第二个事务想要以不兼容的模式更新行或锁定已经由先前事务锁定的表InnoDB
,则将对该行的锁定请求添加到相应的队列中。为了使事务获取锁,必须删除先前输入到该行或表的锁队列中的所有不兼容锁请求(当持有或请求这些锁的事务提交或回滚时会发生这种情况)。
事务可以具有针对不同的行或表的任意数量的锁定请求。在任何给定的时间,一个事务可以请求另一个事务持有的锁,在这种情况下,该事务被另一个事务阻止。请求事务必须等待持有阻塞锁的事务提交或回滚。如果事务不等待锁,则它处于RUNNING
状态。如果事务正在等待锁,则它处于LOCK WAIT
状态。(该INFORMATION_SCHEMA
INNODB_TRX
表指示事务状态值。)
性能架构data_locks
表为每个LOCK WAIT
事务保存一个或多个行,指示任何阻止其进度的锁定请求。该表还包含一行,描述给定行或表的未决锁队列中的每个锁。性能模式data_lock_waits
表显示了事务已持有的哪些锁正在阻止其他事务请求的锁。
InnoDB锁定和锁定等待信息
注意本节描述了性能模式
data_locks
和data_lock_waits
表所公开的锁定信息,这些信息取代了MySQL 8.0中的INFORMATION_SCHEMA
INNODB_LOCKS
和INNODB_LOCK_WAITS
表。对于用旧的条款类似的讨论INFORMATION_SCHEMA
桌,看到 InnoDB的锁锁等待信息中的MySQL 5.7参考手册。
当一个事务在一个表中更新了一行,或将其锁定SELECT FOR UPDATE
,InnoDB
建立在该行锁的列表或队列。同样,InnoDB
为表级锁维护表上的锁列表。如果第二个事务想要以不兼容的模式更新行或锁定已经由先前事务锁定的表InnoDB
,则将对该行的锁定请求添加到相应的队列中。为了使事务获取锁,必须删除先前输入到该行或表的锁队列中的所有不兼容锁请求(当持有或请求这些锁的事务提交或回滚时会发生这种情况)。
事务可以具有针对不同的行或表的任意数量的锁定请求。在任何给定的时间,一个事务可以请求另一个事务持有的锁,在这种情况下,该事务被另一个事务阻止。请求事务必须等待持有阻塞锁的事务提交或回滚。如果事务不等待锁,则它处于RUNNING
状态。如果事务正在等待锁,则它处于LOCK WAIT
状态。(该INFORMATION_SCHEMA
INNODB_TRX
表指示事务状态值。)
性能架构data_locks
表为每个LOCK WAIT
事务保存一个或多个行,指示任何阻止其进度的锁定请求。该表还包含一行,描述给定行或表的未决锁队列中的每个锁。性能模式data_lock_waits
表显示了事务已持有的哪些锁正在阻止其他事务请求的锁。
InnoDB事务和锁定信息的持久性和一致性
注意本节描述了性能模式
data_locks
和data_lock_waits
表所公开的锁定信息,这些信息取代了MySQL 8.0中的INFORMATION_SCHEMA
INNODB_LOCKS
和INNODB_LOCK_WAITS
表。对于用旧的条款类似的讨论INFORMATION_SCHEMA
表,请参见持久性和InnoDB事务和锁定信息的一致性中的MySQL 5.7参考手册。
事务表和锁定表(INFORMATION_SCHEMA
INNODB_TRX
表,性能模式data_locks
和data_lock_waits
表)公开的数据代表对快速变化的数据的一瞥。这与用户表不同,用户表仅在应用程序启动的更新发生时才更改数据。基础数据是内部系统管理的数据,并且可以快速更改:
数据可能不是之间是一致的
INNODB_TRX
,data_locks
和data_lock_waits
表。在
data_locks
与data_lock_waits
表暴露从实时数据InnoDB
存储引擎,提供有关的交易锁定inormationINNODB_TRX
表。从锁表检索的数据在SELECT
执行时存在,但在客户端使用查询结果时可能已消失或更改。data_locks
与联接data_lock_waits
可以显示中的行data_lock_waits
,该行标识data_locks
不再存在或尚不存在的父行。事务表和锁定表中的数据可能与
INFORMATION_SCHEMA
PROCESSLIST
表或性能模式threads
表中的数据不一致。例如,在比较
InnoDB
事务表和锁定表中的数据与表中的数据时,应格外小心PROCESSLIST
。即使您发出单个SELECT
(例如,joinINNODB_TRX
和PROCESSLIST
),这些表的内容也通常不一致。可能INNODB_TRX
会引用其中不存在的行,PROCESSLIST
或者所引用的事务中当前正在执行的SQL查询与中的INNODB_TRX.TRX_QUERY
行不同PROCESSLIST.INFO
。