我将介绍如何使用 data_locks 表来检查 MySQL 8 中的有用锁。

大家好，
我是Mandai，Wild团队负责开发工作的成员。

这次，我们将介绍一种在 MySQL 8 中检查锁定状态的便捷方法。

检查死锁时，SHOW ENGINE INNODB STATUS\G 轻松找到SHOW ENGINE INNODB STATUS\G 确认

MySQL 8 中添加到 performance_schema 的 data_locks 和 data_lock_waits 表在这种情况下非常有用。

data_locks 表是什么？

data_locks 表是一个保存 InnoDB 锁信息的表。

我们创建一个合适的表，加个锁，看看会发生什么。

-- 创建数据库 CREATE DATABASE test; -- 创建表 CREATE TABLE users ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, PRIMARY KEY (`id`), KEY `idx_username` (`username`), UNIQUE KEY `unique_email` (`email`) );

此时，请查看 data.locks 表。

SELECT * FROM performance_schema.data_locks; 空集 (0.001 秒)

是的，什么也没发生。

接下来，向该表格中添加一条数据。

INSERT INTO users (username, email) VALUES ('test', '[email protected]');

接下来，我们启动一个事务，并SELECT ... FOR UPDATE 应用锁

-- 开始事务 BEGIN; -- 应用锁 SELECT * FROM users WHERE username = 'test' FOR UPDATE;

让我们在不提交的情况下查看 data_locks 表。

SELECT * FROM performance_schema.data_locks; +--------+---------------------------------------+-----------------------+----------+----------+--------------+-------------+----------------+-------------------+--------------+----------------------+----------+---------------+-------------+-------------------------+ | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +---------+---------------------------------------+----------------------+------------+---------+--------------+-------------+----------------+-------------------+--------------+----------------------+----------+--------------------------+-------------+-------------------------+ | INNODB | 139629897444568:4067:139629789500112 | 432725 | 56 | 170 | test | users | NULL | NULL | NULL | 139629789500112 | TABLE | IX | GRANTED | NULL | | INNODB | NULL | NULL | idx_username | 139629789497120 | RECORD | idx_username | 139629789497120 | RECORD | | 139629789497464 | 记录 |

出现了 4 条数据记录。data_locks
现在 表 表用于可视化当前对该表持有锁的事务。

这里有一些重要信息，让我们来看看这些列。

引擎交易ID

这是交易 ID。
此值SHOW ENGINE INNODB STATUS\G 可以通过运行 Trx id 。

交易的详细信息存储在 information_schema 表的 innodb_trx 表中，因此您可以通过 trx_id 搜索获取更多信息。

对象架构

这是表所在的模式名称。
在频繁发生锁定或存在多个模式的环境中，也需要检查此项。

对象名称

这是表名。
我遇到的一个案例中，`data_locks` 表有超过 150 条记录，而且各个表都不一样，所以我用这一列来筛选结果。

索引名称

这是被锁定索引的名称。
“PRIMARY” ，则表示主键已被锁定。如果某个特定索引被锁定，则会显示该索引的名称。

如果为 NULL，则表示表已锁定。

锁类型

这指的是锁的类型。
表锁 和 记录锁 有两种类型： 表锁 表示整个表被锁定， 记录锁 表示一条记录被锁定。

锁定模式

这些是锁定模式。
， IX、X和REC_NOT_GAP。IX 有三种：
中 是意图排他锁，如果存在表锁，则该锁以这种模式应用。X
他 锁，单独显示时，它是下一个键锁。REC_NOT_GAP
记录 锁，表明它不是间隙锁。

还有其他类型和模式的锁定装置可供选择； 官方文档 请参阅

锁定状态

这表示锁的状态。
如果已获取锁 “已授予” ；如果尚未获取锁，意味着正在等待另一个查询完成其锁的获取，则会 “等待” 显示

锁定数据

此窗口显示与锁相关的数据。
对于 INNODB 数据库， RECORD 时才显示 TABLE 数据库 ，则输出 NULL。

对于复合指数，会显示多个值，并用逗号分隔。

此外， “最高伪记录” ，则表示该伪记录代表索引的最小上限。
由于它是伪记录，因此不存在实际记录，而是因为机制需要锁定终止而人为创建的记录。

data_lock_waits 表是什么？

如果在另一个会话中尝试锁定同一条记录，而用户表上已经持有锁，则会发生锁等待。data_lock_waits
用于检查正在等待此锁的查询 表 。

我们来尝试按以下步骤重现这个问题。
你需要两个终端，所以请使用两个不同的终端连接到 MySQL。

前面提到的 SELECT ... FOR UPDATE 语句 执行

-- 在终端 1 中执行 -- 开始事务 BEGIN; -- 应用锁 SELECT * FROM users WHERE username = 'test' FOR UPDATE;

-- 在终端 2 中连接到 MySQL 并执行以下查询 -- 启动事务 BEGIN; -- 应用锁 SELECT * FROM users WHERE username = 'test' FOR UPDATE;

运行此查询时，终端 2 屏幕可能会出现卡顿。
后台正在进行锁定等待操作，导致无法返回结果。

首先，我们来检查 data_locks 表。

SELECT * FROM performance_schema.data_locks; +--------+---------------------------------------+-----------------------+----------+----------+--------------+-------------+----------------+-------------------+--------------+----------------------+----------+---------------+-------------+-------------------------+ | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +---------+---------------------------------------+----------------------+------------+---------+--------------+-------------+----------------+-------------------+--------------+----------------------+----------+--------------------------+-------------+-------------------------+ | INNODB | 139629897446184:4067:139629789512192 | 432729 | 60 | 89 | test | users | NULL | NULL | NULL | 139629789512192 | TABLE | IX | GRANTED | NULL | | INNODB | NULL | idx_username | 139629789509280 | RECORD | X | WAITING | 'test', 1 | | INNODB | TABLE | IX | GRANTED | NULL | | INNODB | 139629897444568:2805:6:1:139629789497120 | 432727 | 56 | 262 | test | users | NULL | NULL | idx_username | 139629789497120 | RECORD | GRANTED | supremum pseudo-record | | INNODB | 139629897444568:2805:6:2:139629789497120 | 432727 | 56 | 262 | test | users | NULL | NULL | idx_username | 139629789497120 | RECORD | GRANTED | 'test', 1 | | INNODB | 139629897444568:2805:4:2:139629789497464 | 432727 | 56 | 262 | test | users | NULL | NULL | PRIMARY | 139629789497464 | RECORD | X,REC_NOT_GAP | GRANTED | 1 | +---------+---------------------------------------+----------------------+------------+----------+--------------------------+-------------+----------------+-------------------+--------------+----------------------+----------+--------------------------+-------------+-------------------------+ 6 rows in set (0.001 sec)

刚才只有 4 个锁，现在锁的数量已经增加到 6 个。
查看新增的两个锁， LOCK_STATUS 的 WAITING 为

这是锁等待状态。

接下来，我们来检查 data_lock_waits 表。

SELECT * FROM performance_schema.data_lock_waits; +--------+---------------------------------------+---------------------------------+----------------------+--------------------+------------------------------------------------+---------------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+ | ENGINE | REQUESTING_ENGINE_LOCK_ID | REQUESTING_ENGINE_TRANSACTION_ID | REQUESTING_THREAD_ID | REQUESTING_EVENT_ID | REQUESTING_OBJECT_INSTANCE_BEGIN | BLOCKING_ENGINE_LOCK_ID | BLOCKING_ENGINE_TRANSACTION_ID | BLOCKING_THREAD_ID | BLOCKING_EVENT_ID | BLOCKING_OBJECT_INSTANCE_BEGIN | +---------+---------------------------------------+--------------------+----------------------+----------------------+-------------------------------------+------------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+ | INNODB | 139629897446184:2805:6:2:139629789509280 | 432729 | 60 | 89 | 139629789509280 | 139629897444568:2805:6:2:139629789497120 | 432727 | 56 | 262 | 139629789497120|

这里也只找到了一条数据。

REQUESTING_ENGINE_TRANSACTION_ID 值 ENGINE_TRANSACTION_ID 通过将 LOCK_STATUS 为 WAITING 的 查询，正在发生锁等待

此外，BLOCKING_ENGINE_TRANSACTION_ID 和 BLOCKING_ENGINE_LOCK_ID 指示哪个锁导致了锁等待。

基于这些信息，我们可以识别出需要停止的查询，但仅使用模式和表名来追踪这些查询确实很困难。
因此，虽然需要预先配置，但有一种方法可以检查实际正在运行的查询。

当然，如果你努力尝试，可以使用二进制日志来跟踪它，但是除非查询很少，否则仅使用查询很难跟踪它，因此使用 events_statements_history_long 表进行调查会更有效，这将在下一节中解释。

启用 events_statements_history_long 表

默认情况下，events_statements_history_long 表的写入功能处于禁用状态，需要手动启用。您
可以通过在线执行以下 SQL 语句来启用此功能：

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'events_statements_history_long';

完成此操作后，后续查询将记录在 events_statements_history_long 表中，但如果您想永久保留此设置，请将以下设置也添加到 my.cnf 中：

[mysqld] performance-schema-consumer-events-statements-history-long=ON

每个线程保存的项目数等于以下查询值对应的项目数。

显示类似“performance_schema_events_statements_history_long_size”的变量； +----------------------------------------------------+-------+ | 变量名 | 值 | +----------------------------------------------------------+-------+ | performance_schema_events_statements_history_long_size | 10000 | +----------------------------------------------------+-------+ 1 行结果 (0.002 秒)

此值为只读，因此如果您想更改它，需要将以下设置添加到 my.cnf 并重启：

[mysqld] performance_schema_events_statements_history_long_size=20000

events_statements_history_long 表中的数据存储在内存中，因此如果值过大，可能会导致内存拥塞。

所有已执行的查询， `performance_schema_events_statements_history_long_size` 表 存储了
`performance_schema_events_statements_history_long_size` 为 10,000 条记录，则当插入第 10,001 条记录时，第 1 条记录将被删除。因此，如果您知道具体的执行来源（例如，与 Web 服务器不同的批处理进程），则使用 `events_statements_history` 表可能更为合适。

events_statements_history 表 performance_schema_events_statements_history_size 设置的条目数
。 的默认自动大小设置为 10 个条目，这有点不够，但也可以在 my.cnf 中更改（需要重启）

events_statements_history 表默认启用，因此无需任何配置即可使用。

概括

这次，我们介绍了如何检查 MySQL 尚未确定为死锁的锁类型。

启用 events_statements_history_long 表的一个副作用是，除了查询之外，它还包含各种执行结果，例如错误详情和内存使用情况，因此定期检查它是很有必要的。

就这样。