MySQL是怎么加锁的

发布于2024-10-09

字数: 6440

MySQL

锁

MySQL是怎么加锁的

这篇文章主要是讲解MySQL的Innodb引擎如何加行级锁，什么情况下加记录锁，什么时候加间隙锁，什么时候加临键锁。(关于MySQL有哪些锁可以看这篇)

什么SQL会加行级锁

在非串行化隔离级别下，普通的select语句并不会加锁，因为属于快照读，通过MVCC控制自己可见数据版本，如果需要在查询语句加锁，可以参照下面的SQL。

sql 复制代码

# 对查询记录添加共享锁(S锁)
select ... lock in share mode;
# 对查询记录添加独占锁(X锁)
select ... for update;

这两条SQL必须放在事务中执行，因为锁会随着事务的提交而释放。

update语句和delete语句都会加行级锁，并且是独占锁。

sql 复制代码

update table_name ... where id = 1;
delete from table_name where id = 1;

MySQL是如何加行级锁的

MySQL的加锁遵循下面几个原则：

加锁的对象是索引，加锁的基本单位是next-key lock，next-key lock是左闭右开区间；
在查询过程中访问到的二级索引列或者非索引列锁，最终都要回溯到主键上加锁；
某些情况上next-key lock会退化为间隙锁或者记录锁，间隙锁是左开右开区间；
唯一索引的范围查询会扫描到不满足条件的第一条记录为止；

以下面表结构来测试MySQL加锁规则，MySQL版本是8.0.26，隔离级别是可重复读。

sql 复制代码

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `code` varchar(18) DEFAULT NULL COMMENT '用户编码，唯一确认',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_code` (`code`),
  KEY `idx_name` (`name`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

插入记录

sql 复制代码

INSERT INTO user (id, name, age, code) VALUES (1, 'Emma', 20, '11010120000728520X');
INSERT INTO user (id, name, age, code) VALUES (5, 'Sophia', 41, '110101200007285701');
INSERT INTO user (id, name, age, code) VALUES (10, 'Liam', 36, '110101200007280601');
INSERT INTO user (id, name, age, code) VALUES (15, 'Noah', 25, '110101200007284100');
INSERT INTO user (id, name, age, code) VALUES (20, 'Jackson', 15, '110101200007281807');

唯一索引等值查询

当使用唯一索引做等值查询的时候，如果查询的记录不存在，加的锁也不一样，具体规则如下：

当查询的记录存在时，该记录的索引上的临键锁会退化成记录锁
当查询的记录不存在的时候，该记录的索引上的临键锁会退化成间隙锁

这里所说的查询的记录存在并不单指select语句，对于update和delete语句也是一样的，对于select语句，「查询的记录存在」指的是表中是否存在这条记录，对于update和delete语句来说，指的是执行语句影响的行数是否大于0。贴一下官方文档

For locking reads (SELECT with FOR UPDATE or FOR SHARE), UPDATE, and DELETE statements, the locks that are taken depend on whether the statement uses a unique index with a unique search condition or a range-type search condition.

For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.

For other search conditions, and for non-unique indexes, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range. For information about gap locks and next-key locks, see Section 17.7.1, “InnoDB Locking”.

唯一索引并不单指聚簇索引，包括二级索引的unique index，如果使用的是二级索引的唯一索引，则会根据二级索引在聚簇索引对应的行记录加锁。贴一下官方文档中的一段话：

If a secondary index is used in a search and the index record locks to be set are exclusive, InnoDB also retrieves the corresponding clustered index records and sets locks on them.

下面以几个例子来验证说明：

记录存在

sql 复制代码

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

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

这条SQL会给id = 1的记录加上X型的记录锁，此时如果有其他事务对这行记录进行删除或者修改操作则会被阻塞。MySQL官方提供了分析锁信息的方法，可以通过下面的SQL来分析锁情况。

sql 复制代码

SELECT * FROM performance_schema.data_locks\G;

以前面的SQL为例子，看看锁信息：

sql 复制代码

mysql> SELECT * FROM performance_schema.data_locks\G;
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 1674918404456:1742:1674888668264
ENGINE_TRANSACTION_ID: 1102914
            THREAD_ID: 53
             EVENT_ID: 7
        OBJECT_SCHEMA: learn
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 1674888668264
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 1674918404456:685:4:2:1674888665480
ENGINE_TRANSACTION_ID: 1102914
            THREAD_ID: 53
             EVENT_ID: 7
        OBJECT_SCHEMA: learn
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 1674888665480
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 1
2 rows in set (0.01 sec)

ERROR:
No query specified

上述输出信息我们只需要关注以下几个信息即可：

INDEX_NAME：索引名，如果没有使用到索引则为null，但是在Innodb下index_name不能为空；
1.row是表锁相关信息，2.row是行锁相关信息；
LOCK_TYPE：锁类型，具体的值与存储引擎相关
LOCK_MODE：怎么请求锁，具体的值与存储引擎相关。对于InnoDB，允许的值是S[,GAP]、X[,GAP]、IS[,GAP]、IX[,GAP]、AUTO_INC和UNKNOWN。除AUTO_INC和UNKNOWN以外的锁定模式表示间隙锁定（如果值不为空）；
LOCK_STATUS：锁请求状态，具体的值与存储引擎相关。对于InnoDB，允许的值是 GRANTED (已持有锁) and WAITING (锁正在等待)；
LOCK_DATA：锁定数据，具体的值取决于存储引擎。对于InnoDB，如果LOCK_TYPE为RECORD，则显示一个值，否则该值为NULL。对于主键索引上的锁，值为锁定记录的主键值。如果是二级索引值，则需要在对应主键索引加锁。如果没有主键，LOCK_DATA会显示表中存在的唯一索引的键值或隐藏字段中的row_id。

更多具体的信息可以看这个链接MySQL :: MySQL 8.0 Reference Manual :: 29.12.13.1 The data_locks Table。

我们回到锁信息的输入日志，从中可以看出主要加了两个锁，一个是意向独占的表级锁，表明有事务要锁定表中的某一行；一个是行级的独占记录锁。

我们重点关注行级锁的信息，即2.row的部分，LOCK_TYPE为RECORD表示这是一个行级锁，LOCK_MODE的取值含义如下：

LOCK_MODE为X表示临键锁；
LOCK_MODE为X, REC_NOT_GAP表示记录锁；
LOCK_MODE为X, GAP表示间隙锁；

所以此时这条SQL锁住的是id = 1这条记录，其他事务无法对该记录进行修改和删除操作。从INDEX_NAME: PRIMARY也可以验证前面所说的加锁的对象是索引。

Q：为什么当记录存在时临键锁会退化为记录锁？

A：因为在唯一索引等值查询且记录存在时仅靠记录锁就可以防止幻读，幻读的定义是同一个SQL前后执行的结果集不一样就认为出现了幻读。因此给id = 1这条记录上锁就会导致其他事务无法插入一条id = 1的记录或者删除这条记录，这样对应这个事务来说只要自己不删除这条记录，就不会出现前后执行结果集不一致的情况，从而避免了幻读。

这个例子是使用了聚簇索引，使用二级索引的唯一索引也是一样的，道理和主键索引一样，感兴趣的朋友可以自行执行下面SQL分析。

sql 复制代码

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

mysql> select * from user where code = '11010120000728520X' for update;
+----+------+------+--------------------+
| id | name | age  | code               |
+----+------+------+--------------------+
|  1 | Emma |   20 | 11010120000728520X |
+----+------+------+--------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM performance_schema.data_locks\G;
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 1674918404456:1742:1674888668264
ENGINE_TRANSACTION_ID: 1103072
            THREAD_ID: 53
             EVENT_ID: 28
        OBJECT_SCHEMA: learn
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 1674888668264
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 1674918404456:685:7:5:1674888665480
ENGINE_TRANSACTION_ID: 1103072
            THREAD_ID: 53
             EVENT_ID: 28
        OBJECT_SCHEMA: learn
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_code
OBJECT_INSTANCE_BEGIN: 1674888665480
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: '11010120000728520X', 1
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 1674918404456:685:4:16:1674888665824
ENGINE_TRANSACTION_ID: 1103072
            THREAD_ID: 53
             EVENT_ID: 28
        OBJECT_SCHEMA: learn
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 1674888665824
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 1
3 rows in set (0.00 sec)

ERROR:
No query specified

记录不存在

假设事务A执行了下面的语句：

sql 复制代码

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

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

mysql> SELECT * FROM performance_schema.data_locks\G;
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 1674918404456:1742:1674888668264
ENGINE_TRANSACTION_ID: 1103073
            THREAD_ID: 53
             EVENT_ID: 33
        OBJECT_SCHEMA: learn
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 1674888668264
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 1674918404456:685:4:18:1674888665480
ENGINE_TRANSACTION_ID: 1103073
            THREAD_ID: 53
             EVENT_ID: 33
        OBJECT_SCHEMA: learn
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 1674888665480
*************************** 重点观察下面四个锁信息项 ***************************
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 5
2 rows in set (0.00 sec)

ERROR:
No query specified

从输出信息中可以看出主要加了两个锁，一个是意向独占的表级锁，表明有事务要锁定表中的某一行；一个是行级的独占间隙锁。此时事务A会在id = 5这条记录的聚簇索引上加间隙锁，范围为(1, 5)。

PixPin_2024-09-30_15-36-42 !]

这个时候如果插入id = 2 id = 3 id = 4的记录会被阻塞。

为什么会退化为间隙锁，间隙锁的范围是**(1, 5)**？

MySQL加锁的单位是next-key lock，next-key lock的区间是左开右闭(出处可点击该链接MySQL :: MySQL 8.0 Reference Manual :: 17.7.1 InnoDB Locking)，此时事务A的加锁范围是**(1, 5]，但是因为这条SQL是一个等值查询(id = 2)，id = 5的记录并不满足条件，next-key lock会退化为间隙锁，最终的加锁范围变成(1, 5)**，退化成间隙锁后依旧可以解决幻读问题。

唯一索引范围查询

唯一索引范围查询比等值查询要复杂许多，我将范围查询分成两种，一种是大于或者大于等于，一种是小于或者小于等于。唯一索引范围查询锁退化原则如下：

针对大于等于的范围查询，如果等值查询的条件记录存在会退化为**记录锁**；
针对小于或者小于等于的范围查询，要看查询条件值是否存在：
- 条件值不存在时，不管是小于还是小于等于，扫描到第一个不满足条件的记录时，该记录的索引会退化为间隙锁，其他记录为next-key lock；
- 条件值存在时，如果是小于，扫描到第一个不满足条件的记录时，该记录的索引会退化为间隙锁，其他记录为next-key lock；如果是小于等于，第一个不满足条件的记录不会退化为间隙锁，其他记录加next-key lock；

大于或者大于等于范围查询

案例一：大于查询

sql 复制代码

mysql> select * from user where id > 15 for update;
+----+---------+------+--------------------+
| id | name    | age  | code               |
+----+---------+------+--------------------+
| 20 | Jackson |   15 | 110101200007281807 |
+----+---------+------+--------------------+
1 row in set (0.00 sec)

MySQL的加锁过程如下：

MySQL扫描到第一行是id = 20这条记录，但是因为不是等值查询，所以不会退化，此时加锁是next-key lock，范围是(15, 20]；
之后继续玩下扫描，但是id = 20的记录已经是最后一条记录了，MySQL中使用了一个特殊的伪记录标记来标识最后的记录，这个标记就是supremum，因此扫描到这里的时候就结束了，此时加锁的范围是(20, +∞]。

从以上分析可得一共加了两个X型的next-key lock。

再通过官方提供的方法验证一下分析过程：

案例二：大于等于查询

sql 复制代码

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

mysql> select * from user where id >= 15 for update;
+----+---------+------+--------------------+
| id | name    | age  | code               |
+----+---------+------+--------------------+
| 15 | Noah    |   25 | 110101200007284100 |
| 20 | Jackson |   15 | 110101200007281807 |
+----+---------+------+--------------------+
2 rows in set (0.00 sec)

加锁过程分析：

扫描到第一条记录id = 15，此时因为满足等值查询，会退还为记录锁，仅锁住id = 15这一条记录；
继续往下扫描找到id = 20的记录，此时会(15, 20]的next-key lock；
扫描到特殊的伪记录，扫描也就结束了，此时加的锁是(20, +∞]的next-key lock；

通过SELECT * FROM performance_schema.data_locks\G;也可以验证上面的结论。

小于或者小于等于范围查询

案例三：小于且查询条件记录不存在的范围查询

bash 复制代码

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

mysql> select * from user where id < 6 for update;
+----+--------+------+--------------------+
| id | name   | age  | code               |
+----+--------+------+--------------------+
|  1 | Emma   |   20 | 11010120000728520X |
|  5 | Sophia |   41 | 110101200007285701 |
+----+--------+------+--------------------+
2 rows in set (0.00 sec)

加锁过程分析：

扫描到第一条记录是id = 1的数据，此时会加上(-∞, 1]的next-key lock；
接下来扫描到id = 5的记录，会加上(1, 5]的next-key lock；
id = 5的记录依旧满足条件，往下继续扫描，直到扫描到id = 10的记录，此时已经不满足查询条件，这个时候next-key lock会退化为间隙锁，范围是(5, 10)，扫描到此结束。

通过这三个锁，可以控制id在1,2,3,4,5,6,7,8,9上无法插入或者更新记录。

案例四：小于且查询条件记录存在的范围查询

bash 复制代码

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

mysql> select * from user where id < 5 for update;
+----+------+------+--------------------+
| id | name | age  | code               |
+----+------+------+--------------------+
|  1 | Emma |   20 | 11010120000728520X |
+----+------+------+--------------------+
1 row in set (0.00 sec)

加锁过程分析：

扫描到第一条记录是id = 1的数据，此时会加上(-∞, 1]的next-key lock；
接下来扫描到id = 5的记录，id = 5的记录不满足条件，会退化为(1, 5)的间隙锁；

案例五：小于等于且查询条件记录存在的范围查询

bash 复制代码

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

mysql> select * from user where id <= 5 for update;
+----+--------+------+--------------------+
| id | name   | age  | code               |
+----+--------+------+--------------------+
|  1 | Emma   |   20 | 11010120000728520X |
|  5 | Sophia |   41 | 110101200007285701 |
+----+--------+------+--------------------+
2 rows in set (0.00 sec)

加锁过程不再赘述，直接给结论：主键索引加了两个锁，一个是(-∞, 1]X型next-key lock；一个是(1, 5]X型next-key lock。

bash 复制代码

mysql> SELECT * FROM performance_schema.data_locks\G;
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2768658825096:1063:2768624101976
ENGINE_TRANSACTION_ID: 1322
            THREAD_ID: 63
             EVENT_ID: 17
        OBJECT_SCHEMA: learn
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2768624101976
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2768658825096:2:4:2:2768624099192
ENGINE_TRANSACTION_ID: 1322
            THREAD_ID: 63
             EVENT_ID: 17
        OBJECT_SCHEMA: learn
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2768624099192
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 1
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2768658825096:2:4:3:2768624099192
ENGINE_TRANSACTION_ID: 1322
            THREAD_ID: 63
             EVENT_ID: 17
        OBJECT_SCHEMA: learn
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2768624099192
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 5
3 rows in set (0.00 sec)

ERROR:
No query specified

案例六：小于等于且查询条件记录不存在的范围查询

加锁与案例三：小于且查询条件记录不存在的范围查询一样。

非唯一索引等值查询

使用非唯一索引等值查询的时候，在加锁的时候会给主键索引加锁和二级索引加锁，但是对于主键索引，只有满足查询条件的记录才会对主键索引上锁。

加锁规则：

当查询的记录存在时，对于第一个不满足条件的记录的锁会退化为间隙锁，其他记录加next-key lock，同时在满足条件的记录的主键索引添加记录锁；
当查询的记录不存在时，对于第一个不满足条件的记录的锁会退化为间隙锁，主键索引不加锁；

案例七：非唯一索引等值查询，查询记录不存在

bash 复制代码

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

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

加锁过程分析：

在二级索引上一直扫描到第一条不符合条件的记录，即id = 10, age = 36这条记录，next-key lock会退化为(25, 36)的间隙锁；

还是使用SELECT * FROM performance_schema.data_locks\G;查看锁信息，下面只给出了行锁信息

bash 复制代码

mysql> SELECT * FROM performance_schema.data_locks\G;
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2768658825096:2:7:4:2768624099192
ENGINE_TRANSACTION_ID: 1329
            THREAD_ID: 63
             EVENT_ID: 51
        OBJECT_SCHEMA: learn
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_age
OBJECT_INSTANCE_BEGIN: 2768624099192
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 36, 10
2 rows in set (0.00 sec)

ERROR:
No query specified

这里面可以看到LOCK_DATA中有两个数值，根据前文提到的LOCK_DATA描述可以得知第一个值是二级索引的值，第二个值是主键索引的值，根据第一个值可以确定age索引树的间隙锁范围。分析LOCK_DATA: 36, 10的10对其他事务插入数据之前先了解什么情况下一条数据插入会被阻塞。

insert语句执行之前需要确定这条记录在索引树的位置，如果插入的位置有记录锁、间隙锁或者临建锁会被阻塞。

对于age = 25的记录插入情况如下：

insert into user (id, name, age, code) values (6, 'Paul', 25, '340604200007286505');这条SQL中id = 12, age = 25，插入的时候在age索引树上它的下一条记录是age = 25, id = 15，这个时候可以插入成功；
insert into user (id, name, age, code) values (16, 'Paul', 25, '340604200007286500');这条记录的下一条记录是age = 36, id = 10，插入失败；

对于非唯一索引来说，是否插入成功还需要考虑主键的值，LOCK_DATA: 36, 10表示的间隙锁范围其实是[age值+id值]确定的，看下面这张图就很容易理解上面两条SQL的执行结果了：

感兴趣的朋友可以试一下下面两条SQL的执行接过，在执行之前可以先按照上面的过程分析一下得出结果，再到客户端执行看看是否与自己的分析结果相一致。
sql 复制代码
# 必须手动开启事务
begin;
insert into user (id, name, age, code) values (7, 'Paul', 36, '340604200007250568');
insert into user (id, name, age, code) values (17, 'Paul', 36, '340604200007250568');

案例八：非唯一索引等值查询，查询记录存在

bash 复制代码

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

mysql> select * from user where age = 25 for update;
+----+------+------+--------------------+
| id | name | age  | code               |
+----+------+------+--------------------+
| 15 | Noah |   25 | 110101200007284100 |
+----+------+------+--------------------+
1 row in set (0.00 sec)

加锁过程分析：

扫描到第一条记录是id = 15的记录，会加上(20, 25]的next-key lock，又因为age = 25满足查询条件，会在主键索引上加上id = 15的X型记录锁；
继续扫描到id = 10的记录，这个记录是第一条不符合查询条件的二级索引，会给二级索引加上(25, 36)的间隙锁，然后停止扫描；

查看日志验证结果：

为什么还需要(25, 36)的间隙锁？

引入间隙锁是为了在可重复读的隔离级别下解决幻读问题，假设这里不加间隙锁，当age = 25, id = 16的数据准备插入时，下一条记录是age = 36, id = 10，没有间隙锁就会插入成功造成幻读；加上间隙锁后就会被阻塞，从而避免幻读。

非唯一索引范围查询

非唯一索引范围查询和唯一索引范围查询区别在于即使查询条件存在也不会退化为间隙锁或者记录锁，任何情况都是next-key lock。

以下面的案例分析一下：

sql 复制代码

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

mysql> select * from user where age >= 36 for update;
+----+--------+------+--------------------+
| id | name   | age  | code               |
+----+--------+------+--------------------+
| 10 | Liam   |   36 | 110101200007280601 |
|  5 | Sophia |   41 | 110101200007285701 |
+----+--------+------+--------------------+
2 rows in set (0.00 sec)

加锁过程分析：

扫描到的第一行是age = 36, id = 10的记录，因为不是唯一索引，所以加的是(25, 36]next-key lock，并且会在id = 10的主键索引上添加记录锁；
继续扫描到age = 41, id = 5的记录，添加(36, 41]next-key lock，在主键索引id = 5的上加记录锁；
已经扫描完最后一条记录了，给特殊标记最后一条记录加锁，也就是(41, +∞]next-key lock；

sql 复制代码

mysql> select * from performance_schema.data_locks\G;
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2768658825096:1063:2768624101976
ENGINE_TRANSACTION_ID: 1434
            THREAD_ID: 74
             EVENT_ID: 19
        OBJECT_SCHEMA: learn
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2768624101976
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2768658825096:2:7:1:2768624099192
ENGINE_TRANSACTION_ID: 1434
            THREAD_ID: 74
             EVENT_ID: 19
        OBJECT_SCHEMA: learn
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_age
OBJECT_INSTANCE_BEGIN: 2768624099192
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2768658825096:2:7:3:2768624099192
ENGINE_TRANSACTION_ID: 1434
            THREAD_ID: 74
             EVENT_ID: 19
        OBJECT_SCHEMA: learn
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_age
OBJECT_INSTANCE_BEGIN: 2768624099192
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 41, 5
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2768658825096:2:7:4:2768624099192
ENGINE_TRANSACTION_ID: 1434
            THREAD_ID: 74
             EVENT_ID: 19
        OBJECT_SCHEMA: learn
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_age
OBJECT_INSTANCE_BEGIN: 2768624099192
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 36, 10
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2768658825096:2:4:3:2768624099536
ENGINE_TRANSACTION_ID: 1434
            THREAD_ID: 74
             EVENT_ID: 19
        OBJECT_SCHEMA: learn
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2768624099536
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 5
*************************** 6. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2768658825096:2:4:4:2768624099536
ENGINE_TRANSACTION_ID: 1434
            THREAD_ID: 74
             EVENT_ID: 19
        OBJECT_SCHEMA: learn
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2768624099536
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 10
6 rows in set (0.00 sec)

ERROR:
No query specified

为什么非唯一索引中等值查询的条件二级索引的锁存在不会退化为记录锁

非唯一索引锁退化无法阻止幻读现象出现，如果退化为记录锁，因为不是唯一索引，所以只能限制删除和修改，无法限制插入，从而导致幻读现象出现。

没有使用索引的查询

对于没有使用索引或者索引失效的select update delete语句都会导致全表扫描，全表扫描会给每一条记录添加next-key lock，最终全表被锁住。

sql 复制代码

mysql> select * from user where age % 2 = 0 for update;
+----+------+------+--------------------+
| id | name | age  | code               |
+----+------+------+--------------------+
|  1 | Emma |   20 | 11010120000728520X |
| 10 | Liam |   36 | 110101200007280601 |
+----+------+------+--------------------+
2 rows in set (0.00 sec)

mysql> select * from performance_schema.data_locks\G;
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2768658825096:1063:2768624101976
ENGINE_TRANSACTION_ID: 1435
            THREAD_ID: 74
             EVENT_ID: 26
        OBJECT_SCHEMA: learn
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2768624101976
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2768658825096:2:4:1:2768624099192
ENGINE_TRANSACTION_ID: 1435
            THREAD_ID: 74
             EVENT_ID: 26
        OBJECT_SCHEMA: learn
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2768624099192
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2768658825096:2:4:2:2768624099192
ENGINE_TRANSACTION_ID: 1435
            THREAD_ID: 74
             EVENT_ID: 26
        OBJECT_SCHEMA: learn
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2768624099192
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 1
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2768658825096:2:4:3:2768624099192
ENGINE_TRANSACTION_ID: 1435
            THREAD_ID: 74
             EVENT_ID: 26
        OBJECT_SCHEMA: learn
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2768624099192
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 5
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2768658825096:2:4:4:2768624099192
ENGINE_TRANSACTION_ID: 1435
            THREAD_ID: 74
             EVENT_ID: 26
        OBJECT_SCHEMA: learn
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2768624099192
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 10
*************************** 6. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2768658825096:2:4:5:2768624099192
ENGINE_TRANSACTION_ID: 1435
            THREAD_ID: 74
             EVENT_ID: 26
        OBJECT_SCHEMA: learn
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2768624099192
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 15
*************************** 7. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2768658825096:2:4:6:2768624099192
ENGINE_TRANSACTION_ID: 1435
            THREAD_ID: 74
             EVENT_ID: 26
        OBJECT_SCHEMA: learn
          OBJECT_NAME: user
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2768624099192
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 20
7 rows in set (0.00 sec)

ERROR:
No query specified

总结

在分析SQL加锁的时候，遵循开头所提到的几个规则，从反证的思路判断是否会造成幻读现象，进而分析next-key lock是否会退化成间隙锁或者行锁。

最后放两张流程图方便理解。

参考资料：

作者：LSL

类型：原创

本文链接：https://lslshiba.top/article/9

此文章版权归LSL所有，如有转载请注明原作者。