INSERT INTO T SELECT ... FROM S WHERE ... 语句加锁分析

目标

一：分析下面这条语句执行时，会加哪些锁、为什么加这些锁，以及可能会存在什么问题：

INSERT INTO t (version) SELECT IFNULL(MAX(version) + 1, 1) FROM t;

二：掌握基本的分析 MySQL 加锁情况的技能；

结论

加的锁包括：

两个意向锁
- 意向排他锁( IX 锁)：事务想要获得一张表中某几行的排他锁
- 意向共享锁( IS 锁)：事务想要获得一张表中某几行的共享锁
三个共享锁
- 一个对 “上确界伪纪录”的共享临键锁；
- 一个对 “version值最大的那条uk_version索引记录”的共享临键锁；
- 一个对"待插入的数据的uk_version索引记录"的间隙锁；

下表是从 performance_schema.data_locks 表中截取的真实数据(省略了不相关的字段)：

INDEX_NAME	LOCK_TYPE	LOCK_MODE	LOCK_DATA	说明
NULL	TABLE	IX	NULL
NULL	TABLE	IS	NULL
uk_version	RECORD	S	supremum pseudo-record	对“上确界伪纪录”加 shared next-key locks
uk_version	RECORD	S	15,4	对 version 最大的那条记录加 next-key locks
uk_version	RECORD	S,GAP	16, 5

验证

数据库版本

进行实验的 MySQL 版本为: 8.0.27 MySQL Community Server - GPL

库表准备

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


# 创建库
CREATE DATABASE IF NOT EXISTS `test`;
USE `test`;
# 创建表
CREATE TABLE IF NOT EXISTS `t`
(
    `id`      BIGINT AUTO_INCREMENT COMMENT '主键 ID',
    `version` BIGINT DEFAULT 0 NOT NULL COMMENT '版本号',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_version` (`version`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT '版本表';
 
# 初始化数据
INSERT INTO `t` (`version`) VALUE (1);
INSERT INTO `t` (`version`) VALUE (5);
INSERT INTO `t` (`version`) VALUE (10);
INSERT INTO `t` (`version`) VALUE (15);

实验

实验一

INSERT INTO t (version) SELECT IFNULL(MAX(version) + 1, 1) FROM t`` 直接执行 INSERTO … SELECT 然后看加锁情况。

1
2
3
4
5
6
7
8
9


# session 1
BEGIN;
INSERT INTO `t` (`version`) SELECT IFNULL(MAX(version) + 1, 1) FROM `t`;
 
# session 2
SELECT ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, THREAD_ID, INDEX_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
 
# session 1
ROLLBACK;

MySQL 官方文档中，大概解释了为什么加这些锁：

https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html INSERT INTO T SELECT … FROM S WHERE … sets an exclusive index record lock (without a gap lock) on each row inserted into T. If the transaction isolation level is READ COMMITTED, InnoDB does the search on S as a consistent read (no locks). Otherwise, InnoDB sets shared next-key locks on rows from S. InnoDB has to set locks in the latter case: During roll-forward recovery using a statement-based binary log, every SQL statement must be executed in exactly the same way it was done originally. CREATE TABLE … SELECT … performs the SELECT with shared next-key locks or as a consistent read, as for INSERT … SELECT. When a SELECT is used in the constructs REPLACE INTO t SELECT … FROM s WHERE … or UPDATE t … WHERE col IN (SELECT … FROM s …), InnoDB sets shared next-key locks on rows from table s.

实验二

SELECT IFNULL(MAX(version) + 1, 1) AS version FROM t FOR SHARE; 单独执行 SELECT FOR SHARE 然后查看加锁情况：

1
2
3
4
5
6
7
8
9


# session 1
BEGIN;
SELECT IFNULL(MAX(version) + 1, 1) AS version FROM `t` FOR SHARE;
 
# session 2
SELECT ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, THREAD_ID, INDEX_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
 
# session 1
ROLLBACK;

实验三

先 SELECT FOR SHARE 然后执行 INSERT.

可以看到，在执行完 INSERT 后，获取的锁与实验一相同。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


# session 1
BEGIN;
SELECT IFNULL(MAX(version) + 1, 1) AS version FROM `t` FOR SHARE;
 
# session 2
SELECT ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, THREAD_ID, INDEX_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
 
# session 1
INSERT INTO `t` (`version`) VALUE (16);
 
# session 2
SELECT ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, THREAD_ID, INDEX_NAME, OBJECT_INSTANCE_BEGIN, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
 
# session 1
ROLLBACK;

加锁分析

对于上面的数据，其uk_version的结构大概如下：

INNODB 二级索引示意图

在执行 SELECT IFNULL(MAX(version) + 1, 1) AS version FROM t FOR SHARE 时：

首先会先扫描到 (version=15, id=4) 这条记录，然后对其加 next-key lock；然后继续向后扫描，对 supremum pseudo-record 这条伪记录加 next-key lock。

小节

结论一：INSERT INTO … SELECT FROM 等价于先执行 SELECT FROM FOR SHARE 然后再 INSERT INTO；
结论二：SELECT MAX(…) FROM … 是范围查询，因此会加间隙锁；
结论三：结论二对唯一索引同样适用；

理论

锁分类

data_locks 表

官方文档：https://dev.mysql.com/doc/mysql-perfschema-excerpt/8.0/en/performance-schema-data-locks-table.html

示例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82


MySQL [test]> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 281472727006424:1069:281472627977064
ENGINE_TRANSACTION_ID: 2423
            THREAD_ID: 53
             EVENT_ID: 129
        OBJECT_SCHEMA: test
          OBJECT_NAME: t
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 281472627977064
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 281472727006424:1069:281472627976976
ENGINE_TRANSACTION_ID: 2423
            THREAD_ID: 53
             EVENT_ID: 128
        OBJECT_SCHEMA: test
          OBJECT_NAME: t
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 281472627976976
            LOCK_TYPE: TABLE
            LOCK_MODE: IS
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 281472727006424:4:5:1:281472627973984
ENGINE_TRANSACTION_ID: 2423
            THREAD_ID: 53
             EVENT_ID: 128
        OBJECT_SCHEMA: test
          OBJECT_NAME: t
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: uk_version
OBJECT_INSTANCE_BEGIN: 281472627973984
            LOCK_TYPE: RECORD
            LOCK_MODE: S
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 281472727006424:4:5:5:281472627973984
ENGINE_TRANSACTION_ID: 2423
            THREAD_ID: 53
             EVENT_ID: 128
        OBJECT_SCHEMA: test
          OBJECT_NAME: t
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: uk_version
OBJECT_INSTANCE_BEGIN: 281472627973984
            LOCK_TYPE: RECORD
            LOCK_MODE: S
          LOCK_STATUS: GRANTED
            LOCK_DATA: 15, 4
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 281472727006424:4:5:6:281472627974328
ENGINE_TRANSACTION_ID: 2423
            THREAD_ID: 53
             EVENT_ID: 129
        OBJECT_SCHEMA: test
          OBJECT_NAME: t
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: uk_version
OBJECT_INSTANCE_BEGIN: 281472627974328
            LOCK_TYPE: RECORD
            LOCK_MODE: S,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 16, 7
5 rows in set (0.004 sec)

字段说明

意向锁的作用

当我们准备给一张表加上表锁的时候，我们首先要去判断有没其他的事务锁定了其中了某些行？如果有的话，肯定不能加上表锁。那么这个时候我们就要去扫描整张表才能确定能不能成功加上一个表锁，如果数据量特别大，比如有上千万的数据的时候，加表锁的效率会很低。当我们在使用共享行锁时，Innodb 会自动给我们加上IS，使用排他行锁时自动加上IX ，用来表示改表中已经存在那些锁参考：https://www.jianshu.com/p/ae91c63e7257

插入意向锁的作用

插入意向锁是在插入之前，先判断插入的间隙是否存在间隙锁，如果存在则产生一个插入意向锁，去等待间隙锁的释放。多个事务插入同一个间隙的不同位置，他们并不会冲突。假设存在索引记录，其值分别为5和9。单独的事务分别尝试插入值6和7，在获得插入行的排他锁之前，每个事务都使用插入意图锁来锁定5和9之间的间隙，但他们不会互相阻塞。参考官方说明：https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

为什么 INSERT INTO T SELECT … FROM S WHERE … 需要加共享锁

考虑下面的情况：

初始时，表中的 version 的最大值是 15；

时间	事务一	事务二
T1	BEGIN;
T2	INSERT INTO `t` (`version`) SELECT IFNULL(MAX(version) + 1, 1) FROM `t`;
T3	(成功，写入一条 version=16 的数据)	BEGIN;
T4		INSERT INTO `t` (`version`) VALUE (10000);
T5		COMMIT;
T6	COMMIT;

在基于 statement-based binary log 的主从同步模式下，如果不加共享锁，由于事务二是先提交的，因此，bin log 在从库执行的时候，可能出现下面的情况：

对于主库：事务一先写入一条 version=16的记录，事务二后写入一条 version=10000 的记录；
对于从库：事务二先写入一条 version=10000 的记录，事务一后写入一条 version=10001 的记录；✘

库	记录	预期结果	实际结果
主库	记录一	version=16	version=16
主库	记录二	version=10000	version=10000
从库	记录一	version=16	version=10000
从库	记录二	version=10000	version=10001