www. On IT Road .com
系统架构
在 MySQL 5.6 及更高版本中，我们可以使用 sys 架构中的 innodb_lock_waits 视图来查找等待锁的事务和持有阻塞锁的事务。
sys 架构默认安装在 MySQL 5.7 及更高版本中。
在 MySQL 5.6 中，我们需要手动安装它。
innodb_lock_waits 视图使用与下面讨论的相同的信息模式表。
要查找哪些事务正在等待锁定以及谁在阻塞的所有当前情况，我们可以使用：
mysql> SELECT * FROM sys.innodb_lock_waits\G
*** 1. row ***
wait_started: 2019-05-20 08:25:11
wait_age: 00:00:09
wait_age_secs: 9
locked_table: `world`.`city`
locked_table_schema: world
locked_table_name: city
locked_table_partition: NULL
locked_table_subpartition: NULL
locked_index: PRIMARY
locked_type: RECORD
waiting_trx_id: 6419
waiting_trx_started: 2019-05-20 08:25:11
waiting_trx_age: 00:00:09
waiting_trx_rows_locked: 1
waiting_trx_rows_modified: 0
waiting_pid: 10
waiting_query: UPDATE world.city SET Population = Population + 1 WHERE ID = 130
waiting_lock_id: 139676533684912:3:7:41:139676430164472
waiting_lock_mode: X,REC_NOT_GAP
blocking_trx_id: 6415
blocking_pid: 9
blocking_query: NULL
blocking_lock_id: 139676533684016:3:7:41:139676430158520
blocking_lock_mode: X,REC_NOT_GAP
blocking_trx_started: 2019-05-20 08:20:56
blocking_trx_age: 00:04:24
blocking_trx_rows_locked: 1
blocking_trx_rows_modified: 1
sql_kill_blocking_query: KILL QUERY 9
sql_kill_blocking_connection: KILL 9
1 row in set (0.00 sec)
在上面的示例中，blocking_query 为 NULL。
这是因为持有锁的事务当前没有执行任何查询。
在 MySQL 5.6 及更高版本中，最近执行的查询可以在 performance_schema.events_statements_current 表和使用 statements 历史表的旧查询中找到。
另请参阅参考手册中的性能模式语句事件表。
默认情况下，查询将被截断为 64 个字符或者使用 statement_truncate_len Sys Schema 选项进行配置。
要更改会话的截断截止时间，请设置 @sys.statement_truncate_len 用户变量，例如：
mysql> SET @sys.statement_truncate_len = 16;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM sys.innodb_lock_waits\G
*** 1. row ***
wait_started: 2019-05-20 08:25:11
wait_age: 00:00:20
wait_age_secs: 20
locked_table: `world`.`city`
locked_table_schema: world
locked_table_name: city
locked_table_partition: NULL
locked_table_subpartition: NULL
locked_index: PRIMARY
locked_type: RECORD
waiting_trx_id: 6419
waiting_trx_started: 2019-05-20 08:25:11
waiting_trx_age: 00:00:20
waiting_trx_rows_locked: 1
waiting_trx_rows_modified: 0
waiting_pid: 10
waiting_query: UPDATE ... = 130
waiting_lock_id: 139676533684912:3:7:41:139676430164472
waiting_lock_mode: X,REC_NOT_GAP
blocking_trx_id: 6415
blocking_pid: 9
blocking_query: NULL
blocking_lock_id: 139676533684016:3:7:41:139676430158520
blocking_lock_mode: X,REC_NOT_GAP
blocking_trx_started: 2019-05-20 08:20:56
blocking_trx_age: 00:04:35
blocking_trx_rows_locked: 1
blocking_trx_rows_modified: 1
sql_kill_blocking_query: KILL QUERY 9
sql_kill_blocking_connection: KILL 9
1 row in set (0.00 sec)
要使查询不被截断，请改用 x$innodb_lock_waits 视图。
显示进程列表
语法
SHOW PROCESSLIST; SHOW [FULL] PROCESSLIST;
进程列表输出是调查 InnoDB 锁定问题的最基本工具。
例如：
mysql> SHOW PROCESSLIST; +-----+-----------------+-----------+----------+---------+------+-----------------------------+---------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+-----------------+-----------+----------+---------+------+-----------------------------+---------------------------------------+ | 1 | event_scheduler | localhost | NULL | Daemon | 4 | Waiting for next activation | NULL | | 328 | root | localhost | locktest | Sleep | 222 | | NULL | | 332 | root | localhost | locktest | Query | 38 | updating | UPDATE t1 SET val = 'c2' WHERE id = 3 | | 340 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST | +-----+-----------------+-----------+----------+---------+------+-----------------------------+---------------------------------------+ 4 rows in set (0.00 sec)
因此，在这种情况下，使用 PRIMARY KEY（t1.id 列）查找行的单行更新需要 38 秒是可疑的。
可能阻止 UPDATE 继续进行的候选查询至少必须比 UPDATE 等待的时间早，但是这可能难以从进程列表中确定。
问题是 InnoDB 是事务性的，这意味着它可能不是现在运行的查询持有锁。
例如采用以下输出：
mysql> SHOW PROCESSLIST; +-----+-----------------+-----------+----------+---------+------+-----------------------------+---------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+-----------------+-----------+----------+---------+------+-----------------------------+---------------------------------------------+ | 1 | event_scheduler | localhost | NULL | Daemon | 0 | Waiting for next activation | NULL | | 328 | root | localhost | locktest | Query | 1 | User sleep | SELECT COUNT(*) FROM t1 WHERE 0 = SLEEP(10) | | 332 | root | localhost | locktest | Query | 40 | updating | UPDATE t1 SET val = 'c2' WHERE id = 3 | | 340 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST | +-----+-----------------+-----------+----------+---------+------+-----------------------------+---------------------------------------------+ 4 rows in set (0.00 sec)
这是与上面相同的示例，但 Id = 328 的连接尚未开始运行另一个查询。
因此，在第一个输出中，可以使用 Time 列对查询进行排序，而在第二个输出中，Id 列似乎是更好的候选者。
然而，即使这样也可能太简单了：虽然 Id 列会说明连接的顺序，但它是锁定的顺序。
这根本不可能从进程列表中确定，因此最多可以使用 SHOW PROCESSLIST 输出来提示正在发生的事情。
但是，结合 SHOW ENGINE INNODB STATUS 的输出，它可以提供有价值的信息。
显示引擎 INNODB 状态
InnoDB 状态输出比进程列表好得多，可以深入了解哪些事务持有锁。
继续上面的示例，输出可能如下所示：
mysql> SHOW ENGINE INNODB STATUS\G *** 1. row *** Type: InnoDB Name: Status: ===================================== 2013-02-25 11:05:21 7f5d5b6cd700 INNODB MONITOR OUTPUT ===================================== ... ----------- TRANSACTIONS ----------- Trx id counter 497211 Purge done for trx's n:o < 497201 undo n:o < 0 state: running but idle History list length 1484 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 340, OS thread handle 0x7f5d5b6cd700, query id 20971 localhost root init SHOW ENGINE INNODB STATUS ---TRANSACTION 497138, ACTIVE 47 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 332, OS thread handle 0x7f5d5b70e700, query id 20559 localhost root updating UPDATE t1 SET val = 'c2' WHERE id = 3 ------- TRX HAS BEEN WAITING 47 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 32 page no 3 n bits 96 index `PRIMARY` of table `locktest`.`t1` trx id 497138 lock_mode X locks rec but not gap waiting Record lock, heap no 28 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 00000003; asc ;; 1: len 6; hex 0000000791e6; asc ;; 2: len 7; hex 5100000039232c; asc Q 9#,;; 3: len 2; hex 6331; asc c1;; ----------------- ---TRANSACTION 496102, ACTIVE 600 sec mysql tables in use 1, locked 0 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1 MySQL thread id 328, OS thread handle 0x7f5d5b74f700, query id 20879 localhost root User sleep SELECT COUNT(*) FROM t1 WHERE 0 = SLEEP(10) Trx read view will not see trx with id >= 497195, sees < 497138 ...
从这个输出中，可以看到'UPDATE t1 SET val = 'c2' WEHRE id = 3'查询正在等待哪个锁，并且可以看到有一个事务已经活动了600秒持有一个锁（请参阅 1 行锁，撤消日志条目 1）。
所以这标识了持有锁的事务。
事务列表中的详细信息还显示了 MySQL 线程 ID（持有锁的事务为 328）。
我们可以使用此信息返回进程列表输出，并查看例如持有锁的连接的用户名和主机。
要让 InnoDB 包含有关当前没有冲突的锁的信息，我们必须启用 innodb_status_output_locks 选项：
mysql> SET GLOBAL innodb_status_output_locks = ON; Query OK, 0 rows affected (0.00 sec)
在这篇文章中，我们将了解可用于调查阻止查询运行的 InnoDB 锁问题的工具。
可以在所有 MySQL 版本中用于调查阻止 InnoDB 查询继续进行的锁的两个命令是 SHOW [FULL] PROCESSLIST 和 SHOW ENGINE INNODB STATUS 。
此外，在使用 InnoDB 插件的 MySQL 5.1 和 MySQL 5.5 及更高版本中，可以使用信息架构中的 InnoDB 表来获取有关 InnoDB 锁的详细信息。
下面将讨论这些方法中的每a)
InnoDB 信息架构表
对于使用 InnoDB 插件的 MySQL 5.1 以及在 MySQL 5.5 及更高版本中，还有另一种获取有关锁的信息的方法。
三个表对研究锁很感兴趣：
- INNODB_TRX ：包含有关 InnoDB 事务的信息。
- strong>INNODB_LOCKS ：包含有关以下 InnoDB 锁的信息：
- 已请求但尚未获取的锁。
- 阻止获取其他锁请求的锁。
- INNODB_LOCK_WAITS ：正在请求哪些锁但由于另一个事务持有该锁而无法授予。
在带有 InnoDB 插件的 MySQL 5.1 中，默认情况下不启用信息模式表，必须手动启用，例如设置插件加载配置选项，如：
[mysqld] plugin-load = "innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so;innodb_buffer_page=ha_innodb_plugin.so;innodb_buffer_page_lru=ha_innodb_plugin.so;innodb_buffer_pool_stats=ha_innodb_plugin.so"
plugin-load 的值必须都在一行上。
信息模式表相对于进程列表和 InnoDB 状态输出的一个优点是，可以编写一个查询，直接提供有关锁等待场景中涉及的事务和锁的信息。
这种查询的一个例子是：
SELECT r.trx_wait_started AS wait_started,
TIMEDIFF(NOW(), r.trx_wait_started) AS wait_age,
TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_age_secs,
rl.lock_table AS locked_table,
rl.lock_index AS locked_index,
rl.lock_type AS locked_type,
r.trx_id AS waiting_trx_id,
r.trx_started as waiting_trx_started,
TIMEDIFF(NOW(), r.trx_started) AS waiting_trx_age,
r.trx_rows_locked AS waiting_trx_rows_locked,
r.trx_rows_modified AS waiting_trx_rows_modified,
r.trx_mysql_thread_id AS waiting_pid,
r.trx_query AS waiting_query,
rl.lock_id AS waiting_lock_id,
rl.lock_mode AS waiting_lock_mode,
b.trx_id AS blocking_trx_id,
b.trx_mysql_thread_id AS blocking_pid,
b.trx_query AS blocking_query,
bl.lock_id AS blocking_lock_id,
bl.lock_mode AS blocking_lock_mode,
b.trx_started AS blocking_trx_started,
TIMEDIFF(NOW(), b.trx_started) AS blocking_trx_age,
b.trx_rows_locked AS blocking_trx_rows_locked,
b.trx_rows_modified AS blocking_trx_rows_modified,
CONCAT('KILL QUERY ', b.trx_mysql_thread_id) AS sql_kill_blocking_query,
CONCAT('KILL ', b.trx_mysql_thread_id) AS sql_kill_blocking_connection
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
INNER JOIN information_schema.innodb_locks bl ON bl.lock_id = w.blocking_lock_id
INNER JOIN information_schema.innodb_locks rl ON rl.lock_id = w.requested_lock_id
ORDER BY r.trx_wait_started\G
对于讨论的示例，此查询的输出将是：
*** 1. row ***
wait_started: 2015-11-24 11:29:48
wait_age: 00:00:04
wait_age_secs: 4
locked_table: `locktest`.`t1`
locked_index: PRIMARY
locked_type: RECORD
waiting_trx_id: 5399
waiting_trx_started: 2015-11-24 11:29:48
waiting_trx_age: 00:00:04
waiting_trx_rows_locked: 1
waiting_trx_rows_modified: 0
waiting_pid: 3
waiting_query: UPDATE t1 SET val = 'c2' WHERE id = 3
waiting_lock_id: 5399:45:3:6
waiting_lock_mode: X
blocking_trx_id: 5397
blocking_pid: 5
blocking_query: NULL
blocking_lock_id: 5397:45:3:6
blocking_lock_mode: X
blocking_trx_started: 2015-11-24 11:28:49
blocking_trx_age: 00:01:03
blocking_trx_rows_locked: 1
blocking_trx_rows_modified: 1
sql_kill_blocking_query: KILL QUERY 5
sql_kill_blocking_connection: KILL 5
1 row in set (0.00 sec)
性能模式锁表
在 MySQL 8 中，本节之后描述的具有 InnoDB 锁的信息模式表已替换为性能模式中的 data_locks 和 data_lock_waits 表。
这些是上面 sys.innodb_lock_waits 视图使用的表。
在大多数情况下，建议使用 sys 架构视图，因为它更易于使用。
但是，在某些情况下，我们可能希望直接使用 Performance Schema 来获取有关锁的更多详细信息。
数据锁的两个表是：
- data_locks ：包含当前持有的锁。
- data_lock_waits ：包含有关正在等待的锁的信息。
与 5.7 及更早版本中的 Information Schema 表（见下文）不同，data_locks 表包含所有当前持有的 InnoDB 锁。
例如：
mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE world.city SET Population = Population + 1 WHERE ID = 130; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM performance_schema.data_locks WHERE THREAD_ID = PS_CURRENT_THREAD_ID()\G
*** 1. row ***
ENGINE: INNODB
ENGINE_LOCK_ID: 139676533684016:1060:139676430161560
ENGINE_TRANSACTION_ID: 6413
THREAD_ID: 47
EVENT_ID: 15
OBJECT_SCHEMA: world
OBJECT_NAME: city
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139676430161560
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*** 2. row ***
ENGINE: INNODB
ENGINE_LOCK_ID: 139676533684016:3:7:41:139676430158520
ENGINE_TRANSACTION_ID: 6413
THREAD_ID: 47
EVENT_ID: 15
OBJECT_SCHEMA: world
OBJECT_NAME: city
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139676430158520
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 130
2 rows in set (0.00 sec)
PS_CURRENT_THREAD_ID() 函数在 MySQL 8.0.16 及更高版本中可用；在此之前使用 sys.ps_thread_id(NULL)。
可以看出，Performance Schema 表支持除 InnoDB 之外的其他存储引擎，但目前 InnoDB 是唯一实现支持的存储引擎。
我们可以看到表级锁和行级锁以及是否使用间隙模式。
LOCK_DATA 列具有用于确定要锁定哪些行的值。
由于 UPDATE 查询中的条件是 WHERE ID = 130，因此在这种情况下 LOCK_DATA 的值为 130。
要调查发生锁定等待的原因，我们需要使用 data_lock_waits 表。
我们可以重新加入 data_locks 表以获取有关所涉及锁的更多信息。
data_lock_waits 中有两列实际上是 data_locks 表的 ENGINE_LOCK_ID 列的外键：
- REQUESTING_ENGINE_LOCK_ID ：这是等待被授予的锁请求的锁 ID。
- BLOCKING_ENGINE_LOCK_ID ：这是阻止授予锁请求的锁的锁 ID。
如何查询等待锁的示例（基于 sys.innodb_lock_waits 视图）是：
SELECT r.trx_wait_started AS wait_started,
TIMEDIFF(NOW(), r.trx_wait_started) AS wait_age,
TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_age_secs,
CONCAT(sys.quote_identifier(rl.object_schema), '.', sys.quote_identifier(rl.object_name)) AS locked_table,
rl.object_schema AS locked_table_schema,
rl.object_name AS locked_table_name,
rl.partition_name AS locked_table_partition,
rl.subpartition_name AS locked_table_subpartition,
rl.index_name AS locked_index,
rl.lock_type AS locked_type,
r.trx_id AS waiting_trx_id,
r.trx_started as waiting_trx_started,
TIMEDIFF(NOW(), r.trx_started) AS waiting_trx_age,
r.trx_rows_locked AS waiting_trx_rows_locked,
r.trx_rows_modified AS waiting_trx_rows_modified,
r.trx_mysql_thread_id AS waiting_pid,
sys.format_statement(r.trx_query) AS waiting_query,
rl.engine_lock_id AS waiting_lock_id,
rl.lock_mode AS waiting_lock_mode,
b.trx_id AS blocking_trx_id,
b.trx_mysql_thread_id AS blocking_pid,
sys.format_statement(b.trx_query) AS blocking_query,
bl.engine_lock_id AS blocking_lock_id,
bl.lock_mode AS blocking_lock_mode,
b.trx_started AS blocking_trx_started,
TIMEDIFF(NOW(), b.trx_started) AS blocking_trx_age,
b.trx_rows_locked AS blocking_trx_rows_locked,
b.trx_rows_modified AS blocking_trx_rows_modified,
CONCAT('KILL QUERY ', b.trx_mysql_thread_id) AS sql_kill_blocking_query,
CONCAT('KILL ', b.trx_mysql_thread_id) AS sql_kill_blocking_connection
FROM performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = CAST(w.blocking_engine_transaction_id AS CHAR)
INNER JOIN information_schema.innodb_trx r ON r.trx_id = CAST(w.requesting_engine_transaction_id AS CHAR)
INNER JOIN performance_schema.data_locks bl ON bl.engine_lock_id = w.blocking_engine_lock_id
INNER JOIN performance_schema.data_locks rl ON rl.engine_lock_id = w.requesting_engine_lock_id
ORDER BY r.trx_wait_started;
请注意 data_lock_waits 和 data_locks 表之间的最后两个连接。
该查询还加入了 information_schema.innodb_trx 视图。
这将在下一节中讨论。
日期：2020-09-17 00:11:45 来源：oir作者：oir