MySQL 8 锁查询

作者： /
内容纲要

概述

MySQL 8查询事务中涉及的锁，如何查询？

为什么要查看事务中加的锁？因为这个可以更好的理解事务的特性，更好的性能调优，更好的避免死锁，另外还有一个重要的原因就是：可以炫技，虽然有时候一不小心就会翻车^_^。

查询锁

performance_schema

performance_schema是MySQL提供的监控内部执行情况的性能模式。MySQL会采集收集数据然后把数据存储到performance_schema数据中。这个类似于应用的埋点。

performance_schema是基于内存存储的，不会持久化到硬盘，所以这些数据在服务器重启时会丢失。

MySQL中的performance_schema提供了锁信息，主要涉及的表时data_lock_waits和data_locks。

Performance_schema需要配置启用才会有，但是MySQL 8默认是启用的，如果你的performance_schema没有启用可以在my.cnf或者my.ini文件中配置后重启。


[mysqld]
performance_schema=ON

如何查询自己数据库实例是否启用performance_schema，我觉得有两种方式：

  1. show variables
  2. show databases

show variables


mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1 row in set (0.02 sec)

show databases


mysql> show databases like '%performance%';
+--------------------------+
| Database (%performance%) |
+--------------------------+
| performance_schema       |
+--------------------------+
1 row in set (0.00 sec)

要不要在生产环境启用performance_schema？

根据自己的情况而定吧。因为你启用了performance_schema就需要消耗一些系统资源，这些系统资源包括：CPU和内存。如果你觉得这些信息在生产环境上也很有价值，你可以启用它，同时还要保证你有足够的资源， 性能测试可以走几波。

如果你服务器资源有限，那就把它关闭掉。

在数据库中，当多个用户并发地访问数据库时，如果没有采用合适的机制来控制并发操作，就可能读取到错误的数据，或者导致数据的不一致。因此，数据库系统引入了事务锁的机制来确保并发事务的正确性。


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 | 140631749492736:1100:140631657736864   |               1009880 |        50 |      116 | trade_test    | trade_user  | NULL           | NULL              | NULL       |       140631657736864 | TABLE     | IX        | GRANTED     | NULL      |
| INNODB | 140631749492736:34:5:3:140631657733952 |               1009880 |        50 |      116 | trade_test    | trade_user  | NULL           | NULL              | idx_age    |       140631657733952 | RECORD    | X,GAP     | GRANTED     | 10, 2     |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
2 rows in set (0.00 sec)

在开始实验之前，我们来了解一下performance_schema.data_locks表提供的信息。

performance_schema.data_locks 表结构

performance_schema.data_locks表是从MySQL 5.7.3开始引入的，用于收集服务器中当前所有锁信息。MySQL 8.0扩展了这个特性，下面是该表的一些重要字段及其含义：

字段名 描述 可能的值
ENGINE 锁所在的存储引擎（如InnoDB） ‘INNODB’
ENGINE_LOCK_ID 锁的唯一标识 ‘140…’（数字序列）
ENGINE_TRANSACTION_ID 与锁相关联的事务ID 1009880（数字序列）
THREAD_ID 持有锁的线程ID 50（数字序列）
EVENT_ID 最近的事件ID 116（数字序列）
OBJECT_SCHEMA 锁定的数据库名 ‘trade_test’
OBJECT_NAME 锁定的表名 ‘trade_user’
INDEX_NAME 锁定的索引名，如果针对的是表级锁，则该值为NULL ‘PRIMARY’
OBJECT_INSTANCE_BEGIN 一个内存地址，指向具体的数据对象 140631657736864（数字序列）
LOCK_TYPE 锁的类型，区分记录级锁或表级锁 ‘RECORD’ 或 ‘TABLE’
LOCK_MODE 锁的模式，区分锁的级别 ‘S’, ‘X’, ‘IS’, ‘IX’等
LOCK_STATUS 表示锁的状态 ‘GRANTED’ 或 ‘WAITING’
LOCK_DATA 锁定的数据详细信息，比如具体锁定的记录 ’10, 2’（数字序列）或NULL

Lock Mode

performance_schema.data_locks表中LOCK_MODE字段表示锁的模式。对于InnoDB引擎，在MySQL中，该字段可以取以下可能的值，代表不同类型的锁：

  1. S – 共享锁 (Shared locks): 允许持有锁的事务读取一行，并且阻止其他事务获得相同数据行的排他锁。

  2. X – 排他锁 (Exclusive locks): 允许事务更新或删除一行，并且阻止其他事务获得相同数据行的共享锁或排他锁。

  3. IS – 意向共享锁 (Intention shared locks): 表示一个事务打算在表中的某些行上设置共享锁，是表级锁。

  4. IX – 意向排他锁 (Intention exclusive locks): 表示一个事务打算在表中的某些行上设置排他锁，也是表级锁。

  5. S_GAP – 共享间隙锁 (Shared gap locks): 保护一个范围内的空间，不允许其他事务在这个范围内插入新行，同时允许共享读取。

  6. X_GAP – 排他间隙锁 (Exclusive gap locks): 与共享间隙锁类似，但更严格，不允许其他事务在范围内插入新行和进行共享读取。

  7. S_NEXT_KEY – 共享next-key锁 (Shared next-key locks): 结合了共享锁和共享间隙锁。

  8. X_NEXT_KEY – 排他next-key锁 (Exclusive next-key locks): 结合了排他锁和排他间隙锁，防止其他事务插入到被锁定的范围。

  9. AUTO_INC – 自增锁 (Automatically increment lock): 用于自增字段，在插入时确保自增属性的唯一性和连续性。

  10. IX_GAP – 意向排他间隙锁 (Intention exclusive gap locks): 一般不单独使用，意向锁通常都和具体的记录锁或间隙锁结合。

  11. IS_GAP – 意向共享间隙锁 (Intention shared gap locks): 类似IX_GAP，但不常见。

其实就是MySQL中的各种锁，然后一条SQL可能回家多种锁，比如：你update语句where条件的字段是普通索引，那么可能就添加两种所，记录锁X锁和间隙锁GAP。

MySQL官方文档给出的锁模式：

file

我们业务SQL中涉及的各种锁

表DDL


CREATE TABLE trade_user (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    email LONGTEXT,
    age TINYINT UNSIGNED,
    PRIMARY KEY (id)
);

插入意向排他锁(IX)

我们向表中插入一条记录，记录如下：

事务A:


begin;
INSERT INTO trade_user(name, email, age) VALUES ('Bob', 'bob@email.com', 25);

我们来查询一下锁记录,我们可以看到使用了一个IX锁锁的类型是表锁，那我们再来启动一个事务做插入，看看是否会插入成功，因为IX锁是表锁嘛。
file

事务B：


begin;
INSERT INTO trade_user(name, email, age) VALUES ('Bob', 'bob@email.com', 25);

运行截图如下，我们看到依然成功，为什么？
file

因为插入意向锁只是意向，如果有冲突就会阻塞，如果没有冲突就不会阻塞。那什么样的冲突会阻塞？我们来看一个具体的例子：


-- 事务A
begin；
INSERT INTO trade_user(id, name, email, age) VALUES (1, 'Bob', 'bob@email.com', 25);

-- 事务B
begin;
INSERT INTO trade_user(id, name, email, age) VALUES (1, 'Bob', 'bob@email.com', 25);

我们让事务A先执行然后执行事务B，我们可以看到事务B被阻塞，因为主键冲突：
file

那这时候我们让事务A提交，那么事务B就报错了因为主键冲突：
file

上面是主键冲突的场景，我们来试试唯一键冲突的场景。

唯一键冲突


-- 添加唯一索引
ALTER TABLE trade_user ADD UNIQUE INDEX uni_name (name);

-- 事务A
begin;
INSERT INTO trade_user(name, email, age) VALUES ('Bob', 'bob@email.com', 25);

-- 事务B
begin;
INSERT INTO trade_user(name, email, age) VALUES ('Bob', 'bob@email.com', 25);

事务A先执行但不提交，事务B后执行被阻塞；事务A提交后事务B报错。

file

我们来试试普通索引

普通索引


-- 删除唯一索引
ALTER TABLE trade_user DROP INDEX uni_name;

-- 添加普通索引
ALTER TABLE trade_user ADD INDEX idx_name(name);

-- 事务A
begin;
INSERT INTO trade_user(name, email, age) VALUES ('Bob', 'bob@email.com', 25);

-- 事务B
begin;
INSERT INTO trade_user(name, email, age) VALUES ('Bob', 'bob@email.com', 25);

file
事务A和B都可以执行成功。

总结

InnoDB存储引擎在处理并发数据插入时，会有意向锁的概念，以支持更细粒度的锁控制。下面是关于意向排他锁（IX Lock）的总结：

  1. 意向排他锁（IX Lock）的定义和作用：
    意向排他锁是表级锁，它表明一个事务打算在表中的某些行上获得排他锁（行级X锁）。IX锁是兼容的，即在同一时间可以有多个事务都持有对同一张表的IX锁。

  2. 并发插入的行为：
    当事务A在表中插入一行数据时，它会自动获得该表的IX锁。如果事务B也尝试向同一表中插入数据，它同样会获得IX锁，因为IX锁之间是兼容的。这意味着IX锁不会阻止其他事务向表中插入数据，只要它们不违反任何唯一性约束。

  3. 主键冲突时的行为：
    当事务A试图插入一条具有特定主键的记录时，其会持有该记录的排他锁。如果事务B也尝试插入一条具有相同主键值的记录，则会被阻塞，直到事务A提交或者回滚。如果事务A提交，事务B尝试的操作由于主键冲突将会失败。

  4. 唯一键冲突时的行为：
    类似于主键，如果在表上有唯一键约束，当事务A插入一行触发唯一键约束时，它会持有该记录的排他锁。如果事务B尝试插入具有相同唯一键值的记录，它会等待事务A完成。如果事务A提交，造成唯一键约束确立，则事务B的操作将由于违反唯一键约束而失败。这种行为确保了数据的一致性和唯一性。

  5. 普通索引下的行为：
    当对表添加一个普通索引（不带唯一性约束）时，即使多个事务试图插入具有相同索引键值的记录，它们也不会被阻塞，因为普通索引不强制唯一性约束。

  6. 锁兼容性：
    意向锁之间是互相兼容的，IX锁允许其他IX锁，并发的存在。而IX锁与排他锁（X Lock）之间是不兼容的，确保了依据唯一性约束可以安全地修改数据。

相关文章

发表评论

您的电子邮箱地址不会被公开。 必填项已用 * 标注

滚动至顶部