关于 MySQL 中 InnoDB 行锁的理解及案例

Last-Modified: 2019年9月29日10:08:11

本文内容主要是《MySQL实战45讲》课程中第 20,21,30 课程的个人笔记及相关理解.

主要是对于加锁规则的理解及分析.

以下仅针对 MySQL 的 InnoDB 引擎.

MyISM 引擎就是表锁

基本概念

锁的种类

MySQL 中的锁主要分为:

全局锁
```
flush table with read lock;
```

表级锁

表锁

lock table 表名 read;
lock table 表名 write;

元数据锁(Meta Data Lock, MDL)

行锁

还有个自增锁, 后续补充.
意向锁在此先不做讨论.

InnoDB 中的锁

行锁

行锁也叫做记录锁, 这个锁是加在具体的索引项上的.

行锁分为两种:

读锁: 共享锁
写锁: 排它锁

行锁冲突情况:

读锁与写锁冲突
写锁与写锁冲突

需要明确:

锁的对象是索引

间隙锁

记录之间是存在间隙的, 这个间隙也是可以加上锁实体, 称为间隙锁.

间隙锁存在的目的: 解决幻读问题.

间隙锁冲突情况:

间隙锁之间是不冲突的, 它们都是为了防止插入新的记录.
间隙锁与插入操作(插入意向锁)产生冲突

需要明确:

间隙锁仅在可重复读隔离级别下才存在.
间隙锁的概念是动态的

对间隙(a,b)加锁后, 存在间隙锁 (a,b).
此时若 a 不存在(删除), 则间隙锁会向左延伸直到找到一条记录.

若b不存在了(删除), 则间隙锁会向右延伸直到找到一条记录.

假设主键上存在记录 id=5 和 id=10 和 id=15 的3条记录, 当存在某个间隙锁 (10,15) 时, 若我们将 id=10 这一行删掉, 则间隙锁 (10, 15) 会动态扩展成 (5, 15), 此时想要插入 id=7 的记录会被阻塞住.

此处的删除指的是事务提交后, 否则间隙锁依旧是 (10,15)

next-key lock

next-key lock = 行锁 + 间隙锁

next-key lock 的加锁顺序:

先加间隙锁
再加行锁

如果加完间隙锁后, 再加行锁时被阻塞进入锁等待时, 间隙锁在此期间是不会释放的.

索引搜索

索引搜索指的是就是:

在索引树上利用树搜索快速定位找到第一个值
然后向左或向右遍历

order by desc 就是用最大的值来找第一个

order by 就是用最小的值来找第一个

等值查询

等值查询指的是:

在索引树上利用树搜索快速定位 xx=yy的过程

where xx > yy 时, 也是先找到 xx = yy 这条记录, 这一个步骤是等值查询.但后续的向右遍历则属于范围查询.
以及在找到具体记录后, 使用 xx=yy 向右遍历的过程.

例子

例子1

begin;
select * from c20 where id=5 for update;

在主键索引 id 上快速查找到 id=5 这一行是等值查询

例子2

begin;
select * from c20 where id > 9 and id < 12 for update;

在主键索引 id 上找到首个大于 9 的值, 这个过程其实是在索引树上快速找到 id=9 这条记录(不存在), 找到了 (5,10) 这个间隙, 这个过程是等值查询.

然后向右遍历, 在遍历过程中就不是等值查询了, 依次扫描到 id=10 , id=15 这两个记录, 其中 id=15 不符合条件, 根据优化2退化为间隙锁, 因此最终锁范围是 (5,10], (10, 15)

例子3

begin;
select * from c20 where id > 9 and id < 12 order by id desc for update;

根据语义 order by id desc, 优化器必须先找到第一个 id < 12 的值, 在主键索引树上快速查找 id=12 的值(不存在), 此时是向右遍历到 id=15, 根据优化2, 仅加了间隙锁 (10,15) , 这个过程是等值查询.

接着向左遍历, 遍历过程就不是等值查询了, 最终锁范围是: (0,5], (5, 10], (10, 15)

例子4

begin;
select * from t where c>=15 and c<=20 order by c desc lock in share mode;

执行过程:

在索引c上搜索 c=20 这一行, 由于索引c是普通索引, 因此此处的查找条件是 <u>最右边c=20</u> 的行, 因此需要继续向右遍历, 直到找到 c=25 这一行, 这个过程是等值查询. 根据优化2, 锁的范围是 (20, 25)
接着再向左遍历, 之后的过程就不是等值查询了.

例子5

begin;
select * from t where c<=20 order by c desc lock in share mode;

这里留意一下 , 加锁范围并不是 (20, 25], (15, 20], (10,15], (5,10], (0, 5], (-∞, 5], 而是

...........

..........

.........

........

.......

......

.....

......

.......

........

.........

..........

...........

所有行锁+间隙锁.

具体为什么, 其实只要 explain 看一下就明白了.

例子6 - 个人不理解的地方???????????

-- T1 事务A
begin;
select * from c20 where id>=15 and id<=20 order by id desc lock in share mode;

-- T2 事务B
begin;
update c20 set d=d+1 where id=25;    -- OK
insert into c20 values(21,21,21);    -- 阻塞

-- T3 事务A 人为制造死锁, 方便查看锁状态
update c20 set d=d+1 where id=25;    -- OK
/*
此时 事务B 提示:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
*/

个人理解:

根据order by id desc, T1 时刻事务A首先在主键索引上搜索 id=20 这一行, 正常来说主键索引上 id=20 的只有一行, 没必要向右遍历.

但实际上, (20,25) 这个间隙被锁上了, 且没有对 id=25 这一行加行锁, 初步理解是根据优化2: 索引上的等值查询在向右遍历且最后一个值不符合条件时, next-key lock 退化为间隙锁.

也就是说这个地方在搜索到 id=20 这一行后还是继续向右遍历了.....不理解为什么

mysql> show engine innodb status
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-09-27 10:34:29 0xe2e8
*** (1) TRANSACTION:
TRANSACTION 1645, ACTIVE 100 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1080, 4 row lock(s), undo log entries 1
MySQL thread id 82, OS thread handle 77904, query id 61115 localhost ::1 root update
insert into c20 values(21,21,21)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 23 page no 3 n bits 80 index PRIMARY of table `test_yjx`.`c20` trx id 1645 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000019; asc     ;;
 1: len 6; hex 00000000066d; asc      m;;
 2: len 7; hex 6e0000019a0110; asc n      ;;
 3: len 4; hex 80000019; asc     ;;
 4: len 4; hex 8000001a; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 1646, ACTIVE 271 sec starting index read
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1080, 5 row lock(s)
MySQL thread id 81, OS thread handle 58088, query id 61120 localhost ::1 root updating
update c20 set d=d+1 where id=25
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 23 page no 3 n bits 80 index PRIMARY of table `test_yjx`.`c20` trx id 1646 lock mode S locks gap before rec
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000019; asc     ;;
 1: len 6; hex 00000000066d; asc      m;;
 2: len 7; hex 6e0000019a0110; asc n      ;;
 3: len 4; hex 80000019; asc     ;;
 4: len 4; hex 8000001a; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 23 page no 3 n bits 80 index PRIMARY of table `test_yjx`.`c20` trx id 1646 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000019; asc     ;;
 1: len 6; hex 00000000066d; asc      m;;
 2: len 7; hex 6e0000019a0110; asc n      ;;
 3: len 4; hex 80000019; asc     ;;
 4: len 4; hex 8000001a; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

上述的:

(1) TRANSACTION(事务1) 指的是事务B

(2) TRANSACTION(事务2) 指的是事务A

注意与上面的事务A, 事务B 顺序是相反了, 别看错了.

分析:

(1) TRANSACTION
- insert into c20 values(21,21,21) 最后一句执行语句
(1) WAITING FOR THIS LOCK TO BE GRANTED
- index PRIMARY of table test_yjx.c20 说明在等表 c20 主键索引上的锁
- lock_mode X locks gap before rec insert intention waiting 说明在插入一条记录, 试图插入一个意向锁, 与间隙锁产生冲突了
- 0: len 4; hex 80000019; asc ;; 冲突的间隙锁: 16进制的 19, 即 10进制的 id=25 左边的间隙.
(2) TRANSACTION 事务2信息
- update c20 set d=d+1 where id=25 最后一句执行语句
(2) HOLDS THE LOCK(S) 事务2持有锁的信息
- index PRIMARY of table test_yjx.c20 说明持有c20表主键索引上的锁
- lock mode S locks gap before rec 说明只有间隙锁
- 0: len 4; hex 80000019; asc ;; 间隙锁: id=25 左边的间隙
(2) WAITING FOR THIS LOCK TO BE GRANTED: 事务2正在等待的锁
- index PRIMARY of table test_yjx.c20 说明在等待 c20 表主键索引上的锁
- lock_mode X locks rec but not gap waiting 需要对行加写锁
- 0: len 4; hex 80000019; asc ;; 等待给 id=25 加行锁(写)
WE ROLL BACK TRANSACTION (1) 表示回滚了事务1

个人猜测实际情况是:

首先找到 id=20 这一条记录, 由于bug, 引擎认为可能存在不止一条的 id=20 的记录(即将其认为是普通索引), 因此向右遍历, 找到了 id=25 这一行, 由于此时是等值查询, 根据优化2, 锁退化为间隙锁, 即 (20,25)
之后正常向左遍历.

无法证实自己的猜测. 已在课程21和课程30留下以下留言, 等待解答(或者无人解答). 2019年9月27日

-- T1 事务A
begin;
select * from c20 where id>=15 and id<=20 order by id desc lock in share mode;

-- T2 事务B
begin;
update c20 set d=d+1 where id=25;    -- OK
insert into c20 values(21,21,21);    -- 阻塞
不能理解, 为什么事务A执行的语句会给间隙(20,25) 加上锁.
通过 show engine innodb status; 查看发现事务A确实持有上述间隙锁.
通过 explain select * from c20 where id>=15 and id<=20 order by id desc lock in share mode; 查看 Extra 也没有 filesort, key=PRIMARY, 因此个人认为是按照主键索引向左遍历得到结果.

按照我的理解, 由于 order by id desc , 因此首先是在主键索引上搜索 id=20, 同时由于主键索引上这个值是唯一的, 因此不必向右遍历. 然而事实上它确实这么做了, 这让我想到了 BUG1: 主键索引上的范围查询会遍历到不满足条件的第一个.
但是这一步的搜索过程应该是等值查询才对, 完全一脸懵住了...
不知道老师现在还能看到这条评论不?

加锁规则

该部分源自《MySQL实战45讲》中的《21-为什么我只改了一行的语句, 锁这么多》

以下仅针对 MySQL 的 InnoDB 引擎在可重复读隔离级别, 具体MySQL版本:

5.x 系列 <= 5.7.24
8.0 系列 <=8.0.13

以下测试若未指定, 则默认使用以下表, 相关案例为了避免污染原始数据, 因此在不影响测试结果前提下, 都放在事务中执行, 且最终不提交.

create table c20(
    id int not null primary key, 
    c int default null, 
    d int default null, 
    key `c`(`c`)
) Engine=InnoDB;

insert into c20 values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

/*
+----+------+------+
| id | c    | d    |
+----+------+------+
|  0 |    0 |    0 |
|  5 |    5 |    5 |
| 10 |   10 |   10 |
| 15 |   15 |   15 |
| 20 |   20 |   20 |
| 25 |   25 |   25 |
+----+------+------+
*/

2个"原则", 2个"优化", 1个"BUG"

原则1: 加锁的基本单位是next-key lock, 前开后闭区间
原则2: 访问到的对象才会加锁

select id from t where c = 15 lock in share mode;
加读锁时, 覆盖索引优化情况下, 不会访问主键索引, 因此如果要通过 lock in share mode 给行加锁避免数据被修改, 那就需要绕过索引优化, 如 select 一个不在索引中的值.

但如果改成 for update , 则 mysql 认为接下来会更新数据, 因此会将对应主键索引也一起锁了
优化1: 索引上的等值查询, 对唯一索引加锁时, next-key lock 会退化为行锁

select * from t where id = 10 for update;
引擎会在主键索引上查找到 id=10 这一行, 这一个操作是等值查询.

锁范围是
优化2: 索引上的等值查询, 向右遍历时且最后一个值不满足等值条件时, next-key Lock 会退化为间隙锁

select * from t where c = 10 for update;
由于索引c是普通索引, 引擎在找到 c=10 这一条索引项后继续向右遍历到 c=15 这一条, 此时锁范围是 (5, 10], (10, 15)

select * from t where c >= 10;

由于索引c是普通索引, 引擎在找到 c=10 这一条索引项后继续向右遍历到 c=15 这一条, 此时锁范围是 (5, 10], (10, 15)
BUG 1: 唯一索引上的范围查询会访问到不满足条件的第一个值

id> 10 and id <=15, 这时候会访问 id=15 以及下一个记录.

读提交与可重复读的加锁区别

读提交下没有间隙锁
读提交下有一个针对 update 语句的 "semi-consistent" read 优化.

如果 update 语句碰到一个已经被锁了的行, 会读入最新的版本, 然后判断是不是满足查询条件, 若满足则进入锁等待, 若不满足则直接跳过.
注意这个策略对 delete 是无效的.
?????? 语句执行过程中加上的行锁, 会在语句执行完成后将"不满足条件的行"上的行锁直接释放, 无需等到事务提交.

加锁案例

案例: 主键索引 - 等值查询 - 间隙锁

-- T1 事务A
begin;
update c20 set d=d+1 where id=7;
/*
1. 在主键索引上不存在id=7记录, 根据规则1: 加锁基本单位是 next-key lock, 因此加锁范围是(5,10]
2. 由于id=7是一个等值查询, 根据优化2, id=10不满足条件, 因此锁退化为间隙锁 (5,10)
*/

-- T2 事务B
begin;
insert into c20 values(8,8,8);        -- 阻塞
update c20 set d=d+1 where id=10;    -- OK

对应课程的案例一

案例: 非唯一索引 - 等值查询 - 间隙锁

-- T1 事务A
begin;
update c20 set d=d+1 where c=7;
/* 分析
1. 加锁基本单位是next-key lock, 加锁范围就是 (5,10]   -- 此时只是分析过程, 并非加锁过程
2. 根据优化2, 索引上的等值查询(c=7)向右遍历且最后一个值不满足条件时, next-key lock 退化为间隙锁, 加锁范围变为 (5, 10)
3. 由于是在索引c上查询, 因此加锁范围实际上是 ((5,5), (10,10)) , 格式 (c, id)
*/

-- T2 事务B
begin;
insert into c20 values(4,5,4);    -- OK
insert into c20 values(6,5,4);    -- 被间隙锁堵住
insert into c20 values(9,10,9);    -- 被间隙锁堵住
insert into c20 values(11,10,9);    -- OK

案例: 非唯一索引 - 等值查询 - 覆盖索引

关注重点: 覆盖索引优化导致无需回表的情况对主键索引影响

-- T1 事务A
begin;
select id from c20 where c = 5 lock in share mode;    
-- 索引c是普通索引, 因此会扫描到 c=10 这一行, 因此加锁范围是 (0,5], (5,10]
-- 同时由于优化2: 索引上的等值查询向右遍历且最后一个值不满足条件时next-key lock退化为间隙锁, 即加锁范围实际是  (0,5], (5,10)
-- 注意, 该条查询由于只 select id, 实际只访问了索引c, 并没有访问到主键索引, 根据规则2: 访问到的对象才会加锁, 因此最终只对索引c 的范围 (0,5], (5,10) 加锁

-- T2 事务B
begin;
update c20 set d=d+1 where id=5;    -- OK, 因为覆盖索引优化导致并没有给主键索引上加锁
insert into c20 values(7,7,7);

对应课程的案例二

注意, 上面是使用 lock in share mode 加读锁, 因此会被覆盖索引优化.

如果使用 for update, mysql认为你接下来要更新行, 因此也会锁上对应的主键索引.

案例: 非主键索引 - 范围查询 - 对主键的影响

关注重点在于: 普通索引上的范围查询时对不符合条件的索引加锁时, 是否会对对应的主键索引产生影响.

-- T1 事务A
begin;
select * from c20 where c>=10 and c<11 for update;
/*
1. 首先查找到 c=10 这一行, 锁范围 (5,10]
2. 接着向右遍历, 找到 c=15 这一行, 不符合条件, 查询结束. 根据规则2: 只有访问到的对象才会加锁, 由于不需要访问c=15对应的主键索引项, 因此这里的锁范围是索引c上的 (5,10], (10,15], 以及主键上的行锁[10]
*/

-- T2 事务B
begin;
select * from c20 where c=15 for update;     -- 阻塞
select * from c20 where id=15 for update;    -- OK

案例: 主键索引 - 范围锁

-- T1 事务A
begin;
select * from c20 where id>=10 and id<11 for update;
/*
1. 首先在主键索引上查找 id=10 这一行, 根据优化1: 索引上的等值查询在对唯一索引加锁时, next-key lock 退化为行锁, 此时加锁范围是 [10]
2. 继续向右遍历到下一个 id=15 的行, 此时并非等值查询, 因此加锁范围是 [10], (10,15]
*/

-- T2 事务B
begin;
insert into c20 values(8,8,8);        -- OK
insert into c20 values(13,13,13);    -- 阻塞
update c20 set d=d+1 where id=15;    -- 阻塞

对应课程案例三

这里要注意, 事务A首次定位查找id=10这一行的时候是等值查询, 而后续向右扫描到id=15的时候是范围查询判断.

案例: 非唯一索引 - 范围锁

-- T1 事务A
begin;
select * from t where c >= 10 and c < 11 for update;
/*
1. 首先在索引c上找到 c=10 这一行, 加上锁 (5,10]
2. 向右遍历找到 c=15 这一行, 不满足条件, 最终加锁范围是 索引c上的 (5,10], (10,15], 及主键索引 [5]
*/

-- T2 事务B
begin;
insert into c20 values(8,8,8);        -- 阻塞
update c20 set d=d+1 where c=15;    -- 阻塞
update c20 set d=d+1 where id=15;    -- 阻塞

对应课程案例四

案例: 唯一索引 - 范围锁 - bug

-- T1 事务A
begin;
select * from c20 where id>10 and id<=15 for update;
/*
1. 在主键索引上找到 id=15 这一行, 加锁, 根据优化1, next-key lock 退化为行锁 [15]
2. 向右遍历找到 id=20 这一行, 加锁 (15,20]
3. 最终锁范围是 [15], (15,20]
*/

-- T2 事务B
begin;
update c20 set d=d+1 where id=20;    -- 阻塞
insert into c20 values(16,16,16);    -- 阻塞

顺便提一下:

begin;
select * from c20 where id>10 and id<15 for update;
/*
1. 在主键索引上找到id=15这一行, 不满足条件, 根据原则1, 加锁 (10,15]
*/

对应课程案例五

案例: 非唯一索引 - 等值

-- T1 事务A
begin;
insert into c20 values(30,10,30);
commit;
/*
在索引c上, 此时有两行 c=10 的行
由于二级索引上保存着主键的值, 因此并不会有两行完全一致的行, 如下:
c    0    5    10    10    15    20    25
id    0    5    10    30    15    20    25

此时两个 (c=10, id=10) 和 (c=10, id=30) 之间也是存在间隙的
*/

-- T2 事务B
begin;
delete from c20 where c=10;
/*
1. 首先找到索引c上 (c=10, id=10) 这一行, 加锁 (5,10]
2. 向右遍历, 找到 (c=10, id=30) 这一行, 加锁 ( (c=10,id=10), (c=10,id=30) ]
3. 向右遍历, 找到 c=20 这一行, 根据优化2, 索引上的等值查询向右遍历且最后一个值不匹配时, next-key lock 退化为间隙锁, 即加锁 (10,15)
4. 总的加锁范围是 (5,10], ( (c=10,id=10), (c=10,id=30) ], (10,15]
*/

-- T3 事务C
begin;
insert into c20 values(12,12,12);    -- 阻塞
update c20 set d=d+1 where c=15;    -- OK


-- T4 扫尾, 无视
delete from c20 where id=30;

对应课程案例六

delete 的加锁逻辑跟 select ... for update 是类似的.

案例: 非唯一索引 - limit

-- T0 初始环境
insert into c20 values(30,10,30);

-- T1 事务A
begin;
delete from c20 where c=10 limit 2;
/*
1. 找到 c=10 的第一条, 加锁 (5,10]
2. 向右遍历, 找到 c=10,id=30 的记录, 加锁 ( (c=10,id=10), (c=10,id=30) ], 此时满足 limit 2
*/

-- T2, 事务B
begin;
insert into c20 values(12,12,12);    -- OK

如果不加 limit 2 则会继续向右遍历找到 c=15 的记录, 新增加锁范围 (10,15)

对应课程案例七

指导意义:

在删除数据时尽量加 limit, 不仅可以控制删除的条数, 还可以减小加锁的范围.

案例: 死锁例子

-- T1 事务A
begin;
select id from c20 where c=10 lock in share mode;
/*
1. 在索引c上找到 c=10 这一行, 由于覆盖索引的优化, 没有回表, 因此只会在索引c上加锁 (5,10]
2. 向右遍历, 找到 c=15, 不满足, 根据优化2, 加锁范围退化为 (10,15)
3. 总的加锁范围是在索引c上的 (5,10], (10,15)
*/

-- T2 事务B
begin;
update c20 set d=d+1 where c=10;    -- 阻塞
/*
1. 找到 c=10 这一行, 试图加上锁 (5,10], 按照顺序先加上间隙锁(5,10), 由于间隙锁之间不冲突, OK. 之后再加上 [10] 的行锁, 但被T1时刻的事务A阻塞了, 进入锁等待
*/

-- T3 事务A
insert into t values(8,8,8);    -- OK, 但造成 事务B 回滚
/*
往 (5,10) 这个间隙插入行, 此时与 T2时刻事务B 加的间隙锁产生冲突.
同时由于 事务B 也在等待 T1时刻事务A 加的行锁, 两个事务间存在循环资源依赖, 造成死锁.
此时事务B被回滚了, 报错如下:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
*/

对应课程案例八

案例: 非主键索引 - 逆序

-- T1 事务A
begin;
select * from c20 where c>=15 and c<=20 order by c desc lock in share mode;
/*
1. 在索引c上找到 c=20 这一行, 加锁 (15,20]
2. 向左遍历, 找到 c=15 这一行, 加锁 (10,15]
3. 继续向左遍历, 找到 c=10 这一行, 由于不满足优化条件, 因此直接加锁 (5,10], 不满足查询条件, 停止遍历. 
4. 最终加锁范围是 (5,10], (10,15], (15, 20]
*/

-- T2 事务B
insert into c20 values(6,6,6);    -- 阻塞

对应课程的上期答疑

案例: 读提交级别 - semi-consistent 优化

-- 表结构
create table t(a int not null, b int default null)Engine=Innodb;
insert into t values(1,1),(2,2),(3,3),(4,4),(5,5);

-- T1 事务A
set session transaction isolation level read committed;
begin;
update t set a=6 where b=1;
/*
b没有索引, 因此全表扫描, 对主键索引上所有行加上行锁
*/

-- T2 事务B
set session transaction isolation level read committed;
begin;
update t set a=7 where b=2;    -- OK
/*
在读提交隔离级别下, 如果 update 语句碰到一个已经被锁了的行, 会读入最新的版本, 然后判断是不是满足查询条件, 若满足则进入锁等待, 若不满足则直接跳过.
*/
delete from t where b=3;    -- 阻塞
/*
注意这个策略对 delete 是无效的, 因此delete语句被阻塞
*/

对应课程评论下方 @时隐时现 2019-01-30 的留言

案例: 主键索引 - 动态间隙锁 - delete

-- T1 事务A
begin;
select * from c20 where id>10 and id<=15 for update;
/*
加锁 (10,15], (15, 20]
*/

-- T2 事务B 注意此处没加 begin, 是马上执行并提交的单个事务.
delete from c20 where id=10;    -- OK
/*
事务A在T1时刻加的间隙锁 (10,15) 此时动态扩展成 (5,15)
*/

-- T3 事务C
insert into c20 values(10,10,10);    -- 阻塞
/*
被新的间隙锁堵住了
*/

对应课程评论下方 @Geek_9ca34e 2019-01-09 的留言

如果将上方的 T2时刻的事务B 和 T3时刻的事务C 合并在一个事务里, 则不会出现这种情况.

个人理解是, 事务未提交时, 期间删除/修改的数据仅仅是标记删除/修改, 此时记录还在, 因此间隙锁范围不变.

只有在事务提价后才会进行实际的删除/修改, 因此间隙锁才"会动态扩大范围"

案例: 普通索引 - 动态间隙锁 - update

-- T1 事务A
begin;
select c from c20 where c>5 lock in share mode;
/*
找到 c=5, 不满足, 向右遍历找到 c=10, 加锁 (5,10], 继续遍历, 继续加锁...
*/

-- T2 事务B
update c20 set c=1 where c=5;    -- OK
/*
删除了 c=5 这一行, 导致 T1时刻事务A 加的间隙锁 (5,10) 变为 (1,10)
*/

-- T3 事务C
update c20 set c=5 where c=1;    -- 阻塞
/*
将 update 理解为两步:
1. 插入 (c=5, id=5) 这个记录    -- 被间隙锁阻塞
2. 删除 (c=1, id=5) 这个记录
*/

案例: 非主键索引 - IN - 等值查询

begin;
select id from c20 where c in (5,20,10) lock in share mode;

通过 explain 分析语句:

mysql> explain select id from c20 where c in (5,20,10) lock in share mode;
+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra     
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+---------
|  1 | SIMPLE      | c20   | range | c             | c    | 5       | NULL |    3 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+---------
1 row in set, 1 warning (0.00 sec)

显示结果太长, 因此将 partitions, filtered 列删除了

结果分析:

使用了索引 c
rows = 3 说明这3个值都是通过 B+ 树搜索定位的

语句分析:

在索引c上查找 c=5, 加锁 (0,5], 向右遍历找到 c=10, 不满足条件, 根据优化2, 加锁 (5,10)
在索引c上查找 c=10, 类似步骤1, 加锁 (5,10], (10, 15)
在索引c上查找 c=20, 加锁 (15,20], (20, 25)

注意上述锁是一个个逐步加上去的, 而非一次性全部加上去.

考虑以下语句:

begin;
select id from c20 where c in (5,20,10) order by id desc for update;

根据语义 order by id desc, 会依次查找 c=20, c=10, c=5.

由于加锁顺序相反, 因此如果这两个语句并发执行的时候就有可能发生死锁.