前言
通过上一篇我们了解了行锁的很多细节,这里结合实战对各种情况演练一番,更深入的理解MySql加锁机制。
PS:本文基于mysql8.0.32, InnoDB存储引擎,RR事务隔离级别。
一、案例
1:表结构
mysql> show create table test\G;
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` bigint NOT NULL,
`num` int NOT NULL,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`key_id` int unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `ind_key` (`key_id`) USING BTREE,
KEY `ind_num` (`num`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
2:表数据
mysql> select * from test;
+----+-----+------+--------+
| id | num | name | key_id |
+----+-----+------+--------+
| 5 | 5 | fd | 5 |
| 7 | 7 | ty | 7 |
| 12 | 12 | io | 12 |
| 18 | 12 | jk | 18 |
| 23 | 22 | po | 23 |
| 24 | 22 | cv | 24 |
| 31 | 28 | ku | 31 |
| 42 | 35 | kl | 49 |
+----+-----+------+--------+
这里先复习下上一章的知识点:
1)行锁细分为记录锁,间隙锁,临键锁。
2)针对主键,记录锁是确定的行,如id=18;
对于间隙锁有(负无穷,5),(5,7)…(31,42),(42,正无穷) ,单位锁定范围遵循左开又开原则 ;
对于临键锁,是记录锁与间隙锁的结合,单位锁定范围遵循左开右闭原则 ,则有
(负无穷,5],(5,7]…(31,42],(42,正无穷) 。
3) 加锁的基本单位是临键锁,视情况会被优化成记录锁或间隙锁。
二、实战分析
如果未命中索引会退化为表锁,因此我们只分析命中索引的情况,命中索引可以按索引类别分为主键,唯一索引,普通索引三种。
1:主键索引
1:select * from test where id=18 for update;
查询数据存在。
mysql> select * from performance_schema.data_locks\G;
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 2138314382288:1064:2138320015896
ENGINE_TRANSACTION_ID: 1420
THREAD_ID: 56
EVENT_ID: 694
OBJECT_SCHEMA: test
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2138320015896
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 2138314382288:2:4:5:2138303895576
ENGINE_TRANSACTION_ID: 1420
THREAD_ID: 56
EVENT_ID: 694
OBJECT_SCHEMA: test
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2138303895576
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 18
可以看到除有表级的意向排它锁(IX),针对id=18这行数据加了记录锁。
2:select * from test where id=20 for update;
查询数据不存在。
mysql> select * from performance_schema.data_locks\G;
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 2138314382288:1064:2138320015896
ENGINE_TRANSACTION_ID: 1421
THREAD_ID: 56
EVENT_ID: 716
OBJECT_SCHEMA: test
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2138320015896
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 2138314382288:2:5:6:2138303895576
ENGINE_TRANSACTION_ID: 1421
THREAD_ID: 56
EVENT_ID: 716
OBJECT_SCHEMA: test
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: ind_key
OBJECT_INSTANCE_BEGIN: 2138303895576
LOCK_TYPE: RECORD
LOCK_MODE: X,GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 23, 23
可以看到除有表级的意向排它锁(IX),还加了id=23这行数据的间隙锁。
加锁过程:1:在主键索引上查找id=20这一行数据,没有查到
2:继续沿着主键索引向叶子结点方向查找,找到第一个大于20的行,即id=23;
3:然后获取id=23这行数据的临键锁,因为是主键索引,所以退化为间隙锁
3:select * from test where id >7 and id<=18 for update;
范围查询。
mysql> select * from performance_schema.data_locks\G;
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 2138314382288:1064:2138320015896
ENGINE_TRANSACTION_ID: 1424
THREAD_ID: 56
EVENT_ID: 775
OBJECT_SCHEMA: test
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2138320015896
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 2138314382288:2:4:4:2138303895576
ENGINE_TRANSACTION_ID: 1424
THREAD_ID: 56
EVENT_ID: 775
OBJECT_SCHEMA: test
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2138303895576
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 12
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 2138314382288:2:4:5:2138303895576
ENGINE_TRANSACTION_ID: 1424
THREAD_ID: 56
EVENT_ID: 775
OBJECT_SCHEMA: test
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2138303895576
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 18
可以看到除有表级的意向排它锁(IX),还加了id=12和id=18这两行数据的临键锁。
加锁过程:1:在主键索引上查找id>7的数据;
2:找到id=12这条数据,获取其临键锁;
3:范围查询,继续查找,依次获取所访问到行的临键锁,直至第一个大于等于where条件里面上限值的值,本例中就是id=18。
思考: 如果条件换成id>7 and id<=20 或id>=7 and id<20会如何呢?
这里先列出答案,不做分析,结论如下:
当 id>7 and id<18时会获取id=12的临键锁和id=18的间隙锁;
当 id>7 and id<=20时会获取id=12,18的临键锁和id=23的间隙锁;
当 id>=7 and id<20时会获取id=7的记录锁,id=12,18的临键锁和id=23的间隙锁;
2.唯一索引
唯一索引加锁规则几乎与主键索引相同,差别之处请往下看
1:select * from test where key_id=18 for update;
查询数据存在。
mysql> select * from performance_schema.data_locks\G;
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 2138314382288:1064:2138320015896
ENGINE_TRANSACTION_ID: 1426
THREAD_ID: 56
EVENT_ID: 819
OBJECT_SCHEMA: test
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2138320015896
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 2138314382288:2:5:5:2138303895576
ENGINE_TRANSACTION_ID: 1426
THREAD_ID: 56
EVENT_ID: 819
OBJECT_SCHEMA: test
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: ind_key
OBJECT_INSTANCE_BEGIN: 2138303895576
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 18, 18
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 2138314382288:2:4:5:2138303895920
ENGINE_TRANSACTION_ID: 1426
THREAD_ID: 56
EVENT_ID: 819
OBJECT_SCHEMA: test
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2138303895920
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 18
可以看到除有表级的意向排它锁(IX),还加了id=18这行数据的记录锁锁和key_id=18的记录锁,所以对于非主键索引来说,除了获取自身的行锁,还会获取所访问行的主键的记录锁。
PS:根据LOCK_DATA字段的值18,18可知,对于非主键索引,其范围表示还要加上主键值
则临键锁有:(负无穷,(5,5)],((5,5),(7,7)]…((31,31),(49,42)],((49,42),正无穷),
即按非主键索引排序
2:select * from test where key_id=20 for update;
查询数据不存在。
mysql> select * from performance_schema.data_locks\G;
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 2138314382288:1064:2138320015896
ENGINE_TRANSACTION_ID: 1427
THREAD_ID: 56
EVENT_ID: 841
OBJECT_SCHEMA: test
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2138320015896
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 2138314382288:2:5:6:2138303895576
ENGINE_TRANSACTION_ID: 1427
THREAD_ID: 56
EVENT_ID: 841
OBJECT_SCHEMA: test
OBJECT_NAME: test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: ind_key
OBJECT_INSTANCE_BEGIN: 2138303895576
LOCK_TYPE: RECORD
LOCK_MODE: X,GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 23, 23
可以看到除有表级的意向排它锁(IX),还加了key_id=23的间隙锁,因为查询结果为空,所以不会存在所访问行主键的记录锁。
3:select * from test where key_id >7 and key_id<17 for update;
范围查询。
mysql> select ENGINE_LOCK_ID,INDEX_NAME, LOCK_TYPE,LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------------------------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_LOCK_ID | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------------------+------------+-----------+---------------+-------------+-----------+
| 2138314382288:1064:2138320015896 | NULL | TABLE | IX | GRANTED | NULL |
| 2138314382288:2:5:4:2138303895576 | ind_key | RECORD | X | GRANTED | 12, 12 |
| 2138314382288:2:5:5:2138303895576 | ind_key | RECORD | X | GRANTED | 18, 18 |
| 2138314382288:2:4:4:2138303895920 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 12 |
+-----------------------------------+------------+-----------+---------------+-------------+-----------+
可以看到除有表级的意向排它锁(IX),还加了key_id=12,18的临键锁,以及所访问到的主键id=12的记录锁。
PS:这里注意下为何key_id=18的临键锁没有被优化成间隙锁可以说是MySQL加锁的一个bug,非主键唯一索引在范围查询的时候并没有做优化,和普通索引一样。
如果条件换成key_id >=7 and key_id<=18会有不一样的bug,因为此时会获得key_id=7,12,18,23的临键锁,正常来说应该是key_id=7的记录锁,key_id=12,18的临键锁的,其并没有按主键的加锁规则来优化。
将非主键唯一索引的范围查询和普通索引的范围查询的加锁作对比,发现其加锁规则一模一样。
3.普通索引
1:select * from test where num = 12 for update;
查询数据存在。
mysql> select ENGINE_LOCK_ID,INDEX_NAME, LOCK_TYPE,LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------------------------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_LOCK_ID | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------------------+------------+-----------+---------------+-------------+-----------+
| 2138314382288:1064:2138320015896 | NULL | TABLE | IX | GRANTED | NULL |
| 2138314382288:2:6:4:2138303895576 | ind_num | RECORD | X | GRANTED | 12, 12 |
| 2138314382288:2:6:5:2138303895576 | ind_num | RECORD | X | GRANTED | 12, 18 |
| 2138314382288:2:4:4:2138303895920 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 12 |
| 2138314382288:2:4:5:2138303895920 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 18 |
| 2138314382288:2:6:6:2138303896264 | ind_num | RECORD | X,GAP | GRANTED | 22, 23 |
+-----------------------------------+------------+-----------+---------------+-------------+-----------+
可以看到除了获的表级的意向排它锁(IX),还获取两条num=12的临键锁,以及第一个大于12的num值的间隙锁,即num=22,id=23,并不会去获取num=22,id=24的间隙锁。
加锁过程:1:找到num=12的第一条数据,获取其临键锁,以及对应id=12的记录锁;
2:继续向下查找,访问到的行都获取其临键锁以及对应主键的记录锁;
3:直至找到第一个num大于12的行,获取其间隙锁,结束。
2:select * from test where num = 18 for update;
查询数据不存在。
mysql> select ENGINE_LOCK_ID,INDEX_NAME, LOCK_TYPE,LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------------------------------+------------+-----------+-----------+-------------+-----------+
| ENGINE_LOCK_ID | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------------------+------------+-----------+-----------+-------------+-----------+
| 2138314382288:1064:2138320015896 | NULL | TABLE | IX | GRANTED | NULL |
| 2138314382288:2:6:6:2138303895576 | ind_num | RECORD | X,GAP | GRANTED | 22, 23 |
+-----------------------------------+------------+-----------+-----------+-------------+-----------+
可以看到除了获的表级的意向排它锁(IX),还获取了第一个大于18的num值的间隙锁,即num=22,id=23。
加锁过程:1:找到num=18的第一条数据,找不到;
2:继续向下查找,直至找到第一个num大于18的行,获取其间隙锁,结束。
3:select * from test where num >7 and num <17 for update;
查询范围。
mysql> select ENGINE_LOCK_ID,INDEX_NAME, LOCK_TYPE,LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------------------------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_LOCK_ID | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------------------+------------+-----------+---------------+-------------+-----------+
| 2138314382288:1064:2138320015896 | NULL | TABLE | IX | GRANTED | NULL |
| 2138314382288:2:6:4:2138303895576 | ind_num | RECORD | X | GRANTED | 12, 12 |
| 2138314382288:2:6:5:2138303895576 | ind_num | RECORD | X | GRANTED | 12, 18 |
| 2138314382288:2:6:6:2138303895576 | ind_num | RECORD | X | GRANTED | 22, 23 |
| 2138314382288:2:4:4:2138303895920 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 12 |
| 2138314382288:2:4:5:2138303895920 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 18 |
+-----------------------------------+------------+-----------+---------------+-------------+-----------+
可以看到除了获的表级的意向排它锁(IX),还获取了num=12,22的临键锁,以及所访问的行主键id=12,18的记录锁。
加锁过程:1:找到第一个num大于7的行,获取其临键锁(num=12,id=12),以及对应id=12的记录锁;
2:继续向下查找,访问到的行都获取其临键锁以及对应主键的记录锁;
3:直至找到第一个num大于等于17的行,获取其临键锁(num=22,id=23),结束。
4:最终锁定范围是((7,7),(22,23)]
因为普通索引的值是可以重复的,所以此时对于num=7,id<7和num=22,id>23这样的边缘值是可以正常插入的
思考1:如何查询条件是num >7 and num <=22 会如何??
区别在第三步,直至找到第一个num大于22的行,获取其临键锁,结束。
思考2:为何找到第一个num大于等于17的行,获取其临键锁而不是间隙锁??
??猜测是因为普通索引的值是可重复的,貌似也不能自圆其说,有时间看看源码吧
总结
通过前面的分析可以得出,加锁有以下规则:
- 加锁的基本单位是临键锁,要注意主键和非主键的临键锁范围表示不同,主键(id1,id2],非主键((key1,id1),(key2,id2)];
- 对于非主键,除了获取自身的临键锁,还会获取访问到的行的主键的记录锁;
- 对于唯一索引,等值查询,存在时临键锁会被优化成记录锁,不存在时向下遍历第一个不等于的值获取其临键锁,并会被优化成间隙锁;
- 对于普通索引,等值查询,除了获取等值的临键锁,还会向下遍历到第一个不等于的值获取其临键锁,不过会被优化成间隙锁,不存在时向下遍历第一个不等于的值获取其临键锁,并会被优化成间隙锁;
- 对于主键索引,范围查询遵循唯一索引等值查询的优化规则;
- 对于非主键唯一索引和普通索引,范围查询遵循普通索引等值查询的优化规则;
- 非主键唯一索引范围查询是有bug的,见MySQL45讲