MySQL—锁分析-每日运维

0. 简介

锁，是在计算机执行多线程（或协程）并发访问时，用于对同一共享资源的一致性同步机制。MySQL中的锁是在存储引擎中实现的，保证了数据访问的一致性。

1. MySQL中的锁

在InnoDB中，锁分为全局锁，表级锁和行级锁。

1.1 全局锁

全局锁主要用于全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

加全局锁：

flush tables with read lock

这时候，意味着整个数据库都处于只读状态。不过在InnoDB引擎下，因为支持MVCC，所以在使用 mysqldump 时加上 –single-transaction 参数的时候，就会在备份数据库之前先开启事务。在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。

释放全局锁，执行这条命令：

unlock tables

当然，当会话断开了，全局锁会被自动释放。

1.2 表级锁

表锁

表锁可以加读或者写锁：

//表级别的共享锁，也就是读锁；
lock tables t_student read;

//表级别的独占锁，也就是写锁；
lock tables t_stuent write;

需要注意的是，表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。

要释放表锁，可以使用下面这条命令，会释放当前会话的所有表锁：

unlock tables

另外，当会话退出后，也会释放所有表锁。
不过尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能，InnoDB 牛逼的地方在于实现了颗粒度更细的行级锁。

元数据锁（MDL）

我们不需要显示的使用 MDL，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL：

对一张表进行 CRUD 操作时，加的是 MDL 读锁；
对一张表做结构变更操作的时候，加的是 MDL 写锁；

意向锁

在InnoDB中：

对行级数据加共享锁之前，需要先在表级别上加一个意向共享锁；
对行级数据加独占锁之前，需要现在表级别上加一个意向独占锁；

也就是，当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。

而普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。

不过，select 也是可以对记录加共享锁和独占锁的，具体方式如下：

//先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... lock in share mode;

//先表上加上意向独占锁，然后对读取的记录加独占锁
select ... for update;

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables ... read）和独占表锁（lock tables ... write）发生冲突。

因为表锁和行级锁的读写、写写互斥，意向锁的目的是使得加表级锁时无需遍历表中的表锁，只需判断意向锁。

AUTO-INC 锁

参考AUTO-INC 锁。

1.3 行级锁

InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。

行级锁有共享锁(S) 和独占锁(X) 之分，从类型上分为：

Record Lock：记录锁，也就是仅仅把一条记录锁住；
Gap Lock：间隙锁，锁定一个范围，不包括记录本身，是一个开区间；
Next-Key Lock：Record Lock + Gap Lock，锁定一个范围，并且锁定记录本身，是左开右闭区间。

Record Lock

Record Lock称为记录锁，锁住的是一条记录，且记录锁有S和X之分：

当一个事务对一条记录加了 S 型记录锁后，其他事务也可以继续对该记录加 S 型记录锁（S 型与 S 锁兼容），但是不可以对该记录加 X 型记录锁（S 型与 X 锁不兼容）;
当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁（S 型与 X 锁不兼容），也不可以对该记录加 X 型记录锁（X 型与 X 锁不兼容）。

Gap Lock

Gap Lock称为间隙锁，只存在于可重复度隔离级别，目的是为了解决可重复读隔离级别下的幻读。

假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。

间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的。

Next-Key Lock

Next-Key Lock称为临键锁，是Record Lock + Gap Lock的组合，锁定一个范围，并且锁定记录本身。

假设，表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改 id = 5 这条记录。

所以，next-key lock 即能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。

next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。

比如，一个事务持有了范围为 (1, 10] 的 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，就会被阻塞。

虽然相同范围的间隙锁是多个事务相互兼容的，但对于记录锁，我们是要考虑 X 型与 S 型关系，X 型的记录锁与 X 型的记录锁是冲突的。

插入意向锁

一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（next-key lock 也包含间隙锁）。

如果有的话，插入操作会发生阻塞，直到持有间隙锁的事务提交为止。但是在等待期间，会生成一个插入意向锁，表明有事务想要在区间内插入新记录，但是现在处于等待状态。

隐式锁

在内存中生成锁结构并不是没有成本的，所以一般情形下执行INSERT语句不会生成锁结构，假如说此时有其他事务执行SELECT ... FOR UPDATE之类的语句，如果对INSERT事务不加锁的话，很可能出现脏读。

这个时候，InnoDB引擎：

对于聚簇索引记录而言，隐藏的trx_id列记录着事务id，当其他事务想获取这条记录的锁时，会判断该记录事务是否是当前的活跃事务，不是的话可以正常读取，是的话就帮助该事务创建一个锁结构，iswating属性为false；然后给自己创建一把锁，iswating属性为true，进入等待状态。

如果是二级索引，先判断页的Page Header部分的PAGE_MAX_TRX_ID属性值小于当前活跃的事务id，说明该页面的修改事务都已经提交；都则定位到对应的二级索引，再执行步骤1。

隐式锁起到了延迟生成锁结构的用处。但是这对用户是透明的。

2. MySQL加锁分析

行级锁加锁规则比较复杂，不同的场景，加锁的形式是不同的。

加锁的对象是索引，加锁的基本单位是 next-key lock，它是由记录锁和间隙锁组合而成的，next-key lock 是前开后闭区间，而间隙锁是前开后开区间。

但是，next-key lock 在一些场景下会退化成记录锁或间隙锁。

那到底是什么场景呢？总结一句，在能使用记录锁或者间隙锁就能避免幻读现象的场景下， next-key lock 就会退化成记录锁或间隙锁。

我们使用下表进行实验说明：

CREATE TABLE `user` (
	`id` BIGINT NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(30) COLLATE utf8mb4_unicode_ci NOT NULL,
	`age` INT NOT NULL,
	PRIMARY KEY (`id`),
	KEY `index_age` USING BTREE (`age`)
) ENGINE = INNODB CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

然后插入以下数据：

INSERT INTO `user` (id, `name`, age)
VALUES (1, '路飞', 19),
	(5, '索隆', 21),
	(10, '山治', 22),
	(15, '乌索普', 20),
	(20, '香克斯', 39);

SELECT * FROM `user`;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
|  1 | 路飞      |  19 |
|  5 | 索隆      |  21 |
| 10 | 山治      |  22 |
| 15 | 乌索普    |  20 |
| 20 | 香克斯    |  39 |
+----+-----------+-----+
5 rows in set (0.00 sec)

2.1 唯一索引等值查询

记录存在的情况

首先，事务A使用当前读读取id=1的数据：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id = 1 for update;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | 路飞   |  19 |
+----+--------+-----+
1 row in set (0.00 sec)

然后事务B更新当前记录，被阻塞：

mysql> update user set age = 20 where id = 1;

这时候查看锁的使用情况：

mysql> select * from information_schema.innodb_locks;
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table    | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| 1084099:256:3:2 | 1084099     | X         | RECORD    | `test`.`user` | PRIMARY    |        256 |         3 |        2 | 1         |
| 1084096:256:3:2 | 1084096     | X         | RECORD    | `test`.`user` | PRIMARY    |        256 |         3 |        2 | 1         |
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

可以发现，此时的lock_type是RECORD是记录锁；lock_mode是X表示是排他锁；lock_index是PRIMARY，表示锁的对象是主键索引；且锁住的记录是lock_data = 1，表示锁住的是第一条记录。

为什么唯一索引等值查询并且查询记录存在的场景下，该记录的索引中的 next-key lock 会退化成记录锁？

原因就是在唯一索引等值查询并且查询记录存在的场景下，仅靠记录锁也能避免幻读的问题。

记录不存在的情况

假设事务A执行了这条等值查询语句，且记录并不在表中：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id = 2 for update;
Empty set (0.03 sec)

这时候，事务B执行以下插入：

mysql> insert user (id, name, age) values (3, "iguochan", 18);

可以发现事务B被阻塞，这时候查看锁信息如下：

mysql> select * from information_schema.innodb_locks;
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table    | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| 1084104:256:3:3 | 1084104     | X,GAP     | RECORD    | `test`.`user` | PRIMARY    |        256 |         3 |        3 | 5         |
| 1084102:256:3:3 | 1084102     | X,GAP     | RECORD    | `test`.`user` | PRIMARY    |        256 |         3 |        3 | 5         |
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.01 sec)

可以发现，lock_mode是X,GAP，表示是排他间隙锁；lock_data = 5表示间隙锁范围是(1, 5)，即从前面一个记录到lock_data这条记录之间。

为什么唯一索引等值查询并且查询记录「不存在」的场景下，在索引树找到第一条大于该查询记录的记录后，要将该记录的索引中的 next-key lock 会退化成「间隙锁」？

原因就是在唯一索引等值查询并且查询记录不存在的场景下，仅靠间隙锁就能避免幻读的问题。

为什么 id = 5 记录上的主键索引的锁不可以是 next-key lock？如果是 next-key lock，就意味着其他事务无法删除 id = 5 这条记录，但是这次的案例是查询 id = 2 的记录，只要保证前后两次查询 id = 2 的结果集相同，就能避免幻读的问题了，所以即使 id =5 被删除，也不会有什么影响，那就没必须加 next-key lock，因此只需要在 id = 5 加间隙锁，避免其他事务插入 id = 2 的新记录就行了。
为什么不可以针对不存在的记录加记录锁？锁是加在索引上的，而这个场景下查询的记录是不存在的，自然就没办法锁住这条不存在的记录。

2.2 唯一索引范围查询

“大于”的范围查询

事务A执行以下语句：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id > 16 for update;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
| 20 | 香克斯    |  39 |
+----+-----------+-----+
1 row in set (0.00 sec)

假设事务B希望向15~20之间插入数据：

mysql> insert user (id, name, age) values (19, "iguochan", 18);

此时查看锁情形是：

mysql> select * from information_schema.innodb_locks;
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table    | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| 1084129:256:3:6 | 1084129     | X,GAP     | RECORD    | `test`.`user` | PRIMARY    |        256 |         3 |        6 | 20        |
| 1084124:256:3:6 | 1084124     | X         | RECORD    | `test`.`user` | PRIMARY    |        256 |         3 |        6 | 20        |
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

此时，发现事务A（也就是1084124）加了X锁，从分析来看应该是Next-Key Lock，应该锁住了范围，为了验证，我们尝试修改id=20的值。

假设事务C执行如下：

mysql> update `user` set age = 20 where id = 20;

发现也被阻塞，然后查看锁分析是：

mysql> select * from information_schema.innodb_locks;
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table    | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| 1084130:256:3:6 | 1084130     | X         | RECORD    | `test`.`user` | PRIMARY    |        256 |         3 |        6 | 20        |
| 1084124:256:3:6 | 1084124     | X         | RECORD    | `test`.`user` | PRIMARY    |        256 |         3 |        6 | 20        |
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

即给20这个记录加上了Record Lock，结合以上分析，可以认为在(15, 20]加上了Next-Key Lock（即Record Lock + Gap Lock）。

然后我们再尝试向20以上的空隙插入一条：

mysql> insert user (id, name, age) values (22, "iguochan", 18);

再分析加锁：

mysql> select * from information_schema.innodb_locks;
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+------------------------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table    | lock_index | lock_space | lock_page | lock_rec | lock_data              |
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+------------------------+
| 1084131:256:3:1 | 1084131     | X         | RECORD    | `test`.`user` | PRIMARY    |        256 |         3 |        1 | supremum pseudo-record |
| 1084124:256:3:1 | 1084124     | X         | RECORD    | `test`.`user` | PRIMARY    |        256 |         3 |        1 | supremum pseudo-record |
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+------------------------+
2 rows in set, 1 warning (0.00 sec)

可以发现，加了(20, +∞]的Next-Key Lock。

“大于等于”范围查询

我们修改一下事务A如下：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id >= 15 for update;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
| 20 | 香克斯    |  39 |
+----+-----------+-----+
1 row in set (0.00 sec)

这时候，除了以上(15, 20]的Next-Key Lock、(20, +∞)的Next-Key Lock外，还包含id=15的Record Lock。

比如，事务B此时执行以下语句：

mysql> update `user` set age = 20 where id = 15;

锁分析如下：

mysql> select * from information_schema.innodb_locks;
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table    | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| 1084138:256:3:5 | 1084138     | X         | RECORD    | `test`.`user` | PRIMARY    |        256 |         3 |        5 | 15        |
| 1084133:256:3:5 | 1084133     | X         | RECORD    | `test`.`user` | PRIMARY    |        256 |         3 |        5 | 15        |
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

所以总结下来就是，主键的范围查询时：

id > a：在所在值间隙形成(mina, b]、(b, c]、...、(z, +∞]的Next-Key Lock，mina表示a所在间隙最左边的值，比如以上例子，mina = 15;
id >= a：
- 当id = a这条记录不存在时，形成(mina, b]、(b, c]、...、(z, +∞]的Next-Key Lock；
- 当id = a这条记录存在时，形成id = a的Record Lock和(a, b]、(b, c]、...、(z, +∞]的Next-Key Lock；

其实很简单，就是从所在间隙往后推，记录包含在内的上Record Lock，记录不在的上Gap Lock，所在值不在记录中，则需要往前推到上一条记录。

小于或者小于等于

其实明白了以上的逻辑，我们很好分析小于的逻辑。

主键的范围查询时：

id < z：
- 若z的记录不存在，在所在值间隙形成(-∞, a]、(a, b]、...、(x, y]的Next-Key Lock，以及形成(y, maxz)的Gap Lock，maxz表示z所在间隙往右存在的记录值;
- 若z的记录存在，则形成(-∞, a]、(a, b]、...、(x, y]的Next-Key Lock，以及形成(y, z)的Gap Lock

id  begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where age = 25 for update; Empty set (0.00 sec)

此时事务B想插入一条数据：

mysql> insert user (id, name, age) values (16, "iguochan", 27);

这时候进行加锁分析如下：

mysql> select * from information_schema.innodb_locks;
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table    | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| 1084151:256:4:6 | 1084151     | X,GAP     | RECORD    | `test`.`user` | index_age  |        256 |         4 |        6 | 39, 20    |
| 1084149:256:4:6 | 1084149     | X,GAP     | RECORD    | `test`.`user` | index_age  |        256 |         4 |        6 | 39, 20    |
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

可以看到，给二级索引index_age加上了(22, 39)的Gap Lock。此时，如果其他事务插入的年龄在23~38之间的话，都会被阻塞，比如事务B。

但是对于年龄22和39这两个特殊的点，我们需要特殊讨论，这里我们给出一个表示，即(age-id, age-id)来表示Gap Lock的范围，那么事务A锁住的区间就是(22-10, 39-20)，这也就是以上表中lock_data为39, 20的原因。

那也就是说，会有以下场景：

-- 成功
mysql> insert user (id, name, age) values (9, "iguochan", 22);
Query OK, 1 row affected (0.00 sec)

-- 阻塞
mysql> insert user (id, name, age) values (11, "iguochan", 22);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

-- 阻塞
mysql> insert user (id, name, age) values (19, "iguochan", 39);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

-- 成功
mysql> insert user (id, name, age) values (21, "iguochan", 39);
Query OK, 1 row affected (0.00 sec)

其实也很好理解，因为二级索引的索引对象就是一级索引。

记录存在的情况

我们先看下此时的表（有些记录的年龄被修改，怕无法同步，我们这里看一眼）：

mysql> select * from `user`;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
|  1 | 路飞      |  19 |
|  5 | 索隆      |  20 |
| 10 | 山治      |  22 |
| 15 | 乌索普    |  20 |
| 20 | 香克斯    |  39 |
+----+-----------+-----+
5 rows in set (0.00 sec)

假设事务A对非唯一索引age进行了等值查询，且表中存在age = 22的记录：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where age = 22 for update;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
| 10 | 山治   |  22 |
+----+--------+-----+
1 row in set (0.00 sec)

事务A的加锁如下：

由于不是唯一索引，所以肯定存在值相同的记录，于是非唯一索引等值查询的过程是一个扫描的过程，最开始要找的第一行是 age = 22，于是对该二级索引记录加上范围为 (20-15, 22] 的 next-key 锁。同时，因为 age = 22 符合查询条件，于是对 age = 22 的记录的主键索引加上记录锁，即对 id = 10 这一行加记录锁。
接着继续扫描，扫描到的第二行是 age = 39，该记录是第一个不符合条件的二级索引记录，所以该二级索引的 next-key 锁会退化成间隙锁，范围是 (22-10, 39-20)。

接下来我们就验证以上三把锁：

(20-15, 22-10] 的 next-key 锁

事务B执行以下语句：

-- 成功
mysql> insert user (id, name, age) values (14, "iguochan", 20);
Query OK, 1 row affected (0.00 sec)

-- 阻塞
mysql> insert user (id, name, age) values (16, "iguochan", 20);

阻塞时的锁分析：

mysql> select * from information_schema.innodb_locks;
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table    | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| 1084177:256:4:4 | 1084177     | X,GAP     | RECORD    | `test`.`user` | index_age  |        256 |         4 |        4 | 22, 10    |
| 1084170:256:4:4 | 1084170     | X         | RECORD    | `test`.`user` | index_age  |        256 |         4 |        4 | 22, 10    |
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

(22-10, 39-20)的 Gap Lock

事务C执行以下语句：

-- 阻塞
mysql> insert user (id, name, age) values (19, "iguochan", 39);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

-- 成功
mysql> insert user (id, name, age) values (21, "iguochan", 39);
Query OK, 1 row affected (0.00 sec)

阻塞时的锁分析如下：

mysql> select * from information_schema.innodb_locks;
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table    | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| 1084180:256:4:6 | 1084180     | X,GAP     | RECORD    | `test`.`user` | index_age  |        256 |         4 |        6 | 39, 20    |
| 1084170:256:4:6 | 1084170     | X,GAP     | RECORD    | `test`.`user` | index_age  |        256 |         4 |        6 | 39, 20    |
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

为什么需要这把间隙锁呢？那是因为(20-15, 22-10]的Next-Key Lock无法完全锁住age = 22这行。

id = 10 的主键锁

事务D执行以下语句：

mysql> update `user` set age = 20 where id = 10;

阻塞时分析锁：

mysql> select * from information_schema.innodb_locks;
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table    | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| 1084181:256:3:4 | 1084181     | X         | RECORD    | `test`.`user` | PRIMARY    |        256 |         3 |        4 | 10        |
| 1084170:256:3:4 | 1084170     | X         | RECORD    | `test`.`user` | PRIMARY    |        256 |         3 |        4 | 10        |
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

可以发现，对PRIMARY做了记录锁。

2.4 非唯一索引范围查询

假设事务A执行了以下搜索：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where age >= 22  for update;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
| 10 | 山治      |  22 |
| 20 | 香克斯    |  39 |
+----+-----------+-----+
2 rows in set (0.01 sec)

那么锁会如下图所示：

也就是非唯一索引不会退化成记录锁，原因就是非唯一索引不可能唯一锁定记录，所以无法退化。同样的，所有满足的聚簇索引上加记录锁。

2.5 没有加索引的查询

如果锁定读查询语句，没有使用索引列作为查询条件，或者查询语句没有走索引查询，导致扫描是全表扫描。那么，每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。

不只是锁定读查询语句不加索引才会导致这种情况，update 和 delete 语句如果查询条件不加索引，那么由于扫描的方式是全表扫描，于是就会对每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表。

因此，在线上在执行 update、delete、select ... for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题。

3. 总结

其实前面分析了这么多，我们不要死记硬背这些加锁过程，只需要记住加锁过程和索引结构以及索引的搜索过程息息相关，而锁的设计就是为了让该搜索不会出现幻读。

4. 参考文档

MySQL 是怎么加锁的？

《MySQL是怎样运行的》从根儿上理解 MySQL

MySQL行锁表锁和语句加锁分析

MySQL—锁分析

0. 简介

1. MySQL中的锁

1.1 全局锁

1.2 表级锁

表锁

元数据锁（MDL）

意向锁

AUTO-INC 锁

1.3 行级锁

Record Lock

Gap Lock

Next-Key Lock

插入意向锁

隐式锁

2. MySQL加锁分析

2.1 唯一索引等值查询

记录存在的情况

记录不存在的情况

2.2 唯一索引范围查询

“大于”的范围查询

“大于等于”范围查询

小于或者小于等于

记录存在的情况

(20-15, 22-10] 的 next-key 锁

(22-10, 39-20)的 Gap Lock

id = 10 的主键锁

2.4 非唯一索引范围查询

2.5 没有加索引的查询

3. 总结

4. 参考文档

相关文章

发布评论取消回复

贤蛋大眼萌

Oracle如何使用授予和撤销权限的语法和示例

产品推荐：7月受欢迎AI容器镜像来了，有Qwen系列大模型镜像

i.MX8MP开发板AI项目实操

MySQL—锁分析

0. 简介

1. MySQL中的锁

1.1 全局锁

1.2 表级锁

表锁

元数据锁（MDL）

意向锁

AUTO-INC 锁

1.3 行级锁

Record Lock

Gap Lock

Next-Key Lock

插入意向锁

隐式锁

2. MySQL加锁分析

2.1 唯一索引等值查询

记录存在的情况

记录不存在的情况

2.2 唯一索引范围查询

“大于”的范围查询

“大于等于”范围查询

小于或者小于等于

记录存在的情况

(20-15, 22-10] 的 next-key 锁

(22-10, 39-20)的 Gap Lock

id = 10 的主键锁

2.4 非唯一索引范围查询

2.5 没有加索引的查询

3. 总结

4. 参考文档

相关文章

发布评论 取消回复

贤蛋大眼萌

Oracle如何使用授予和撤销权限的语法和示例

产品推荐：7月受欢迎AI容器镜像来了，有Qwen系列大模型镜像

i.MX8MP开发板AI项目实操

发布评论取消回复