MySQL(二十三)-加锁分析实战
参考文献
- 解决死锁之路 - 常见 SQL 语句的加锁分析
- 何登成–管中窥豹——MySQL(InnoDB)死锁分析之道
- MySQL · 引擎特性 · InnoDB隐式锁功能解析
- Locks Set by Different SQL Statements in InnoDB
- MySQL技术内幕 InnoDB存储引擎
- 27.12.13.1 The data_locks Table
- MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁
- 一张图彻底搞懂 MySQL 的锁机制
- mysql事务和锁 SELECT FOR UPDATE
- MySQL锁总结
- MySQL DELETE 删除语句加锁分析
加锁实战分析
-
对于唯一键值的锁定,
Next-Key Lock降级为Record Lock仅存在于查询所有的唯一索引列.若唯一索引由多个列组成,而查询仅是查找多个唯一索引列中的其中一个,那么查询其实是range类型,而不是point类型,因此InnoDB存储引擎依然会使用Next-Key Lock进行锁定.操作序号 SessionA SessionB 1 begin; 2 select * from t where id=5 for update; 3 begin; 4 insert into t select 4; 5 commit;#成功,不需要等待 6 commit; -
若是辅助索引,则情况会完全不同
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16mysql> show variables like '%transaction_isolation%';
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.01 sec)
create table z(a int ,b int,primary
key(a),key(b));
insert into z select 1,1;
insert into z select 3,1;
insert into z select 5,3;
insert into z select 7,6;
insert into z select 10,8;-
表z的列b是辅助索引,若在SessionA中执行下面SQL
1
select * from z where b=3 for update;
-
观察
PERFORMANCE_SCHEMA.DATA_LOCKS表信息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
66mysql> select * from PERFORMANCE_SCHEMA.DATA_LOCKS\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4566258352:5769:5039511720
ENGINE_TRANSACTION_ID: 507987
THREAD_ID: 55
EVENT_ID: 143
OBJECT_SCHEMA: dicom
OBJECT_NAME: z
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 5039511720
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4566258352:4707:5:4:5042258968
ENGINE_TRANSACTION_ID: 507987
THREAD_ID: 55
EVENT_ID: 143
OBJECT_SCHEMA: dicom
OBJECT_NAME: z
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: b
OBJECT_INSTANCE_BEGIN: 5042258968
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 3, 5
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4566258352:4707:4:4:5042259312
ENGINE_TRANSACTION_ID: 507987
THREAD_ID: 55
EVENT_ID: 143
OBJECT_SCHEMA: dicom
OBJECT_NAME: z
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5042259312
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 5
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4566258352:4707:5:5:5042259656
ENGINE_TRANSACTION_ID: 507987
THREAD_ID: 55
EVENT_ID: 143
OBJECT_SCHEMA: dicom
OBJECT_NAME: z
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: b
OBJECT_INSTANCE_BEGIN: 5042259656
LOCK_TYPE: RECORD
LOCK_MODE: X,GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 6, 7
4 rows in set (0.01 sec)- 可以看到首先对辅助索引值为3这条记录加上了
Next-Key Lock(即锁定范围(1,3]),然后辅助索引对应的主键索引加上了Record Lock(即锁定a=5的聚簇索引),最后对辅助索引值为3的下一个键值加上Gap Lock(即锁定范围(3,6))
- 可以看到首先对辅助索引值为3这条记录加上了
-
MySQL中SELECT...FOR UPDATE加锁分析
隔离级别与准备工作
1 | mysql> show variables like 'transaction_isolation'; |
-
明确指定主键并且数据存在,在查询到的数据上加
Record Lock
-
明确指定主键但数据不存在,
-
条件小于当前索引值最小值,在索引最小值上加
Gap Lock -
条件在当前索引值区间内,在该区间的右边界索引值上加
Gap Lock -
条件大于当前索引值最大值,在
supremum pseudo-record伪记录上加上Next-Key Lock
-
-
不使用主键(不管查询的数据存不存在),加表锁,由下方
data_locks表中的信息看出,MySQL对每个主键索引都加上了Next-Key Lock1
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
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from z where b = 2 for update;
+---+------+
| a | b |
+---+------+
| 1 | 2 |
+---+------+
1 row in set (0.00 sec)
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 5407970096:1065:5518680984
ENGINE_TRANSACTION_ID: 6973
THREAD_ID: 48
EVENT_ID: 125
OBJECT_SCHEMA: debug
OBJECT_NAME: z
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 5518680984
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 5407970096:4:4:1:5528285720
ENGINE_TRANSACTION_ID: 6973
THREAD_ID: 48
EVENT_ID: 125
OBJECT_SCHEMA: debug
OBJECT_NAME: z
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5528285720
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 5407970096:4:4:2:5528285720
ENGINE_TRANSACTION_ID: 6973
THREAD_ID: 48
EVENT_ID: 125
OBJECT_SCHEMA: debug
OBJECT_NAME: z
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5528285720
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 1
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 5407970096:4:4:4:5528285720
ENGINE_TRANSACTION_ID: 6973
THREAD_ID: 48
EVENT_ID: 125
OBJECT_SCHEMA: debug
OBJECT_NAME: z
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5528285720
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 3
*************************** 5. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 5407970096:4:4:5:5528285720
ENGINE_TRANSACTION_ID: 6973
THREAD_ID: 48
EVENT_ID: 125
OBJECT_SCHEMA: debug
OBJECT_NAME: z
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5528285720
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 9
*************************** 6. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 5407970096:4:4:6:5528285720
ENGINE_TRANSACTION_ID: 6973
THREAD_ID: 48
EVENT_ID: 125
OBJECT_SCHEMA: debug
OBJECT_NAME: z
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 5528285720
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 5
6 rows in set (0.01 sec)
.png)
MySQL简单 SQL 的加锁分析
-
MySQL版本
1
2
3
4
5
6
7mysql> select version();
+--------------+
| version() |
+--------------+
| 8.0.27-debug |
+--------------+
1 row in set (0.01 sec) -
RR与RC下进行对比
-
前置准备
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17CREATE TABLE `s` (
`id` int NOT NULL AUTO_INCREMENT,
`no` varchar(10) NOT NULL,
`name` varchar(64) NOT NULL,
`age` int NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `no` (`no`),
Key `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `s` (`id`, `no`, `name`, `age`) VALUES (15, 'S0001', 'Bob', 25);
INSERT INTO `s` (`id`, `no`, `name`, `age`) VALUES (18, 'S0002', 'Alice', 24);
INSERT INTO `s` (`id`, `no`, `name`, `age`) VALUES (20, 'S0004', 'Jim', 24);
INSERT INTO `s` (`id`, `no`, `name`, `age`) VALUES (30, 'S0005', 'Eric', 23);
INSERT INTO `s` (`id`, `no`, `name`, `age`) VALUES (37, 'S0006', 'Tom', 22);
INSERT INTO `s` (`id`, `no`, `name`, `age`) VALUES (49, 'S0008', 'Tom', 25);
INSERT INTO `s` (`id`, `no`, `name`, `age`) VALUES (50, 'S0017', 'Rose', 23);
聚簇索引,查询命中
1 | mysql> begin; |
- 结论: 在使用聚簇索引且查询命中情况下,RR与RC都在聚簇索引上加上了
Record Locks
聚簇索引,查询未命中
1 | mysql> begin; |
- 结论,在使用聚簇索引,但查询未命中的情况下
- RR下,加上了
Gap Locks(区间(15,18)) - RC不加锁
- RR下,加上了
二级唯一索引,查询命中
1 | mysql> begin; |
- 结论: 在使用二级唯一索引且查询命中情况下,RR和RC都在二级索引上加上了
Record Locks以及二级索引对应的聚簇索引上加上了Record Locks
二级唯一索引,查询未命中
1 | mysql> begin; |
-
结论: 在使用二级唯一索引,查询未命中情况下
-
RR下会取决于未命中的条件的值与当前现有的索引的值进行比较后落在那个区间,从而判断加
Gap Locks还是Next-Key Locks-
条件的值在当前现有的索引的值的(-∞,max)区间内,加
Gap Locks1
2
3
4
5
6
7
8
9
10mysql> begin;
Query OK, 0 rows affected (0.01 sec)
-- update s set age = 20 where no ='-10000000'; 落在(-∞,'S0001')上,在S0001加Gap Locks
-- update s set age = 20 where no =''; 落在(-∞,'S0001')上,在S0001加Gap Locks
-- update s set age = 20 where no ='S0003'; 落在('S0002','S0004')上,在S0004加Gap Locks
-- 落在('S0008','S00017')上,在S00017加Gap Locks 锁定 "S0011" 所在的索引区间
mysql> update s set age = 20 where no ='S0011';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0 -
条件的值超出当前现有索引的值的最大值,在
supremum pseudo-record上加Next-Key Locks1
2
3
4
5
6mysql> begin;
Query OK, 0 rows affected (0.01 sec)
-- 落在('S00017',+∞]上,在`supremum pseudo-record`上加`Next-Key Locks`
mysql> update s set age = 20 where no ='S0020';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
-
-
RC下不加锁
-
二级非唯一索引,查询命中
1 | mysql> begin; |
- 结论,在使用二级非唯一索引,查询命中情况下
- RR下,会在命中的二级非唯一索引上加上
Next-Key Locks,在二级非唯一索引对应的聚簇索引上加上Record Locks- 若查询条件在二级非唯一索引(-∞,max)区间内,则会在不符合条件的下一个二级非唯一索引上加
GAP Locks- 关于"在不符合条件的下一个二级非唯一索引上加
GAP Locks"的原因猜想:因为索引是不唯一的,防止其他事务中插入和当前索引相同的值,因此将下一个二级非唯一索引也加上了GAP Locks
- 关于"在不符合条件的下一个二级非唯一索引上加
- 若查询条件恰好等于二级非唯一索引区间的最大值(MAX),则会在
supremum pseudo-record加上Next-Key Locks
- 若查询条件在二级非唯一索引(-∞,max)区间内,则会在不符合条件的下一个二级非唯一索引上加
- RC下,会在命中的二级非唯一索引以及对应的聚簇索引上都加上
Record Locks
- RR下,会在命中的二级非唯一索引上加上
二级非唯一索引,查询未命中
1 | mysql> begin; |
- 结论: 在使用二级非唯一索引,查询未命中情况下,
- RR下,如果未命中的条件的值在当前现有的索引值的区间(-∞,max)内,则会在不满足条件的下一个二级非唯一索引加
Gap Locks.否则(即在区间(max,+∞))会在supremum pseudo-record上加Next-Key Locks以避免幻读现象的发生. - RC下不会在索引上加锁.
- RR下,如果未命中的条件的值在当前现有的索引值的区间(-∞,max)内,则会在不满足条件的下一个二级非唯一索引加
无索引
1 | mysql> begin; |
- 结论: 在不使用索引的情况下
- RR下,
- 如果查询命中,则会在所有的聚簇索引上加
Next-Key Locks,即锁表； - 如果查询未命中,会对不满足条件的所有记录加
Next-Key Locks和对应的聚簇索引加Record Locks - 若执行的是更新操作,则会锁表.
- 如果查询命中,则会在所有的聚簇索引上加
- RC下,只会在命中的记录的聚簇索引上
Record Locks,不命中则不加锁
- RR下,
在没有索引的时候,只能走聚簇索引,对表中的记录进行全表扫描.在 RC 隔离级别下会给所有记录加行锁,在 RR 隔离级别下,不仅会给所有记录加行锁,所有聚簇索引和聚簇索引之间还会加上 GAP 锁 即锁表.
语句
update s set name = 'Ju' where age = 25;满足条件的虽然只有 1 条记录,但是聚簇索引上所有的记录,都被加上了 X 锁.那么,为什么不是只在满足条件的记录上加锁呢？这是由于 MySQL 的实现决定的.如果一个条件无法通过索引快速过滤,那么存储引擎层面就会将所有记录加锁后返回,然后由 MySQL Server 层进行过滤,因此也就把所有的记录都锁上了.不过在实际的实现中,MySQL 有一些改进,如果是 RC 隔离级别,在 MySQL Server 过滤条件发现不满足后,会调用 unlock_row 方法,把不满足条件的记录锁释放掉（违背了 2PL 的约束）.这样做可以保证最后只会持有满足条件记录上的锁,但是每条记录的加锁操作还是不能省略的.如果是 RR 隔离级别,一般情况下 MySQL 是不能这样优化的,除非设置了
innodb_locks_unsafe_for_binlog参数,这时也会提前释放锁,并且不加 GAP 锁,这就是所谓的 semi-consistent read.
聚簇索引,范围查询
1 | mysql> begin; |
- 在使用聚簇索引,范围查询情况下,
- RR下会对符合范围查询的聚簇索引加上
Next-Key Locks - RC下会对符合范围查询的聚簇索引加上
Record Locks
- RR下会对符合范围查询的聚簇索引加上
二级唯一索引,范围查询
1 | mysql> begin; |
- 结论: 在使用二级索引范围查询情况下
- RR下会对满足范围查询的二级索引加上
Next-Key Locks以及对应的聚簇索引加上Record Locks外,还会在不满足条件的第一个二级索引上加上Next-Key Locks以及对应的聚簇索引上加上Record Locks- 即上述信息中满足条件的为(‘S0001’,‘S0002’),对应的聚簇索引为(15,18),不满足条件的第一个则为(‘S0004’)对应的聚簇索引为(20)
- RC下会对满足范围查询的二级索引以及对应的聚簇索引都加上
Record Locks
- RR下会对满足范围查询的二级索引加上
总结
聚簇索引,查询命中
- 在使用聚簇索引且查询命中情况下,RR和RC都会在聚簇索引上加上
Record Locks,锁定的是符合查询条件的行. - 如果查询语句使用了
FOR UPDATE或LOCK IN SHARE MODE语句,则会额外加上Next-Key Locks,同时会在聚簇索引的supremum pseudo-record上加上Next-Key Locks,避免幻读的发生.
聚簇索引,查询未命中
- 结论,在使用聚簇索引,但查询未命中的情况下
- RR下,如果表中不存在符合查询条件的记录,那么会在聚簇索引上加上
Gap Locks,以此避免其他事务往数据表中插入数据时,产生幻读的情况. - RC下,不会在聚簇索引上加锁.
- RR下,如果表中不存在符合查询条件的记录,那么会在聚簇索引上加上
二级唯一索引,查询命中
- 结论: 在使用二级唯一索引且查询命中情况下
- RR和RC都会在二级唯一索引上加上
Record Locks和对应的聚簇索引上的Record Locks. - 如果查询语句使用了
FOR UPDATE或LOCK IN SHARE MODE语句,则会额外加上相应的Next-Key Locks以及在聚簇索引的supremum pseudo-record上加上Next-Key Locks,以此避免幻读的发生.
- RR和RC都会在二级唯一索引上加上
二级唯一索引,查询未命中
-
结论: 在使用二级唯一索引,查询未命中情况下
-
RR下会取决于未命中的条件的值与当前现有的索引的值进行比较后落在那个区间,从而判断加
Gap Locks还是Next-Key Locks-
如果未命中的条件的值在当前现有的索引值的区间 (-∞,max) 内,则会在二级唯一索引和对应的聚簇索引上加
Gap Locks -
条件的值超出当前现有索引的值的最大值,在
supremum pseudo-record上加Next-Key Locks,以避免幻读的出现
-
-
RC下不加锁
-
二级非唯一索引,查询命中
- 结论,在使用二级非唯一索引,查询命中情况下
-
- RR下,会在命中的二级非唯一索引上加上
Next-Key Locks,在二级非唯一索引对应的聚簇索引上加上Record Locks- 若查询条件在二级非唯一索引(-∞,max)区间内,则会在不符合条件的下一个二级非唯一索引上加
GAP Locks- 关于"在不符合条件的下一个二级非唯一索引上加
GAP Locks"的原因猜想:因为索引是不唯一的,防止其他事务中插入和当前索引相同的值(为防止发生幻读),因此将下一个二级非唯一索引也加上了GAP Locks
- 关于"在不符合条件的下一个二级非唯一索引上加
- 若查询条件恰好等于二级非唯一索引区间的最大值(MAX),则会在
supremum pseudo-record加上Next-Key Locks
- 若查询条件在二级非唯一索引(-∞,max)区间内,则会在不符合条件的下一个二级非唯一索引上加
- RR下,会在命中的二级非唯一索引上加上
- RC下,会在命中的二级非唯一索引以及对应的聚簇索引上都加上
Record Locks
-
二级非唯一索引,查询未命中
- 结论: 在使用二级非唯一索引,查询未命中情况下,
- RR下,如果未命中的条件的值在当前现有的索引值的区间(-∞,max)内,则会在不满足条件的下一个二级非唯一索引加
Gap Locks.否则(即在区间(max,+∞))会在supremum pseudo-record上加Next-Key Locks以避免幻读现象的发生. - RC下不会在索引上加锁.
- RR下,如果未命中的条件的值在当前现有的索引值的区间(-∞,max)内,则会在不满足条件的下一个二级非唯一索引加
无索引
- 结论: 在不使用索引的情况下
- RR下
- 如果查询命中,则会在所有的聚簇索引上加
Next-Key Locks,即锁表； - 如果查询未命中,会对不满足条件的所有记录加
Next-Key Locks和对应的聚簇索引加Record Locks - 若执行的是更新操作,则会锁表.
- 如果查询命中,则会在所有的聚簇索引上加
- RC下
- 如果查询命中,则只会在命中的记录的聚簇索引上加上
Record Locks； - 如果查询未命中,则不会在聚簇索引上加锁.
- 如果查询命中,则只会在命中的记录的聚簇索引上加上
- RR下
聚簇索引,范围查询
- 在使用聚簇索引,范围查询情况下,
- RR下会对符合范围查询的聚簇索引加上
Next-Key Locks - RC下会对符合范围查询的聚簇索引加上
Record Locks
- RR下会对符合范围查询的聚簇索引加上
二级唯一索引,范围查询
- 结论: 在使用二级索引范围查询情况下
- RR下会对满足范围查询的二级索引加上
Next-Key Locks以及对应的聚簇索引加上Record Locks外,还会在不满足条件的第一个二级索引上加上Next-Key Locks以及对应的聚簇索引上加上Record Locks - RC下会对满足范围查询的二级索引以及对应的聚簇索引都加上
Record Locks
- RR下会对满足范围查询的二级索引加上
本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 HoleLin's Blog！
