MySQL RR隔离级别锁详细分析
一、基本概念
1.隔离级别
- READ UNCOMMITED
- READ COMMITTED
◦ Oracle、DB2、Microsoft SQL Server （默认）
◦ 解决脏读 - REPEATABLE READ
◦ InnoDB（默认）
◦ 解决脏读、不可重复读
◦ InnoDB中的RR解决了幻读问题 - SERIALIZABLE
◦ 解决脏读、不可重复读和幻读
2.锁的算法
- Record Lock：单个行记录上的锁
- Gap Lock：锁定一个范围，但不包含记录本身
- Next-Key Lock：Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身
3.锁与并发
- locking （锁）
- concurrency control （并发控制）
- isolation （隔离级别）
- serializability （序列化）
锁是用来实现并发控制， 并发控制用来实现隔离级别， 隔离级别是通过锁来控制的， 锁的目的为了使得事物之间的执行是序列化。
注：在分析锁的实现时，请确保已了解上述概念。
二、实验演示分析，均为RR隔离级别
创建一张表，插入数据：
CREATE TABLE `l` (
`a` int(11) NOT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`a`),
UNIQUE KEY `b` (`b`),
KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `l` VALUES (5,5,5,5),(10,10,10,10),(15,15,15,15),(20,20,20,20),(25,25,25,25),(30,30,30,30);
1.主键等值查询：
会话1：
MySQL root@10.0.0.51:test> set tx_isolation='repeatable-read';
Query OK, 0 rows affected
Time: 0.000s
MySQL root@10.0.0.51:test> begin;
Query OK, 0 rows affected
Time: 0.000s
MySQL root@10.0.0.51:test> select * from l where a=15 for update;
+----+----+----+----+
| a | b | c | d |
+----+----+----+----+
| 15 | 15 | 15 | 15 |
+----+----+----+----+
1 row in set
Time: 0.006s
会话2：
MySQL root@10.0.0.51:(none)> set global innodb_status_output_locks=1;
Query OK, 0 rows affected
Time: 0.001s
MySQL root@10.0.0.51:(none)> show engine innodb status\G
----------省略部分输出----------
---TRANSACTION 6974, ACTIVE 90 sec
2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 14, OS thread handle 0x7fdfc8c19700, query id 242 10.0.0.51 root cleaning up
TABLE LOCK table `test`.`l` trx id 6974 lock mode IX
RECORD LOCKS space id 11 page no 3 n bits 80 index `PRIMARY` of table `test`.`l` trx id 6974 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000f; asc ;; --表示主键a，4个字节，hex 8000000f表示十进制15
1: len 6; hex 000000001b13; asc ;; --6个字节事务id
2: len 7; hex 90000001560110; asc V ;; --固定7个字节回滚指针
3: len 4; hex 8000000f; asc ;; --表示b列，hex 8000000f表示十进制15
4: len 4; hex 8000000f; asc ;; --表示c列，hex 8000000f表示十进制15
5: len 4; hex 8000000f; asc ;; --表示d列，hex 8000000f表示十进制15
-- TABLE LOCK table `test`.`l` trx id 6974 lock mode IX:表示l表上有一个IX锁，锁定一条记录时，需要在上一级，也就是表上加IX锁
-- index `PRIMARY` of table `test`.`l`：表示锁加在主键索引上
-- lock_mode X locks rec but not gap：表示是行锁
-- Record lock : 表示是锁住的记录
-- heap no 4 PHYSICAL RECORD: n_fields 6 : 表示锁住记录的heap no 为4的物理记录，由6个列组成
-- compact format : 表示这条记录存储的格式（Dynamic其实也是compact的格式）
-- info bits : 0 -- 表示这条记录没有被删除; 非0 -- 表示被修改或者被删除（32）
-- 输出上述信息的前提是 innodb_status_output_locks = 1
-- 可在配置文件中设置打开，不会影响运行时的性能
-- 只有在show engine innodb status时才会使用
加锁范围：主键索引a=15 record lock
加锁分析：找到主键a=15，加上next key lock，但是唯一索引等值查询会优化为record lock，因此只锁住a=15
2.唯一索引等值查询
会话1：
MySQL root@10.0.0.51:test> begin;
Query OK, 0 rows affected
Time: 0.000s
MySQL root@10.0.0.51:test> select * from l where b=15 for update;
+----+----+----+----+
| a | b | c | d |
+----+----+----+----+
| 15 | 15 | 15 | 15 |
+----+----+----+----+
1 row in set
Time: 0.006s
会话2：
---TRANSACTION 6975, ACTIVE 4 sec
3 lock struct(s), heap size 360, 2 row lock(s)
MySQL thread id 14, OS thread handle 0x7fdfc8c19700, query id 248 10.0.0.51 root cleaning up
TABLE LOCK table `test`.`l` trx id 6975 lock mode IX
RECORD LOCKS space id 11 page no 4 n bits 80 index `b` of table `test`.`l` trx id 6975 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000f; asc ;;
1: len 4; hex 8000000f; asc ;;
RECORD LOCKS space id 11 page no 3 n bits 80 index `PRIMARY` of table `test`.`l` trx id 6975 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000f; asc ;;
1: len 6; hex 000000001b13; asc ;;
2: len 7; hex 90000001560110; asc V ;;
3: len 4; hex 8000000f; asc ;;
4: len 4; hex 8000000f; asc ;;
5: len 4; hex 8000000f; asc ;;
加锁范围：唯一索引b=15和主键索引a=15 record lock
加锁分析：找到唯一索引b=15，加上next key lock，但是唯一索引等值查询会优化为record lock，因此只锁住b=15，回表会范围主键索引a=15这一行，因此加上record lock。
3.普通索引等值查询
会话1：
MySQL root@10.0.0.51:test> begin;
Query OK, 0 rows affected
Time: 0.000s
MySQL root@10.0.0.51:test> select * from l where c=15 for update;
+----+----+----+----+
| a | b | c | d |
+----+----+----+----+
| 15 | 15 | 15 | 15 |
+----+----+----+----+
1 row in set
Time: 0.006s
会话2：
---TRANSACTION 6976, ACTIVE 3 sec
4 lock struct(s), heap size 1184, 3 row lock(s)
MySQL thread id 14, OS thread handle 0x7fdfc8c19700, query id 252 10.0.0.51 root cleaning up
TABLE LOCK table `test`.`l` trx id 6976 lock mode IX
RECORD LOCKS space id 11 page no 5 n bits 80 index `c` of table `test`.`l` trx id 6976 lock_mode X
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000f; asc ;;
1: len 4; hex 8000000f; asc ;;
RECORD LOCKS space id 11 page no 3 n bits 80 index `PRIMARY` of table `test`.`l` trx id 6976 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000f; asc ;;
1: len 6; hex 000000001b13; asc ;;
2: len 7; hex 90000001560110; asc V ;;
3: len 4; hex 8000000f; asc ;;
4: len 4; hex 8000000f; asc ;;
5: len 4; hex 8000000f; asc ;;
RECORD LOCKS space id 11 page no 5 n bits 80 index `c` of table `test`.`l` trx id 6976 lock_mode X locks gap before rec
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000014; asc ;;
1: len 4; hex 80000014; asc ;;
加锁范围：普通索引c=15有next-key lock,普通索引c=20上有gap lock，主键索引a=15有record lock
加锁分析：找到普通索引c=15，加上next key lock，因为是普通索引，a无法保证唯一性约束，会接着访问c=20这一行，等值判断，向右遍历，最后一个值不满足c=15这个等值条件，此时，加锁范围会进行优化，next key lock会退化成间隙锁(15,20)，接着会访问主键索引a=15，加上record lock。
4.无索引等值查询
会话1：
MySQL root@10.0.0.51:test> begin;
Query OK, 0 rows affected
Time: 0.000s
MySQL root@10.0.0.51:test> select * from l where d=15 for update;
+----+----+----+----+
| a | b | c | d |
+----+----+----+----+
| 15 | 15 | 15 | 15 |
+----+----+----+----+
1 row in set
Time: 0.006s
会话2：
---TRANSACTION 6977, ACTIVE 25 sec
2 lock struct(s), heap size 360, 7 row lock(s)
MySQL thread id 14, OS thread handle 0x7fdfc8c19700, query id 256 10.0.0.51 root cleaning up
TABLE LOCK table `test`.`l` trx id 6977 lock mode IX
RECORD LOCKS space id 11 page no 3 n bits 80 index `PRIMARY` of table `test`.`l` trx id 6977 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 6; hex 000000001b0d; asc ;;
2: len 7; hex 8c0000013e0110; asc > ;;
3: len 4; hex 80000005; asc ;;
4: len 4; hex 80000005; asc ;;
5: len 4; hex 80000005; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000001b0e; asc ;;
2: len 7; hex 8d000001550110; asc U ;;
3: len 4; hex 8000000a; asc ;;
4: len 4; hex 8000000a; asc ;;
5: len 4; hex 8000000a; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000f; asc ;;
1: len 6; hex 000000001b13; asc ;;
2: len 7; hex 90000001560110; asc V ;;
3: len 4; hex 8000000f; asc ;;
4: len 4; hex 8000000f; asc ;;
5: len 4; hex 8000000f; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000014; asc ;;
1: len 6; hex 000000001b14; asc ;;
2: len 7; hex 91000001570110; asc W ;;
3: len 4; hex 80000014; asc ;;
4: len 4; hex 80000014; asc ;;
5: len 4; hex 80000014; asc ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000019; asc ;;
1: len 6; hex 000000001b19; asc ;;
2: len 7; hex 94000001480110; asc H ;;
3: len 4; hex 80000019; asc ;;
4: len 4; hex 80000019; asc ;;
5: len 4; hex 80000019; asc ;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000001e; asc ;;
1: len 6; hex 000000001b33; asc 3;;
2: len 7; hex ad0000015a0110; asc Z ;;
3: len 4; hex 8000001e; asc ;;
4: len 4; hex 8000001e; asc ;;
5: len 4; hex 8000001e; asc ;;
加锁范围:主键索引(-∞,5],(5,10],(10,15],(15,20],(20,25],(25,30],(30,+∞]，类似于表锁的效果
加锁分析：由于where条件列没有索引，会直接访问主键进行全表扫描，此时会对表中每一行的record和gap依次加锁。
总结：
- RR隔离级别的加锁单位默认为Next-Key Lock
- 查找过程中访问到的对象才会加锁
- 对唯一索引的等值查询Next-Key Lock退化为行锁
- 二级索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁
- 二级索引上加了Record Lock 或Next-Key Lock ，则对应的主键加Record Lock ；
- 二级索引加Gap Lock ，则对应的主键不加锁；
- 在RR 的隔离级别下，当查询的列没有索引时，会锁住所有记录
5.唯一索引范围查询
查询条件为 >
会话1：
MySQL root@10.0.0.51:test> begin;
Query OK, 0 rows affected
Time: 0.000s
MySQL root@10.0.0.51:test> select * from l where a>15 for update;
+----+----+----+----+
| a | b | c | d |
+----+----+----+----+
| 20 | 20 | 20 | 20 |
| 25 | 25 | 25 | 25 |
| 30 | 30 | 30 | 30 |
+----+----+----+----+
3 rows in set
Time: 0.006s
会话2：
---TRANSACTION 6995, ACTIVE 3 sec
2 lock struct(s), heap size 360, 4 row lock(s)
MySQL thread id 14, OS thread handle 0x7fdfc8c19700, query id 340 10.0.0.51 root cleaning up
TABLE LOCK table `test`.`l` trx id 6995 lock mode IX
RECORD LOCKS space id 11 page no 3 n bits 80 index `PRIMARY` of table `test`.`l` trx id 6995 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000014; asc ;;
1: len 6; hex 000000001b14; asc ;;
2: len 7; hex 91000001570110; asc W ;;
3: len 4; hex 80000014; asc ;;
4: len 4; hex 80000014; asc ;;
5: len 4; hex 80000014; asc ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000019; asc ;;
1: len 6; hex 000000001b19; asc ;;
2: len 7; hex 94000001480110; asc H ;;
3: len 4; hex 80000019; asc ;;
4: len 4; hex 80000019; asc ;;
5: len 4; hex 80000019; asc ;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000001e; asc ;;
1: len 6; hex 000000001b33; asc 3;;
2: len 7; hex ad0000015a0110; asc Z ;;
3: len 4; hex 8000001e; asc ;;
4: len 4; hex 8000001e; asc ;;
5: len 4; hex 8000001e; asc ;;
加锁范围：主键索引(15,20],(20,25],(25,30],(30,+∞]
加锁分析：根据主键找到大于15的第一条记录a=20，加上next key lock，向右遍历，依次加锁，直到找到最后一条记录a=30。
查询条件为 >=
会话1：
MySQL root@10.0.0.51:test> begin;
Query OK, 0 rows affected
Time: 0.000s
MySQL root@10.0.0.51:test> select * from l where a>=15 for update;
+----+----+----+----+
| a | b | c | d |
+----+----+----+----+
| 15 | 15 | 15 | 15 |
| 20 | 20 | 20 | 20 |
| 25 | 25 | 25 | 25 |
| 30 | 30 | 30 | 30 |
+----+----+----+----+
4 rows in set
Time: 0.007s
会话2：
---TRANSACTION 6996, ACTIVE 3 sec
3 lock struct(s), heap size 360, 5 row lock(s)
MySQL thread id 14, OS thread handle 0x7fdfc8c19700, query id 344 10.0.0.51 root cleaning up
TABLE LOCK table `test`.`l` trx id 6996 lock mode IX
RECORD LOCKS space id 11 page no 3 n bits 80 index `PRIMARY` of table `test`.`l` trx id 6996 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000f; asc ;;
1: len 6; hex 000000001b13; asc ;;
2: len 7; hex 90000001560110; asc V ;;
3: len 4; hex 8000000f; asc ;;
4: len 4; hex 8000000f; asc ;;
5: len 4; hex 8000000f; asc ;;
RECORD LOCKS space id 11 page no 3 n bits 80 index `PRIMARY` of table `test`.`l` trx id 6996 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000014; asc ;;
1: len 6; hex 000000001b14; asc ;;
2: len 7; hex 91000001570110; asc W ;;
3: len 4; hex 80000014; asc ;;
4: len 4; hex 80000014; asc ;;
5: len 4; hex 80000014; asc ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000019; asc ;;
1: len 6; hex 000000001b19; asc ;;
2: len 7; hex 94000001480110; asc H ;;
3: len 4; hex 80000019; asc ;;
4: len 4; hex 80000019; asc ;;
5: len 4; hex 80000019; asc ;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000001e; asc ;;
1: len 6; hex 000000001b33; asc 3;;
2: len 7; hex ad0000015a0110; asc Z ;;
3: len 4; hex 8000001e; asc ;;
4: len 4; hex 8000001e; asc ;;
5: len 4; hex 8000001e; asc ;;
加锁范围：主键索引[15,20],(20,25],(25,30],(30,+∞],此时15上有record lock
加锁分析：根据主键找到a=15，加上next key lock，但是，根据优化，唯一索引等值查询退化为行锁，因此，a=15只加record lock，向右遍历每条记录，依次加next key lock，直到找到最后一条记录a=30。
查询条件为 <
会话1：
MySQL root@10.0.0.51:test> begin;
Query OK, 0 rows affected
Time: 0.001s
MySQL root@10.0.0.51:test> select * from l where a<15 for update;
+----+----+----+----+
| a | b | c | d |
+----+----+----+----+
| 5 | 5 | 5 | 5 |
| 10 | 10 | 10 | 10 |
+----+----+----+----+
2 rows in set
Time: 0.006s
会话2：
---TRANSACTION 6998, ACTIVE 3 sec
2 lock struct(s), heap size 360, 3 row lock(s)
MySQL thread id 14, OS thread handle 0x7fdfc8c19700, query id 352 10.0.0.51 root cleaning up
TABLE LOCK table `test`.`l` trx id 6998 lock mode IX
RECORD LOCKS space id 11 page no 3 n bits 80 index `PRIMARY` of table `test`.`l` trx id 6998 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 6; hex 000000001b0d; asc ;;
2: len 7; hex 8c0000013e0110; asc > ;;
3: len 4; hex 80000005; asc ;;
4: len 4; hex 80000005; asc ;;
5: len 4; hex 80000005; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000001b0e; asc ;;
2: len 7; hex 8d000001550110; asc U ;;
3: len 4; hex 8000000a; asc ;;
4: len 4; hex 8000000a; asc ;;
5: len 4; hex 8000000a; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000f; asc ;;
1: len 6; hex 000000001b13; asc ;;
2: len 7; hex 90000001560110; asc V ;;
3: len 4; hex 8000000f; asc ;;
4: len 4; hex 8000000f; asc ;;
5: len 4; hex 8000000f; asc ;;
加锁范围：主键索引（-∞,5],(5,10],(10,15]
加锁分析：根据主键找到最小的一条记录a=5，加next key lock，依次向右遍历每一条记录，直到找到第一条不满足a<15的记录a=15。
查询条件为 <=
会话1：
MySQL root@10.0.0.51:test> begin;
Query OK, 0 rows affected
Time: 0.000s
MySQL root@10.0.0.51:test> select * from l where a<=15 for update;
+----+----+----+----+
| a | b | c | d |
+----+----+----+----+
| 5 | 5 | 5 | 5 |
| 10 | 10 | 10 | 10 |
| 15 | 15 | 15 | 15 |
+----+----+----+----+
3 rows in set
Time: 0.007s
会话2：
---TRANSACTION 6999, ACTIVE 46 sec
2 lock struct(s), heap size 360, 4 row lock(s)
MySQL thread id 14, OS thread handle 0x7fdfc8c19700, query id 356 10.0.0.51 root cleaning up
TABLE LOCK table `test`.`l` trx id 6999 lock mode IX
RECORD LOCKS space id 11 page no 3 n bits 80 index `PRIMARY` of table `test`.`l` trx id 6999 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 6; hex 000000001b0d; asc ;;
2: len 7; hex 8c0000013e0110; asc > ;;
3: len 4; hex 80000005; asc ;;
4: len 4; hex 80000005; asc ;;
5: len 4; hex 80000005; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000001b0e; asc ;;
2: len 7; hex 8d000001550110; asc U ;;
3: len 4; hex 8000000a; asc ;;
4: len 4; hex 8000000a; asc ;;
5: len 4; hex 8000000a; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000f; asc ;;
1: len 6; hex 000000001b13; asc ;;
2: len 7; hex 90000001560110; asc V ;;
3: len 4; hex 8000000f; asc ;;
4: len 4; hex 8000000f; asc ;;
5: len 4; hex 8000000f; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000014; asc ;;
1: len 6; hex 000000001b14; asc ;;
2: len 7; hex 91000001570110; asc W ;;
3: len 4; hex 80000014; asc ;;
4: len 4; hex 80000014; asc ;;
5: len 4; hex 80000014; asc ;;
加锁范围：主键索引（-∞,5],(5,10],(10,15],(15,20]
加锁分析：根据主键找到最小的一条记录a=5，加next key lock，依次向右遍历每一条记录，直到找到第一条不满足a<=15的记录a=20。但此时其实只需要找到a=15的记录即可，并不需要找到a=20，因为是唯一索引，具有唯一性约束，a=15的下一条记录一定不满足条件，(15,20]的next key lock可以不加，按照丁奇的说法，这算是唯一索引范围查询的一个bug。
查询条件为 > and <
会话1：
MySQL root@10.0.0.51:test> begin;
Query OK, 0 rows affected
Time: 0.000s
MySQL root@10.0.0.51:test> select * from l where a>10 and a<20 for update;
+----+----+----+----+
| a | b | c | d |
+----+----+----+----+
| 15 | 15 | 15 | 15 |
+----+----+----+----+
1 row in set
Time: 0.006s
会话2：
---TRANSACTION 0, not started
MySQL thread id 17, OS thread handle 0x7fdfc8c5b700, query id 452 10.0.0.51 root init
show engine innodb status
---TRANSACTION 7021, ACTIVE 3 sec
2 lock struct(s), heap size 360, 2 row lock(s)
MySQL thread id 14, OS thread handle 0x7fdfc8c19700, query id 451 10.0.0.51 root cleaning up
TABLE LOCK table `test`.`l` trx id 7021 lock mode IX
RECORD LOCKS space id 11 page no 3 n bits 80 index `PRIMARY` of table `test`.`l` trx id 7021 lock_mode X
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000f; asc ;;
1: len 6; hex 000000001b13; asc ;;
2: len 7; hex 90000001560110; asc V ;;
3: len 4; hex 8000000f; asc ;;
4: len 4; hex 8000000f; asc ;;
5: len 4; hex 8000000f; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000014; asc ;;
1: len 6; hex 000000001b14; asc ;;
2: len 7; hex 91000001570110; asc W ;;
3: len 4; hex 80000014; asc ;;
4: len 4; hex 80000014; asc ;;
5: len 4; hex 80000014; asc ;;
加锁范围：主键索引(10,15],(15,20]
加锁分析：根据主键找到a>10的第一条记录a=15，加next key lock，依次向右遍历每一条记录，直到找到第一条不满足a<20的记录a=20。
查询条件为 > and <=
会话1：
MySQL root@10.0.0.51:test> begin;
Query OK, 0 rows affected
Time: 0.001s
MySQL root@10.0.0.51:test> select * from l where a>10 and a<=20 for update;
+----+----+----+----+
| a | b | c | d |
+----+----+----+----+
| 15 | 15 | 15 | 15 |
| 20 | 20 | 20 | 20 |
+----+----+----+----+
2 rows in set
Time: 0.006s
会话2：
---TRANSACTION 7022, ACTIVE 2 sec
2 lock struct(s), heap size 360, 3 row lock(s)
MySQL thread id 14, OS thread handle 0x7fdfc8c19700, query id 455 10.0.0.51 root cleaning up
TABLE LOCK table `test`.`l` trx id 7022 lock mode IX
RECORD LOCKS space id 11 page no 3 n bits 80 index `PRIMARY` of table `test`.`l` trx id 7022 lock_mode X
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000f; asc ;;
1: len 6; hex 000000001b13; asc ;;
2: len 7; hex 90000001560110; asc V ;;
3: len 4; hex 8000000f; asc ;;
4: len 4; hex 8000000f; asc ;;
5: len 4; hex 8000000f; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000014; asc ;;
1: len 6; hex 000000001b14; asc ;;
2: len 7; hex 91000001570110; asc W ;;
3: len 4; hex 80000014; asc ;;
4: len 4; hex 80000014; asc ;;
5: len 4; hex 80000014; asc ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000019; asc ;;
1: len 6; hex 000000001b19; asc ;;
2: len 7; hex 94000001480110; asc H ;;
3: len 4; hex 80000019; asc ;;
4: len 4; hex 80000019; asc ;;
5: len 4; hex 80000019; asc ;;
加锁范围：主键索引(10,15],(15,20],(20,25]
加锁分析：根据主键找到a>10的第一条记录a=15，加next key lock，依次向右遍历每一条记录，直到找到第一条不满足a<=20的记录a=25。唯一索引范围查询，会找到访问到不满足条件的第一个值为止，这算是一个bug。
查询条件为 >= and <=
会话1：
MySQL root@10.0.0.51:test> begin;
Query OK, 0 rows affected
Time: 0.001s
MySQL root@10.0.0.51:test> select * from l where a>=10 and a<=20 for update;
+----+----+----+----+
| a | b | c | d |
+----+----+----+----+
| 10 | 10 | 10 | 10 |
| 15 | 15 | 15 | 15 |
| 20 | 20 | 20 | 20 |
+----+----+----+----+
3 rows in set
Time: 0.006s
会话2：
---TRANSACTION 7023, ACTIVE 3 sec
3 lock struct(s), heap size 360, 4 row lock(s)
MySQL thread id 14, OS thread handle 0x7fdfc8c19700, query id 459 10.0.0.51 root cleaning up
TABLE LOCK table `test`.`l` trx id 7023 lock mode IX
RECORD LOCKS space id 11 page no 3 n bits 80 index `PRIMARY` of table `test`.`l` trx id 7023 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000001b0e; asc ;;
2: len 7; hex 8d000001550110; asc U ;;
3: len 4; hex 8000000a; asc ;;
4: len 4; hex 8000000a; asc ;;
5: len 4; hex 8000000a; asc ;;
RECORD LOCKS space id 11 page no 3 n bits 80 index `PRIMARY` of table `test`.`l` trx id 7023 lock_mode X
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000f; asc ;;
1: len 6; hex 000000001b13; asc ;;
2: len 7; hex 90000001560110; asc V ;;
3: len 4; hex 8000000f; asc ;;
4: len 4; hex 8000000f; asc ;;
5: len 4; hex 8000000f; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000014; asc ;;
1: len 6; hex 000000001b14; asc ;;
2: len 7; hex 91000001570110; asc W ;;
3: len 4; hex 80000014; asc ;;
4: len 4; hex 80000014; asc ;;
5: len 4; hex 80000014; asc ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000019; asc ;;
1: len 6; hex 000000001b19; asc ;;
2: len 7; hex 94000001480110; asc H ;;
3: len 4; hex 80000019; asc ;;
4: len 4; hex 80000019; asc ;;
5: len 4; hex 80000019; asc ;;
加锁范围：主键索引(5,10],(10,15],(15,20],(20,25]
加锁分析：根据主键找到a>=10的第一条记录a=10，加next key lock，依次向右遍历每一条记录，直到找到第一条不满足a<=20的记录a=25。唯一索引范围查询，会找到访问到不满足条件的第一个值为止，这算是一个bug。
查询条件为 >= and <= order by desc
会话1：
MySQL root@10.0.0.51:test> begin;
Query OK, 0 rows affected
Time: 0.000s
MySQL root@10.0.0.51:test> select * from l where a>=10 and a<=20 order by a desc for update;
+----+----+----+----+
| a | b | c | d |
+----+----+----+----+
| 20 | 20 | 20 | 20 |
| 15 | 15 | 15 | 15 |
| 10 | 10 | 10 | 10 |
+----+----+----+----+
3 rows in set
Time: 0.006s
会话2：
---TRANSACTION 7024, ACTIVE 4 sec
3 lock struct(s), heap size 360, 5 row lock(s)
MySQL thread id 14, OS thread handle 0x7fdfc8c19700, query id 463 10.0.0.51 root cleaning up
TABLE LOCK table `test`.`l` trx id 7024 lock mode IX
RECORD LOCKS space id 11 page no 3 n bits 80 index `PRIMARY` of table `test`.`l` trx id 7024 lock_mode X locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000019; asc ;;
1: len 6; hex 000000001b19; asc ;;
2: len 7; hex 94000001480110; asc H ;;
3: len 4; hex 80000019; asc ;;
4: len 4; hex 80000019; asc ;;
5: len 4; hex 80000019; asc ;;
RECORD LOCKS space id 11 page no 3 n bits 80 index `PRIMARY` of table `test`.`l` trx id 7024 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 6; hex 000000001b0d; asc ;;
2: len 7; hex 8c0000013e0110; asc > ;;
3: len 4; hex 80000005; asc ;;
4: len 4; hex 80000005; asc ;;
5: len 4; hex 80000005; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000001b0e; asc ;;
2: len 7; hex 8d000001550110; asc U ;;
3: len 4; hex 8000000a; asc ;;
4: len 4; hex 8000000a; asc ;;
5: len 4; hex 8000000a; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000f; asc ;;
1: len 6; hex 000000001b13; asc ;;
2: len 7; hex 90000001560110; asc V ;;
3: len 4; hex 8000000f; asc ;;
4: len 4; hex 8000000f; asc ;;
5: len 4; hex 8000000f; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000014; asc ;;
1: len 6; hex 000000001b14; asc ;;
2: len 7; hex 91000001570110; asc W ;;
3: len 4; hex 80000014; asc ;;
4: len 4; hex 80000014; asc ;;
5: len 4; hex 80000014; asc ;;
加锁范围：主键索引(-∞,5],(5,10],(10,15],(15,20],(20,25）,注意此时25上为gap lock
加锁分析：由于是order by主键desc，所以先找到a=20，加next key lock(15,20]和gap lock(20,25），向左遍历，找到a=5停下来。
总结：
- 唯一索引上的范围查询会访问到不满足条件的第一个值为止。
6.普通索引范围查询
执行计划使用了普通索引
会话1：
MySQL root@10.0.0.51:test> begin;
Query OK, 0 rows affected
Time: 0.000s
MySQL root@10.0.0.51:test> select * from l where c<15 for update;
+----+----+----+----+
| a | b | c | d |
+----+----+----+----+
| 5 | 5 | 5 | 5 |
| 10 | 10 | 10 | 10 |
+----+----+----+----+
2 rows in set
Time: 0.005s
MySQL root@10.0.0.51:test> explain select * from l where c<15 for update;
+----+-------------+-------+-------+---------------+-----+---------+--------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-----+---------+--------+------+-----------------------+
| 1 | SIMPLE | l | range | c | c | 5 | <null> | 1 | Using index condition |
+----+-------------+-------+-------+---------------+-----+---------+--------+------+-----------------------+
会话2：
---TRANSACTION 7030, ACTIVE 47 sec
3 lock struct(s), heap size 360, 5 row lock(s)
MySQL thread id 23, OS thread handle 0x7fdfc8c9d700, query id 491 10.0.0.51 root cleaning up
TABLE LOCK table `test`.`l` trx id 7030 lock mode IX
RECORD LOCKS space id 11 page no 5 n bits 80 index `c` of table `test`.`l` trx id 7030 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 4; hex 80000005; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 4; hex 8000000a; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000f; asc ;;
1: len 4; hex 8000000f; asc ;;
RECORD LOCKS space id 11 page no 3 n bits 80 index `PRIMARY` of table `test`.`l` trx id 7030 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 6; hex 000000001b0d; asc ;;
2: len 7; hex 8c0000013e0110; asc > ;;
3: len 4; hex 80000005; asc ;;
4: len 4; hex 80000005; asc ;;
5: len 4; hex 80000005; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000001b0e; asc ;;
2: len 7; hex 8d000001550110; asc U ;;
3: len 4; hex 8000000a; asc ;;
4: len 4; hex 8000000a; asc ;;
5: len 4; hex 8000000a; asc ;;
加锁范围：二级索引c(-∞,5],(5,10],(10,15]，主键索引a=5和a=10 record lock
加锁分析：根据二级索引c找到最小的一条记录c=5，加next key lock，依次向右遍历每一条记录，直到找到第一条不满足c<15的记录c=15,接着根据主键a找到a=5和a=10的两条记录。
执行计划使用主键索引扫描，没有用到二级索引
会话1：
MySQL root@10.0.0.51:test> begin;
Query OK, 0 rows affected
Time: 0.001s
MySQL root@10.0.0.51:test> select * from l where c<25 for update;
+----+----+----+----+
| a | b | c | d |
+----+----+----+----+
| 5 | 5 | 5 | 5 |
| 10 | 10 | 10 | 10 |
| 15 | 15 | 15 | 15 |
| 20 | 20 | 20 | 20 |
+----+----+----+----+
4 rows in set
Time: 0.005s
MySQL root@10.0.0.51:test> explain select * from l where c<25 for update;
+----+-------------+-------+------+---------------+--------+---------+--------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+--------+------+-------------+
| 1 | SIMPLE | l | ALL | c | <null> | <null> | <null> | 6 | Using where |
+----+-------------+-------+------+---------------+--------+---------+--------+------+-------------+
1 row in set
Time: 0.006s
会话2：
---TRANSACTION 7031, ACTIVE 60 sec
2 lock struct(s), heap size 360, 7 row lock(s)
MySQL thread id 23, OS thread handle 0x7fdfc8c9d700, query id 497 10.0.0.51 root cleaning up
TABLE LOCK table `test`.`l` trx id 7031 lock mode IX
RECORD LOCKS space id 11 page no 3 n bits 80 index `PRIMARY` of table `test`.`l` trx id 7031 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 6; hex 000000001b0d; asc ;;
2: len 7; hex 8c0000013e0110; asc > ;;
3: len 4; hex 80000005; asc ;;
4: len 4; hex 80000005; asc ;;
5: len 4; hex 80000005; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000001b0e; asc ;;
2: len 7; hex 8d000001550110; asc U ;;
3: len 4; hex 8000000a; asc ;;
4: len 4; hex 8000000a; asc ;;
5: len 4; hex 8000000a; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000000f; asc ;;
1: len 6; hex 000000001b13; asc ;;
2: len 7; hex 90000001560110; asc V ;;
3: len 4; hex 8000000f; asc ;;
4: len 4; hex 8000000f; asc ;;
5: len 4; hex 8000000f; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000014; asc ;;
1: len 6; hex 000000001b14; asc ;;
2: len 7; hex 91000001570110; asc W ;;
3: len 4; hex 80000014; asc ;;
4: len 4; hex 80000014; asc ;;
5: len 4; hex 80000014; asc ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000019; asc ;;
1: len 6; hex 000000001b19; asc ;;
2: len 7; hex 94000001480110; asc H ;;
3: len 4; hex 80000019; asc ;;
4: len 4; hex 80000019; asc ;;
5: len 4; hex 80000019; asc ;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000001e; asc ;;
1: len 6; hex 000000001b33; asc 3;;
2: len 7; hex ad0000015a0110; asc Z ;;
3: len 4; hex 8000001e; asc ;;
4: len 4; hex 8000001e; asc ;;
5: len 4; hex 8000001e; asc ;;
加锁范围：主键(-∞,5],(5,10],(10,15],(15,20],(20,25],(25,30],(30,+∞]，因为执行计划没有使用二级索引c，因此进行全表扫描，主键上所有记录都加锁。
总结：
- 二级索引范围查询加锁看是否使用二级索引，根据过滤条件从左向右依次加锁，最后如果需要回表，对主键上的记录要加record lock。
- 没有使用二级索引，需要对主键上每一条记录和间隙加锁。
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· 使用C#创建一个MCP客户端
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型，支持深度思考和联网搜索！
· ollama系列1：轻松3步本地部署deepseek，普通电脑可用
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 按钮权限的设计及实现