InnoDB Locking

翻译 & 理解。

mysql v8.0 innodb locking

Shared and Exclusive Locks

InnoDB实现了两种标准的行锁(row-level locks)

  • 共享锁shared(S) locks)允许持有该锁的事务读取数据行;

  • 排它锁exclusive(X) locks)允许持有该锁的事务更新或删除数据行。

共享锁被称为共享的原因在于:多个事务可以同时持有共享锁,从而实现数据的并发读取。

【例1】针对同一行数据r行,假设事务T1已持有该行的共享锁,则事务T2的请求加锁结果如下:

  • 如果事务T2尝试获取该行的共享锁,则会立即生效,此时,事务T1与事务T2均持有r行的共享锁;
  • 如果事务T2尝试获取该行的排它锁,由于锁冲突,导致事务T2进入阻塞等待状态(直到事务T1释放锁)。

【例2】针对同一行数据r行,假设事务T1已经持有该行的排他锁,则事务T2无论请求共享锁还是排它锁都会进入阻塞等待状态(直到事务T1释放锁)。

Intention Locks

InnoDB实现了多粒度的锁(multiple granularity locking),用于支持行锁与表锁的共存。

意向锁(Intention Locks)表锁(table-level locks),用于表示事务稍后需要哪种类型的锁(共享的或排他的)来锁定表中的某一行。

  • 意向共享锁intention shared lock (IS))表示事务打算对表中的各个行设置共享锁
  • 意向排他锁intention exclusive lock (IX))表示事务打算对表中的各个行设置排他锁

意向锁与行锁的加锁规则

  • 如果事务希望获取某行的S锁(row-level locks)之前,必须先获取表上的IS锁(table-level locks)或更强的锁;
  • 如果事务希望获取某行的X锁(row-level locks)之前,必须先获取表上的IX锁(table-level locks)

意向锁与表锁之间的兼容规则

- X IX S IS
X Conflict Conflict Conflict Conflict
IX Conflict Compatible Conflict Compatible
S Conflict Conflict Compatible Compatible
IS Conflict Compatible Compatible Compatible

【例】如果事务T1已经持有表的IX锁,则事务T2获取不同表锁的结果如下:

  • 如果事务T2尝试获取X锁或S锁,则会造成锁冲突,事务T2进入阻塞等待(直到事务T1释放表的IX锁);
  • 如果事务T2尝试获取IX锁或IS锁,由于兼容规则,事务T2可以正常获取IX锁或IS锁

注意意向锁表锁行锁的兼容规则的不同。


以下为用于测试初始化的SQL语句,

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 建表
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `C`(`C`),
UNIQUE KEY `d` (`d`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

# 初始化
INSERT INTO `test`.`t`(`id`, `c`, `d`) VALUES (0, 0, 0);
INSERT INTO `test`.`t`(`id`, `c`, `d`) VALUES (5, 5, 5);
INSERT INTO `test`.`t`(`id`, `c`, `d`) VALUES (10, 10, 10);

Record Locks

记录锁(record locks)是索引上的一种行锁。

如下面的sql,会对c1=10的行设置记录锁record lock,阻止其他事务对id=0的行进行插入、更新、删除

1
SELECT * FROM t WHERE id = 0 FOR UPDATE;

Clustered and Secondary Indexes

mysql v8.0 Clustered and Secondary Indexes

InnoDB使用聚簇索引(clustered index)来存储数据,该索引的构建方式如下:

① InnoDB使用表中显示的主键(primary key)来构建聚簇索引;

② 如果没有主键,InnoDB会尝试寻找一个非空(NOT NULL)唯一索引(UNIQUE INDEX)来构建聚簇索引;

③ 如果表没有主键且没有非空的唯一索引,InnoDB会为表生成一个隐式的ROW-ID的自增字段,构建名为GEN_CLUST_INDEX的聚簇索引。

How the Clustered Index Speeds Up Queries

聚簇索引的特点在于把所有的数据存储在叶子节点,实现了数据与索引的分离

正常来说,数据占用的空间往往远大于索引,考虑不同索引实现的场景:

【场景1】如果采用BTree来实现,由于BTree的数据与索引是存储在同一个节点上,所以:

  • 查询时可以直接从节点上获取数据,不需要遍历整棵树
  • 每个节点上存储的索引有限,当遇到深层次遍历时会触发大量的磁盘IO
  • 范围查询性能差,需要再次遍历也子节点

【场景2】如果采用B+Tree来实现,由于B+Tree的数据与索引是分离的,所以:

  • 查询时需要遍历整棵数才可以到叶子节点获取数据
  • 非叶子节点不存储数据从而可以存储更多的索引数据,当遇到深层次遍历时所触发的磁盘IO较少,从而拥有更好的性能
  • 范围查询性能好,利用叶子节点的顺序链表来遍历查询

How Secondary Indexes Relate to the Clustered Index

除了聚簇索引,其他搜索都被称为二级索引

二级索引中不会包含行的所有数据,仅会包括关联行的主键。

也就是说,二级索引的检索过程为:先检索二级索引,找到目标行的主键索引KEY,然后在检索聚簇索引,最终找到目标行的数据。

由于二级索引需要关联聚簇索引的KEY,因此聚簇索引的主键选择上不应该选择过大的数据结构。

有一点疑问:为什么二级索引不采用BTree实现?

如果采用BTree实现的话,指定的目标查询不会遍历到叶子节点,这是BTree的优势。
但是如果需要范围查询的时候,性能就不如B+Tree这种实现了,这应该是采用该索引的原因。

实际上,MySQL InnoDB可以设置两种索引类型:BtreeHash,如果该索引不会有顺序查询,使用Hash索引更合适。

Gap Locks

间隙锁(gap locks)是对索引记录之间(两个记录之间、第一个记录之前、最后一个记录之后)间隙的锁。

间隙锁的作用在于防止其他事务操作当前间隙内的数据

【例】下面的SQL会增加一个间隙锁,区间为(10, 20),因此,其他事务无法在间隙内插入、更新或删除,例如插入15会被阻塞。

1
SELECT * FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE

针对不同的索引类型,会出现不同的加锁情况:

  • 对于唯一索引(UNIQUE INDEX)来说,锁定某行时仅需要锁定数据行(不需要间隙锁的)
  • 对于普通索引(INDEX)来说,锁定某行时不仅需要锁定数据行,还要锁定前后间隙

对于测试数据来说,索引(不同字段之间数据值相同)存在的间隙有:(-∞, 0)(0, 5)(5, 10)(10, +∞)

不同的事务的间隙锁可以共存(甚至X锁与S锁),例如,在区间(0, 5)上,事务A持有S锁,事务B持有X锁。

读已提交(READ COMMITTED)的隔离级别下,间隙锁不再用于搜索与索引扫描,仅用于外键约束检查和重复键检查。

Case 1

在初始化测试数据的条件下,考虑c为普通索引,加锁结果如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 事务A:加排它锁成功,间隙锁(0,5)与(5,10)、记录锁{5}
# 其中,间隙锁(5,10)是由于临键锁(5,10]降级而来,从5向右遍历发现10这个节点不满足=5,所以去除c=10的记录锁)
select * from t where c=5 for update; # ok

# 事务B:加插入意向锁失败,由于事务A持有间隙锁(0, 5)导致冲突
insert into t values (3, 3, 3); # blocking

# 此时,事务B提示:lock_mode X locks gap before rec insert intention waiting Record lock

# 事务C:加插入意向锁成功失败,由于事务A持有间隙锁(0, 5)(临键锁(5, 10]降级而来)导致冲突
insert into t values (8, 8, 8); # blocking

# 此时,事务C提示:lock_mode X insert intention waiting Record lock

# 事务D:加插入意向锁成功,由于13不在锁定范围之内
insert into t values (13, 13, 13); # ok

# 事务E:加入排他锁失败,由于记录锁{5}的冲突
update t set c=3 where c=5; # blocking

# 此时,事务E提示:lock_mode X waiting Record lock

# 事务F:加入排它锁成功,由于c=10,没有加锁
update t set c=99 where c=10;

c=5是一个普通索引的加锁行为,不仅对当前的行加记录锁,而且会对两侧的间隙加锁(左侧是间隙锁,右侧为临键锁)。

临键锁的退化

① 在RR级别下,加锁的单位为临键锁

② 针对二级索引的等值(c=5)的加锁情况,会按照索引顺序继续寻找不同的行,进而退化为间隙锁,例如事务F并没有阻塞。

Case 2

在初始化测试数据的条件下,考虑c为普通索引,加锁结果如下:

1
2
3
4
5
6
7
8
9
10
11
12
# 事务A:加排它锁成功,间隙锁(0, 5)、记录锁{5}、临键锁(5, 10]
# 注意,c between 0 and 5 为非等值查询,因此不存在临键锁的退化
select * from t where c between 0 and 5 for update; # ok

# 事务B:加排它锁失败,由于存在记录锁{5}的冲突
update t set c=3 where c=5; # blocking

# 事务C:加插入意向锁失败,由于存在临键锁(5, 10]的冲突
insert into t values (7, 7, 7); # blocking

# 事务D:加排它锁失败,由于存在临键锁(5, 10])
update t set c=99 where c=10; # blocking

between类似于一个范围查询,会把范围内的都加锁(无论是行还是间隙)。

注意事务D的语句会被blocking,也证明了非等值语句的情况下,临键锁是不会退化的。

Next-Key Locks

临键锁(next-key locks)是记录锁与间隙锁的一种组合,锁住索引记录以及索引记录之前的间隙((左开右闭的区间])。

RR级别下,加锁的最小单元是临键锁(next-key locks),都是按照左开右闭的区间来加锁的。

不过,也存在锁降级的情况:

① 唯一索引下,降级为行锁

② 普通索引下,如果是等值查询的话,第二个临键锁(next-key locks)会退化为间隙锁(gap locks)

对于测试数据中c索引来说,临键锁(不同字段之间数据值相同)的区间有:(-∞, 0](0, 5](5, 10](10, +∞)

锁降级:唯一索引上,临键锁会降级为记录锁(不包括左右边缘)。

临键锁仅发生在可重复度(REPEATABLE READ )的隔离级别,主要解决了幻读(phantom read)

Case 1

唯一索引上的锁降级情况如下,

1
2
3
4
5
6
7
8
9
10
11
# 事务A:加排他锁成功,记录锁{5}
select * from t where id=5 for update; # ok

# 事务B:加插入意向锁成功
insert into t values (3, 3, 3); # ok

# 事务C:加插入意向锁成功
insert into t values (7, 7, 7); # ok

# 事务D:加排它锁失败,c=5 找到 id=5,由于记录锁导致加锁失败
update t where c=99 where c=5; # blocking

Case 2

在RR隔离级别下,在测试数据初始化的条件下,考虑c为普通索引,锁操作如下,

1
2
3
4
5
6
7
8
9
10
11
# 事务A:加排它锁成功,间隙锁(0,5)与(5,10)、记录锁{5}
# 其中,间隙锁(5,10)是由于临键锁(5,10]降级而来,从5向右遍历发现10这个节点不满足=5,所以去除c=10的记录锁)
select * from t where c=5 for update; # ok

# 事务B:加排他锁失败,由于临间隙锁(5, 10)的冲突
insert into t values (8, 8, 8); # blocking

# 此时,事务B提示:lock_mode X locks gap before rec insert intention waiting

# 事务C:加排他锁失败,不存在锁冲突
insert into t values (13, 13, 13); # ok

c=5为普通索引上的加锁,包括间隙锁(0, 5)、记录锁{5}、间隙锁(5, 10)(等值查询情况下的锁退化)。

Case 3

在RR隔离级别下,在测试数据初始化的条件下,考虑d为唯一索引,锁操作如下,

1
2
3
4
5
6
7
8
# 事务A:加排他锁成功,记录锁{5}
select * from t where d=5 for update; # ok

# 事务B:加插入意向锁成功,无冲突
insert into t values (8, 8, 8); # ok

# 事务C:加插入意向锁成功,无冲突
insert into t values (13, 13, 13); # ok

Case 4

在RR隔离级别下,在测试数据初始化的条件下,考虑c为普通索引,锁操作如下,

1
2
3
4
5
6
7
# 事务A:加排他锁成功,间隙锁(5, 10),记录锁{10},临键锁(10, +∞)
select * from t where c > 8 for update; # ok

# 事务B:加插入意向锁失败,由于存在临键锁(10, +∞)冲突
insert into t values (13, 13, 13); # blocking...

# 此时,事务B提示:lock_mode X insert intention waiting Record lock

Case 5

在RR隔离级别下,在测试数据初始化的条件下,考虑d为唯一索引,锁操作如下,

1
2
3
4
5
6
7
8
# 事务A:加排他锁成功,间隙锁(5, 10), 记录锁{10}, 临键锁(10, +∞)
select * from t where d > 8 for update; # ok

# 事务B:加插入意向锁失败,由于存在间隙锁(5, 10)冲突
insert into t values (7, 7, 7); # blocking...

# 事务C:加插入意向锁失败,由于存在临键锁(10, +∞)冲突
insert into t values (13, 13, 13); # blocking...

Insert Intention Locks

插入意图锁(insert intention locks)是一种间隙锁,当执行插入(insert)操作是触发。

【例1】以下SQL会加入插入意向锁,锁住的间隙是(0, 5),

1
insert into t values(3, 3, 3);

多事务的插入意向锁

考虑到插入意向锁是间隙锁,因此,不同事务的插入意向锁可以共存。

不同事务同时持有相同间隙的插入意向锁的情况下,如果插入的位置不同(值不同),此时各个事务会正常执行插入操作,

否则,如果出现插入到相同位置,该事务会一直等待,直到插入该位置的事务释放锁。

【例2】存在三个事务,分别在初始化的测试数据集上尝试写入数据,具体如下,

1
2
3
4
5
6
7
8
9
10
# 事务A:持有插入意向锁成功,锁定区间(0, 5),此时会给3增加行锁
insert into t values (3, 3, 3) # ok

# 事务B:持有插入意向锁成功,锁定区间(0, 5),与事务A插入位置不同,所以无冲突,执行成功
insert into t values (4, 4, 4) # ok

# 事务C:由于事务A已经对于同一位置写入数据,由于3已经被A持有了行锁,因此,当前事务阻塞等待事务A的释放
insert into t values (3, 3, 3) # blocking

# 此时,事务C提示:lock mode S waiting Record lock

上面发现,相同位置数据的数据会提示S锁,插入不应该是X锁么?

对于唯一索引来说,插入前需要先检查是否存在重复数据,此时X锁先会降级为S锁来实现当前读。

AUTO-INC Locks

自增锁(auto-inc locks)表锁(table-level locks),用于实现自增主键。

由于自增锁属于表锁,性能必然很差,因此,考虑到自增主键使用的场景,自增锁的锁定范围并不是整个事务,而是锁定的insert sql语句级别,

也就说,不同的事务之间,在insert时是交替完成的,虽然是表锁但对事务的并发插入并没有太大的影响。

Phantom Reads

幻读(phantom reads)是由于不同事务之间新增数据导致的前后数量不一致的问题。

幻读是insert引起的,不可重复读是由update或delete引起的。

幻读产生原因在于新增的,不可重复读产生的原因在于更新。

在RR隔离级别下,通过临键锁(next-key locks)解决了幻读。

select for update属于当前读(排它锁),而select属于快照读(无锁)。

select lock in share mode属于共享锁,无法与排它锁共存。

Case 1

即使InnoDB实现了MVCC解决了幻读的情况,但不同事务之间仍然存在先后插入数据的冲突问题。

在测试数据初始化的条件下,观察以下加锁结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 事务A:快照读,不会加锁
select * from t where id > 2; # ok

# 事务B:加插入意向锁成功,锁定id=3(记录锁)
insert into t values (3, 3, 3); # ok

# 事务A:加插入意向锁失败,由于事务B已经持有id=3的记录锁
insert into t values (3, 3, 3); # blocking...

# 此时,事务A提示:lock mode S waiting Record lock

# 事务B:提交事务,写入id=3的数据
commit; # ok

# 事务A:由于事务B已经提交事务,返回失败"Duplicate entry '3' for key 'PRIMARY'"

# 事务A:失败后再次执行快照读(前后数据不变,观察不到事务B已经写入数据),虽然提示3已经写入,但还是观察不到
select * from t where id > 2; # ok

Dead Locks

死锁(dead locks)是由于多个事务相互持有互相等待的锁导致的。

Case 1

insert-insert-insert-rollback 三个事务写入的场景下的死锁

在测试数据初始化的条件下,观察以下加锁结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 事务A:加插入意向锁(0,5),id=3的排他锁(X锁)
insert into t values (3, 3, 3); # ok

# 事务B:加插入意向锁失败,优先获取S锁,查看数据是否存在,进入阻塞等待事务A的释放
insert into t values (3, 3, 3); # blocking...

# 事务C:同上
insert into t values (3, 3, 3); # blocking...

# 事务A:回滚,释放事务A上的锁
rollback; # ok

# 此时,事务B与事务C同时持有S锁,都在等待对方释放S锁进而添加X锁(存在死锁)
# 最终结果:
# 事务C:加插入意向锁(0,5),id=3的排他锁(X锁)
# 事务B:加锁失败,出现死锁,返回异常:Deadlock found when trying to get lock; try restarting transaction

由于两个事务持有同一个记录的S锁,彼此等待对方释放S锁,进而进入死锁。

Case 2

update-insert 两个事务写入重叠锁区间场景下的死锁

在测试数据初始化的条件下,观察以下加锁结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 事务A:加排他锁成功,间隙锁(0,5)、记录锁{5}、临键锁(5, 10]
select * from t where c=5 for update; # ok

# 事务B:加排他锁成功,间隙锁(5,10)、记录锁{10}、临键锁(10, +∞)
select * from t where c=10 for update; # ok

# 此时,存在锁重叠,区间为(5, 10]

# 事务A:加插入意向锁失败,由于事务B的间隙锁(5,10)冲突
insert into t values (7, 7, 7); # blocking

# 事务B:加插入意向锁失败,由于事务A的临键锁(5, 10]冲突
insert into t values (7, 7, 7); # Deadlock found when trying to get lock; try restarting transaction

# 事务A:加插入意向锁成功,由于事务A出现死锁。

Case 3

S X X

1
2
3
4
5
6
7
8
9
10
# 事务A:id=5 S锁
select * from t where id=5 for share; # ok

# 事务B:id=5 请求 X锁
select * from t where id=5 for update; # blocking

# 事务A:id=5 请求 X锁
select * from t where id=5 for update; # ok

# 事务B:Deadlock found when trying to get lock; try restarting transaction
1
2
3
4
5
6
7
8
9
10
# 事务A:id=5 S锁
select * from t where id=5 for share; # ok

# 事务B:id=5 请求 X锁
delete from t where id=5; # blocking

# 事务A:id=5 请求 X锁
delete from t where id=5; # ok

# 事务B:Deadlock found when trying to get lock; try restarting transaction

临键锁会分为两段加锁:加间隙锁加记录锁

1
2
3
4
5
6
7
8
9
10
# 事务A:间隙锁(0, 5)(5, 10),记录锁{5}
select * from t where c=5 for share; # ok

# 事务B:加间隙锁成功(0,5)(5,10),加记录锁{5}冲突,等待
update t set c=99 where c=5; # blocking

# 事务A:由于事务B持有间隙锁,因此出现死锁,事务B异常退出
insert into t values (3, 3, 3); #ok

# 事务B:Deadlock found when trying to get lock; try restarting transaction

limit

limit对加锁范围仍然有影响:仅会对扫描到的数据范围进行加锁

Case 1

针对唯一索引的limit场景如下,

1
2
3
4
5
# 事务A:扫描id>0的数据,由于limit仅为1,则仅会锁住id=5的这条数据
delete from t where id > 0 limit 1; # ok

# 事务B:
delete from t where id > 5 limit 1; # ok

Case 2

针对非唯一索引的limit场景如下,

1
2
3
4
5
6
7
8
# 事务A:扫描c>0的数据,由于limit仅为1,间隙锁(0, 5),行锁{5} 由于limit1,不会锁住5后面的区间
delete from t where c > 0 limit 1; # ok

# 事务B:由于事务A存在间隙锁(0, 5),加锁失败
insert into t values (3, 3, 3); # blocking

# 事务C:无锁冲突,加锁成功
delete from t where c > 5 limit 1; # ok

Summary

数据库上的锁都是为了避免数据并发更新导致的问题,这与系统中所使用锁的初衷是一样的。

在InnoDB上,锁都是建立索引的基础之上,这里包括聚簇索引、二级索引等。

对于唯一索引来说,针对每个特定的值的遍历与查询是可以明确目标的,因此,不需要间隙锁,仅使用记录锁就可以了。

对于普通索引来说,针对某个特定的值的遍历与查询是不固定的,可能存在多个相同的值,此时就需要锁定目标值的前后,防止加入相同的值。

间隙锁是可以共存的,即使是冲突的锁,临键锁解决了幻读的问题。

插入意向锁是间隙锁,需要注意插入冲突的判断条件是位置是否相同,即使是相同范围的间隙锁,虽然插入位置不同也不会造成冲突。

对于自增锁来说,虽然是表锁,但锁定的时间仅限于插入SQL语句,不会跟随整个事务,因此,性能并没有那么差。