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

你好。
我是Mandai，负责Wild 开发团队。

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

检查死锁时，您可能会发现检查SHOW ENGINE INNODB STATUS\GSHOW 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 | 记录 |

之前是空的，但现在有四条记录。
如您所见， data_locks表用于可视化当前对表持有锁的事务。

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

引擎交易ID

事务 ID。
此值可通过SHOW ENGINE INNODB STATUS\GTrx id 。

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

对象架构

表所在的模式名称。
如果您所处的环境中频繁发生锁定或存在多个模式，则还需要检查此项。

对象名称

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

索引名称

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

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

锁类型

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

锁定模式

这是锁定模式。
在本例中，有三种模式：
IX 、 X和REC_NOT_GAP。IX一种称为意图排他锁的锁，如果应用了表锁，则该锁将以这种锁定模式应用。X
他锁，如果单独显示，则表示它是下一个键锁。REC_NOT_GAP
表示它是记录锁，而不是间隙锁。

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

锁定状态

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

锁定数据

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

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

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

data_lock_waits 表是什么？

如果一个会话持有用户表的锁，而另一个会话尝试锁定同一条记录，则会发生锁等待。
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 表。

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)

之前只有四个锁，但现在有六个了。
查看新增的两个锁，的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_IDLOCK_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_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 表的一个副作用是，除了查询之外，它还包含各种执行结果，例如错误详情和内存使用情况，因此定期检查它是很有必要的。

就是这样。