对于 Gap 锁的一些疑问

事务隔离级别为 RR 可重复读
表结构和数据如下

mysql> show create table test;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` int(11) NOT NULL,
`num` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `num` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> insert into test values (10, 10), (20, 20), (30, 30), (40, 40), (50, 50)
mysql> select * from test;
+----+-----+
| id | num |
+----+-----+
| 10 | 10 |
| 20 | 20 |
| 30 | 30 |
| 40 | 40 |
| 50 | 50 |
+----+-----+
5 rows in set (0.05 sec)

事务 A 的 sql 如下

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

mysql> select num from test where num > 10 and num < 15 for update;

事务 B 的 sql 如下
mysql> begin;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test values(120, 31);

此时事务 B 会阻塞，我理解事务 A 会添加 GAP 锁，我理解 GAP 的范围是[10,20]，为什么事务 B 会阻塞？？

num

MySQL

test

sec

9 条回复 • 2024-02-20 13:18:43 +08:00