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

最新推荐文章于 2022-08-26 14:53:12 发布

喵思 Muse88

最新推荐文章于 2022-08-26 14:53:12 发布

阅读量147

点赞数

文章标签： mysql u0019

本文链接：https://blog.csdn.net/weixin_33680112/article/details/113467605

版权

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是普通索引, 因此此处的查找条件是最右边c=20 的行, 因此需要继续向右遍历, 直到找到 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;

+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------

+----+-------------+-------+------------+-------+---------------+------+---------+------+------+---------

| 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.

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