Mysql中读提交隔离级别中的间隙锁定

Question

MYSQL VERSION : 5.7.X
STORAGE ENGINE : Innodb

我有一个大致的想法，读提交的隔离将主要使用共享和独占记录锁。但是，根据mysql的文档，在某些情况下，甚至读提交也必须使用间隙锁定。

阅读承诺..。对于锁定读取(选择用于更新或锁定共享模式)、UPDATE语句和DELETE语句，InnoDB只锁定索引记录，而不锁定前面的空白，从而允许在锁定的记录旁边自由插入新记录。间隙锁定只用于外键约束检查和重复键检查.

IMHO，只有记录锁就够了。有人能解释一下Gap锁定的场景吗?为什么mysql会这么做？

Answer 1

在read提交(RC)隔离级别下，间隙锁是必要的，以防止由于并发插入而可能发生的完整性冲突--这就是文档声明的内容。

间隙锁定只用于外键约束检查和重复键检查.

暗示。

假设事务T1与RC隔离一起更新作为唯一键(索引)一部分的列的值。显然，这只有在新的键值不存在的情况下才有可能。另外，假设事务T2 (也是RC隔离)尝试插入一个键值等于T1刚刚创建的键值的新记录。由于RC隔离级别，T2没有看到T1所做的更改，因为它还没有提交，因此不会引发重复的键错误。如果不是因为有问题的唯一索引的间隙锁定，这将最终导致两个记录，以相同的据称唯一的钥匙。

使用MySQL 8，您可以在使用performance_schema中的视图data_locks和data_lock_waits时看到这一点。我创建了这个示例表：

create table fruits (
  id int not null auto_increment primary key, 
  name varchar(10), 
  property varchar(20)
);
create unique index fruits_ux1 on fruits (name, property);
insert into fruits (name, property) 
values ('apple', 'red'), ('apple', 'tart'), 
       ('banana', 'yellow'), ('banana', 'sweet');

在事务1中，我更新香蕉属性：

T1> set transaction isolation level read committed;

T1> begin;

T1> update fruits set property = concat(property, '?') where name = 'banana';

我可以看到，具有新值的键条目对它们设置了间隙锁(在提交事务之前，这些行实际上并不存在，因此这里不能使用标准X锁)：

mysql> select thread_id, object_name, index_name, engine_lock_id, lock_type, lock_mode, lock_data  from performance_schema.data_locks;
+-----------+-------------+------------+---------------------------------------+-----------+---------------+------------------------+
| thread_id | object_name | index_name | engine_lock_id                        | lock_type | lock_mode     | lock_data              |
+-----------+-------------+------------+---------------------------------------+-----------+---------------+------------------------+
|        47 | fruits      | NULL       | 140491829069632:1066:140491735749880  | TABLE     | IX            | NULL                   |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:1:140491735746840 | RECORD    | X             | supremum pseudo-record |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:4:140491735746840 | RECORD    | X             | 'banana', 'yellow', 3  |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:5:140491735746840 | RECORD    | X             | 'banana', 'sweet', 4   |
|        47 | fruits      | PRIMARY    | 140491829069632:5:4:6:140491735747184 | RECORD    | X,REC_NOT_GAP | 4                      |
|        47 | fruits      | PRIMARY    | 140491829069632:5:4:7:140491735747184 | RECORD    | X,REC_NOT_GAP | 3                      |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:6:140491735747528 | RECORD    | X,GAP         | 'banana', 'sweet?', 4  |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:7:140491735747528 | RECORD    | X,GAP         | 'banana', 'yellow?', 3 |
+-----------+-------------+------------+---------------------------------------+-----------+---------------+------------------------+

在事务2中，我现在尝试插入一个可能的重复：

T2> set transaction isolation level read committed;

T2> begin;

T2> insert into fruits (name, property) values ('banana', 'sweet?');

语句阻塞，我可以看到它阻塞了这个唯一索引键的间隙锁(注意blocking_engine_lock_id值)：

mysql> select requesting_engine_lock_id, blocking_engine_lock_id from performance_schema.data_lock_waits;
+---------------------------------------+---------------------------------------+
| requesting_engine_lock_id             | blocking_engine_lock_id               |
+---------------------------------------+---------------------------------------+
| 140491829070536:5:5:6:140491735753104 | 140491829069632:5:5:6:140491735747872 |
+---------------------------------------+---------------------------------------+

类似地，如果T1从父表中删除一行，而T2试图向引用T1刚刚删除的行的子表插入新记录(它仍然会看到)，则外键索引上的间隙锁定将防止插入潜在的孤立记录。

Answer 2

从这里：

https://www.percona.com/blog/2012/03/27/innodbs-gap-locks/

间隙锁是索引记录之间差距的锁。由于这个间隙锁，当您两次运行相同的查询时，无论该表上的其他会话修改如何，您都会得到相同的结果。这使得读取一致，因此使服务器之间的复制保持一致。如果为UPDATE执行SELECT * FROM > 1000，您希望获得两次相同的值。为此，InnoDB用独占锁锁定WHERE子句找到的所有索引记录，并使用共享间隙锁锁定它们之间的间隙。