面试官：你知道select * from users where id ＞ 5 for update时会发生什么吗？

前言

MySQL 数据库在多用户并发环境下，为了保证数据的一致性与完整性，采用了多种加锁机制。行锁是其中重要的一种，它可以确保数据库在多个事务并发执行时，能够精确控制对行数据的访问，从而避免数据冲突和不一致性问题。
同时对于，select * from users where id > 5 for update也是行锁加锁中多种情况中的一种，让我们通过本文用具体的例子和详细的步骤，介绍 MySQL 中 行锁 的加锁机制，帮助你全面理解在不同查询场景下，MySQL 是如何对数据加锁的。

一. 什么是行锁？

行锁是一种锁定特定行数据的机制，允许多个事务并发地操作不同的数据行。当一个事务在某一行上加锁时，其他事务无法对这行数据进行修改，直到锁被释放。

行锁在 InnoDB 存储引擎中最为常见，它能够支持高并发数据访问。行锁的粒度较细，相比于表锁，它允许其他事务访问表中的其他行数据，从而提高了数据库的并发性能。

二. 如何查看加锁情况？

在 MySQL 中，我们可以通过 performance_schema 查看当前事务加的锁。使用以下命令可以查看数据表的加锁情况：

SELECT * FROM performance_schema.data_locks\G;

该命令会返回当前加锁的行，包括锁类型、锁定的表、锁定的行等信息。通过这个命令，我们可以清楚地看到正在进行的锁操作。

三. 唯一索引加锁机制

3.1 等值查询

当使用 唯一索引 进行等值查询时，MySQL 会查找到确切的行数据，并对其加锁。具体的加锁行为如下：

例子：数据存在的情况

假设我们有一个名为 user 的表，表结构如下：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
);

INSERT INTO user (id, name) VALUES (1, 'user1'), (5, 'user5'), (10, 'user10');

查询语句：

SELECT * FROM user WHERE id = 5 FOR UPDATE;

加锁过程：

0. MySQL 会使用 唯一索引 id 来查找数据行，发现 id = 5 这一行数据。
1. 对该行数据加上 行锁，防止其他事务修改该行数据。

查询结果：

+----+--------+
| id | name   |
+----+--------+
|  5 | user5  |
+----+--------+

例子：数据不存在的情况

如果我们查询一个不存在的数据，例如：

SELECT * FROM user WHERE id = 7 FOR UPDATE;

加锁过程：

1. MySQL 会查找索引树，但由于 id = 7 不存在，MySQL 会将查询范围锁定在 (5, ∞)

2. 这时会向后去查找是否存在后一个的值

   0. 如果没有，则在 (5,∞) 加一个 间隙锁，防止其他事务插入 id = 7 这一行数据。
   1. 如果存在，如上数据中，则在(5,10)之间加上一个间隙锁

3.2 范围查询

当使用唯一索引进行 范围查询 时，MySQL 会对扫描的每一条记录加 Next-Key 锁，并根据查询条件的不同，可能退化为 间隙锁 或 记录锁。

例子：大于范围查询

查询语句：

SELECT * FROM users WHERE id > 5 FOR UPDATE;

加锁过程：

1. 查询结果中包含 id = 10。
2. (5,10]为 Next-key 锁。
3. 对 (10, +∞) 范围加 间隙锁。

例子：大于等于范围查询

查询语句：

SELECT * FROM users WHERE id >= 5 FOR UPDATE;

加锁过程：

0. 查询结果中包含 id = 5 和 id = 10。
1. 对 id = 5 加行锁。
2. (5,10]为 Next-key 锁。
3. 对 (10, +∞) 范围加 间隙锁。

例子：小于范围查询

查询语句：

SELECT * FROM users WHERE id < 6 FOR UPDATE;

加锁过程：

0. 查询结果中包含 id = 1 。
1. 对 (-∞, 1] 范围加 临键锁。
2. 对 (1, 5] 范围加 临键锁。
3. 对(5,10)范围加间隙锁。

例子：小于等于范围查询

查询语句：

SELECT * FROM users WHERE id <= 6 FOR UPDATE;

加锁过程：

0. 查询结果中包含 id = 1 和 id = 5。
1. 对 (-∞, 1] 范围加 临键锁。
2. 对 (1, 5] 范围加 临键锁。
3. 对(5,10)范围加间隙锁。

四. 非唯一索引加锁机制

4.1 等值查询

当我们使用 非唯一索引 进行等值查询时，MySQL 会同时加锁主键索引和非唯一索引（二级索引）。不过，只有满足查询条件的记录才会对主键索引加锁。

加锁规则

1. 该条数据存在：

   • 由于非唯一索引可能存在多个值相同的记录，因此，MySQL 会执行一个扫描过程。从找到的第一条记录开始，扫描直到不满足查询条件的记录为止。对于扫描到的符合条件的记录，会在二级索引上加 临键锁，并且在主键索引上加 行锁。

   a. 扫描到的所有符合条件的记录会加 临键锁（针对二级索引）， b. 扫描到第一条不符合条件的记录时，会对该范围加 间隙锁（防止其他事务插入符合条件的记录）。

2. 该数据不存在：

   • 当查询没有找到匹配的记录时，MySQL 会扫描到第一个不符合条件的二级索引记录。此时，二级索引上的 临键锁 会退化为 间隙锁，以防止其他事务插入符合查询条件的数据。由于没有符合查询条件的记录，主键索引不会被加锁。

例子：数据存在

假设我们有如下 user 表，其中 name 字段是二级索引：

CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    INDEX idx_name (name)
);

INSERT INTO user (name, age) VALUES ('小明', 20), ('小红', 22), ('小明', 25);

查询语句：

SELECT * FROM user WHERE name = '小明' FOR UPDATE;

加锁过程：

0. MySQL 会使用 非唯一索引 name 查找到符合条件的两条记录 id = 1 和 id = 3。
1. 对这两条记录的二级索引加 临键锁，同时对主键索引加 行锁。

加锁情况：

• 对 name = '小明' 的记录加 临键锁（针对二级索引）。
• 对 id = 1 和 id = 3 的主键索引加 行锁。

例子：数据不存在

假设查询一个不存在的记录：

SELECT * FROM user WHERE name = '小李' FOR UPDATE;

加锁过程：

0. MySQL 会使用 非唯一索引 查找，但未找到匹配的数据。
1. 对第一个不满足条件的记录（如 name = '小红'）加 间隙锁，防止其他事务插入符合查询条件的数据。

4.2 范围查询

在使用 非唯一索引 进行 范围查询 时，MySQL 会对扫描的每一条记录加 Next-Key 锁。此时，Next-Key 锁 不会退化为间隙锁或记录锁。对于范围查询，Next-Key 锁 是一个组合锁，涵盖了二级索引上的锁和主键索引上的锁。

例子：范围查询

假设数据库的数据如下：

+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
|  5 | 张三       |  21 |
| 10 | 李四       |  22 |
| 20 | 王五       |  39 |
+----+-----------+-----+

查询语句：

SELECT * FROM user WHERE age >= 22 FOR UPDATE;

加锁过程：

0. 查询结果包括 id = 10 和 id = 20。
1. 对 (21, 22] 范围加 临键锁。
2. 对 (22, 39] 范围加 临键锁。
3. 对 [39, +∞) 范围加 临键锁。

五. 不使用索引查询

当查询语句没有使用索引时，MySQL 会对整个表加 表级锁，而不是行级锁。表级锁会导致全表的访问都被阻塞，从而影响系统的并发性能。因此，应该尽量避免没有索引的查询。

举例来说，如：(加入users表只有id为主键的索引)

update users set name = '赵六' where name = '李四'

由于不能确定某一个唯一值，则这个sql对整个表加锁。

六. 总结

MySQL 的加锁机制是为了保证数据的并发安全。在不同的查询条件下，MySQL 会自动选择合适的锁类型，以实现最优的性能和数据一致性。在实际开发中，我们应尽量避免全表扫描，合理使用索引，避免不必要的表级锁和行级锁冲突，从而提高系统的并发能力和稳定性。

谈谈个人理解，对于行锁加锁时候，临键锁和间隙锁都是围绕解决幻读这种问题来解决的，知道了这个点，其实再回顾本篇文章后，对于上述加锁的案例，就可以得到更深入的理解了。

最后，本文通过理解不同类型的锁以及它们的使用场景，能够更好地优化查询语句、设计数据库架构，避免性能瓶颈和死锁问题，从而提升系统的可靠性和效率。