我将介绍如何使用 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 秒）

是的，什么也没发生。

接下来向这个表中添加一条数据。

插入用户（用户名，电子邮件）值（'测试'，' [email protected] '）；

接下来，启动事务并SELECT ... FOR UPDATE获取锁

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

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

从 performance_schema.data_locks 中选择 *；+--------+-----------------------------------+-----------------------+----------+----------+--------------+----------------+-------------------+-----------+-----------+-----------+-------------+-------------------------+| 引擎 | 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 | 测试 | 用户 | NULL | NULL | NULL | 139629789500112 | 表 | IX | 授予 | NULL | | INNODB | NULL | NULL | idx_username | 139629789497120 | 记录 | idx_username | 139629789497120 | 记录 | | 139629789497464 | 记录 |

之前是空的，现在有四条记录了。
可以看到， data_locks表是一个可视化显示当前在表上拥有锁的事务的表。

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

引擎事务 ID

事务 ID。
此值是可以通过SHOW ENGINE INNODB STATUS\G交易 ID

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

对象架构

存储表的模式的名称。
如果您处于经常发生锁定或存在多个模式的环境中，则也需要检查这一点。

对象名称

这是表名。
我遇到过一个案例，data_locks 表中有 150 多条记录，而且各个表都不一样，所以我用这一列来缩小结果范围。

索引名称

锁定索引的名称。
PRIMARY ，则表示锁定在主键上；如果特定索引被锁定，则会显示该索引的名称。

如果为 NULL，则表被锁定。

锁类型

锁的类型。
有两种类型： TABLE和RECORD TABLE表示锁定整个表，而RECORD表示锁定某条记录。

锁定模式

这是锁定模式。
在本例中，有三种：
IX 、 X和REC_NOT_GAP IX是一种称为意向排他锁的锁，如果使用了表锁，则在此锁定模式下会施加锁定。
X是排他锁，如果单独显示，则为 next-key 锁。
REC_NOT_GAP表示这是记录锁，而不是间隙锁。

还有其他锁定类型和模式，请参阅官方文档

锁定状态

锁的状态。
如果已获取锁GRANTED 。如果未获取锁，即查询正在等待另一个查询完成锁定，则显示WAITING

锁数据

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

对于复合索引，显示多个值，以逗号分隔。

另外“supremum pseudo-record” ，则表示索引最小上限的伪记录。
由于是伪记录，因此不存在实际记录，但由于系统要求锁定结束，因此是伪记录。

什么是 data_lock_waits 表？

如果一个会话持有 users 表的锁，而另一个会话尝试锁定同一条记录，则会发生锁等待。您
data_lock_waits来检查哪些查询正在等待此锁。

让我们尝试如下重现它。
您将需要两个终端，因此请在不同的终端中连接到 MySQL。

首先，在终端 1 中，执行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 表。

从 performance_schema.data_locks 中选择 *；+--------+-----------------------------------+-----------------------+----------+----------+--------------+----------------+-------------------+-----------+-----------+-----------+-------------+-------------------------+| 引擎 | 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 | 测试 | 用户 | NULL | NULL | NULL | 139629789512192 | 表 | IX | 授予 | NULL | | INNODB | NULL | idx_username | 139629789509280 | 记录 | X | 等待 | '测试'，1 | | INNODB | 表 | IX | 授予 | NULL | | INNODB | 139629897444568：2805：6：1：139629789497120 | 432727 | 56 | 262 | 测试 | 用户 | NULL | NULL | idx_username | 139629789497120 | 记录 | 授予 | 最高伪记录 | | INNODB | 139629897444568：2805：6：2：139629789497120 | 432727 | 56 | 262 | 测试 | 用户 | NULL | NULL | idx_username | 139629789497120 | 记录 | 授予 | '测试'，1 | | INNODB | 139629897444568：2805：4：2：139629789497464 | 432727 | 56 | 262 | 测试 | 用户 | NULL | NULL | PRIMARY | 139629789497464 | 记录 | X，REC_NOT_GAP | 已授予 | 1 | +---------+---------------------------------------+------------------------+------------+-----------+-------------+-------------+----------------+-------------------+---------------+-------------------------+-------------+-------------------------+ 集合中有 6 行（0.001 秒）

之前只有四把锁，现在有六把了。
查看新增的两把锁，可以看到LOCK_STATUS 状态是WAITING

这是锁等待状态。

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

从 performance_schema.data_lock_waits 中选择 *；+--------+-----------------------------------+---------------------------------+---------------------------------+---------------------------------+----------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+| 引擎 | 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_IDLOCK_STATUS为WAITING的查询正在发生锁等待

还BLOCKING_ENGINE_TRANSACTION_ID和BLOCKING_ENGINE_LOCK_ID中找出导致锁等待的锁

通过这些信息，您可以识别需要停止的查询，但仅使用模式和表名确实很难追踪它们。
因此，有一种方法可以检查实际正在执行的查询，尽管这需要一些预先设置。

当然，如果你努力的话，你可以使用二进制日志来跟踪它，但我认为除非流动的查询很少，否则单独使用查询来跟踪它会很困难，因此使用 events_statements_history_long 表进行调查会很有效，这将在下一节中解释。

启用 events_statements_history_long 表

默认情况下，向 events_statements_history_long 表写入数据是被禁用的，因此您需要启用它。
您可以通过执行以下 SQL 来在线启用它：

更新 performance_schema.setup_consumers 设置 ENABLED = 'YES' 其中 NAME = 'events_statements_history_long'；

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

[mysqld] 性能模式消费者事件语句历史长=开启

每个线程保存的项目数是等于下面查询值的项目数。

显示类似“performance_schema_events_statements_history_long_size”的变量；+----------------------------------------+-------------------+ | Variable_name | Value | +----------------------------------------------+-------+ | 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_sizeperformance_schema_events_statements_history_long_size为 10,000，则在插入第 10,001 个查询时，第一个查询将被删除。因此，如果您知道具体的执行源（例如，与 Web 服务器不同的批处理进程），则最好使用 events_statements_history 表。

events_statements_history 表performance_schema_events_statements_history_size中为每个线程设置的条目数
的为 10 条，略显不足，但此设置也可以在 my.cnf 中更改（需要重启）。

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

概括

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

启用 events_statements_history_long 表的副作用是，除了查询之外，它还包含各种执行结果，例如错误详细信息和内存使用情况，因此定期检查它是个好主意。

就是这样。