MySQL 锁深入分析

Categories: 编程 2023-12-14

MySQL 锁

官方文档：https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

Lock Mode

下面后面优化

https://dev.mysql.com/blog-archive/innodb-data-locking-part-2-locks/

One thing that emerges here is a distinction between “whole table” and “part of the table” when we try to specify required access rights at the table level. You can imagine following combinations:

• X → I want to be the only one who has access to the whole table
• S → I want to be able assume that whole table is protected from modification
• IX → I intend to modify some part of the table
• IS → I intend to read some part of the table

中文：

• S 锁：共享锁，行级锁，已加 S 锁的行，不允许其它事务再加 X 锁，但可以继续加 S 锁
• X 锁：排它锁，行级锁，已加 S 锁的行，不允行其它事务再加 S 或 X 锁
• IS: 意向共享锁，表级锁，已加 S 锁的表，肯定会有 IS 锁，反过来，有 IS 锁的表，不一定会有 S 锁
• IX: 意向排它锁，表级锁，已加 X 锁的表，肯定会有 IX 锁，反过来，有 IX 锁的表，不一定会有 X 锁

锁与索引的关系

MySQL next-key lock 加锁范围总结

主键索引

1. 加锁时，会先给表添加意向锁，IX 或 IS；
2. 加锁是如果是多个范围，是分开加了多个锁，每个范围都有锁；（这个可以实践下 id < 20 的情况）
3. 主键等值查询，数据存在时，会对该主键索引的值加行锁 X,REC_NOT_GAP；
4. 主键等值查询，数据不存在时，会对查询条件主键值所在的间隙添加间隙锁 X,GAP；
5. 主键等值查询，范围查询时情况则比较复杂：
   1. 8.0.17 版本是前开后闭，而 8.0.18 版本及以后，修改为了 ` 前开后开 ` 区间；
   2. 临界 <= 查询时，8.0.17 会锁住下一个 next-key 的前开后闭区间，而 8.0.18 及以后版本，修复了这个 bug。

非主键唯一索引

1. 非主键唯一索引等值查询，数据存在，for update 是会在主键加锁的，而 for share 只有在走覆盖索引的情况下，会仅在自己索引上加锁；
2. 非主键索引等值查询，数据不存在，无论是否索引覆盖，相当于一个范围查询，仅仅会在非主键索引上加锁，加的还是间隙锁，前开后开区间；
3. 在非主键唯一索引范围查询时，不是覆盖索引的时候，会对相应的范围加前开后闭区间，并且如果存在数据，会对对应的主键加行锁；
4. 在非主键唯一索引范围查询时，如果是覆盖索引时，会对所有的后闭区间对应的主键，加行锁；
5. 在非主键唯一索引加锁时，还是存在 next-key 锁住下一个区间的 bug。

普通索引

1. 普通索引等值查询，因为不能确定唯一性，所以即使定位到记录，也是会向后查询，直到查询到不为该值的记录，从而锁定该值的区间；
2. 普通索引的锁也是加载该索引上的，如果涉及到存在的记录，会对该主键加行锁；
3. 普通索引的范围查询，同样出现 next-key 查询下一个区间的 bug。

普通字段

普通字段查询，会查询全表，这里锁的话就会锁住主键的所有区间。

手动验证

准备工作

1. 安装 docker，下载 MySQL 镜像。

docker pull mysql

1. 启动 MySQL 容器。

docker run --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql

1. 查看 MySQL 是否启动。

docker ps

查看已经启动并映射了本机端口号。

CONTAINER ID   IMAGE          COMMAND                   CREATED          STATUS          PORTS                               NAMES
df75447a15aa   mysql:latest   "docker-entrypoint.s…"   16 seconds ago   Up 15 seconds   0.0.0.0:3306->3306/tcp, 33060/tcp   yano-mysql

1. 创建数据

CREATE TABLE `people`
(
    `id` int(11) NOT NULL,
    `a`  int(11) DEFAULT NULL,
    `b`  int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `a` (`a`)
) ENGINE = InnoDB;

insert into people
values (0, 0, 0),
       (5, 5, 5),
       (10, 10, 10);

1. 确认版本

SELECT @@version;

结果是：

8.3.0

1. 确认隔离级别

查看当前会话隔离级别

select @@transaction_isolation;

查看系统当前隔离级别

select @@global.transaction_isolation;

结果是：

REPEATABLE-READ

当前查询的数据为：

如何查询锁

查询表 people 的锁状态：

select *
from performance_schema.data_locks
where OBJECT_NAME = 'people';

下图是一个示例（字段没有截全）：

加锁范围验证

case 1：查询非索引字段

表中字段 b 是 ` 非索引字段 `。会话 1 执行以下语句：

begin;

select *
from people
where b = 5 for update;

此时查出结果：

select *
from performance_schema.data_locks
where OBJECT_NAME = 'people';

为了查看方便，下面就只查询重要的列：

select ENGINE_TRANSACTION_ID, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
from performance_schema.data_locks
where OBJECT_NAME = 'people';

可以看到，会话 1 对整个表加了 IX 表锁 ，对 id = 0、id = 5、id = 10、id = 6 的记录加了 X 行锁。

此时在会话 2 中执行以下语句：

select *
from people
where b = 5 for update ;

会话 2 会 ` 阻塞 `。即使查询下面的语句也是会阻塞的：

select *
from people
where id = 5 for update ;

此时 data_locks 的数据为：

可以看到会话 2 的事务 ID = 1985，对 id = 0 的记录加了 X 行锁，但是是 WAITING 状态阻塞。

case 2：查询索引字段（行锁）

表中字段 a 是索引字段。会话 1 执行以下语句：

begin;

select *
from people
where id = 5 for update;

此时查出结果：

此时 data_locks 的数据为：

可以看到，会话 1 对整个表加了 IX 表锁，对 id = 5 的记录加了 X 行锁。

此时在会话 2 中执行以下语句：

select *
from people
where id = 5 for update;

此时 data_locks 的数据为：

会话 2 的事务 ID 是 1987，第二行是 WAITING 状态会阻塞。但是只要查询的 id 不是 5，就不会阻塞。例如下面的语句是不会阻塞的。

这是因为表中有 id = 5 这条记录，这里只是行锁。

select *
from people
where id = 10 for update;

case 3：查询索引字段（间隙锁）

会话 1 执行以下语句：

begin;

select *
from people
where id = 3 for update;

此时查出结果为空，因为没有 id = 3 的记录。

此时 data_locks 的数据为：

可以看到，会话 1 对整个表加了 IX 表锁，对 id = 3 的记录加了 X ` 间隙锁 `（不包含 id = 5 这行数据）。

此时在会话 2 中执行以下语句：

select *
from people
where id = 3 for update;

并没有阻塞。虽然会话 1 和会话 2 都会对 id = 3 的记录加上间隙锁，但是查询间隙锁并不会冲突，只有 “往这个间隙中插入记录” 才会冲突。

例如在会话 2 中执行以下语句：

insert into people
values (3, 3, 3);

insert into people
values (4, 4, 4);

会话 2 会阻塞，直到会话 1 提交事务或者回滚事务。此时 data_locks 的数据为：

但是会话 2 执行下面的语句不会阻塞：

insert into people
values (6, 6, 6);

可重复读下的幻读

一个灵魂问题， 可重复读隔离级别完全解决了幻读吗 ？

幻读定义

参考官方文档：15.7.4 Phantom Rows

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.

同一个查询在不同的时间，产生不同的结果集，就是所谓的幻读问题。例如，一个 SELECT 语句在第一次执行时，结果集为空；但是第二次执行时，返回了一行数据，这行数据在第一次执行时并不存在，这就是幻读。

` 快照读 ` 不会出现幻读

从本篇文章上面可以看出，可重复读隔离级别下，快照读几乎不会出现幻读。是通过 MVCC 实现的。

待详细补充

` 当前读 ` 不会出现幻读

本篇文章上面的例子，可以看出在正常的情况下， 行锁 和 ` 间隙锁 ` 已经解决幻读。

出现幻读的情况

还是 people 表，初始数据：

在会话 1 查询 id = 100 的数据：

begin;

select *
from people
where id = 100;

可以看到查询结果集为空，而且并没有加锁（下面查询语句为空）。

select ENGINE_TRANSACTION_ID, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
from performance_schema.data_locks
where OBJECT_NAME = 'people';

此时在会话 2 中插入 id = 100 的数据：

insert into people
values (100, 100, 100);

此时在会话 1 再次查询 id = 100 的数据：

select *
from people
where id = 100;

发现结果还是空的。但是此时在会话 1 中更新 id = 100 的数据：

update people
set a = 99 where id = 100;

之后再次查询 id = 100 的数据：

select *
from people
where id = 100;

能查到以下结果：

此时查看 data_locks 的数据：

发现会话 1 对 id = 100 的记录加了 X 行锁，但是这一行在会话 1 开始时并没有查询出来，出现了幻读。

出现幻读的原因，在于会话 1 在查询 id = 100 的时候，没有对 id = 100 的记录加锁，所以会话 2 可以插入 id = 100 的记录。但是会话 1 在更新 id = 100 的时候，对 id = 100 的记录加了 X 行锁，在会话 1 中就能够查询到 id = 100 的记录了。