作者：桦仔
10余年DBA工作经验
微信：debolop
QQ交流群：740052625
公众号：数据库实战派
背景
本文通过对SQL Server和MySQL两种数据库的删除操作加锁行为进行测试，探讨了Ghost Record对锁机制的影响。
通过模拟SQL Server和MySQL中的事务操作，文章对阿里云月报中关于删除事务减少锁范围的说法提出质疑。实践测试显示，删除操作后，锁的范围依然会扩大，而不仅仅是缩小为Ghost Record的record lock。阿里云月报中的一句话，出处：http://mysql.taobao.org/monthly/2022/01/01/
但是Ghost Record是可以跟正常的Record一样作为Key Range Lock的加锁对象的。可以看出这相当于把删除操作变成了更新操作，因此删除事务不再需要持有Next Key Lock
翻译为人话就是：假设delete语句物理删除数据，那么delete事务会持有gap lock，那么会造成锁扩大，而实际上delete操作会转为update操作，最终delete事务持有的gap lock退化为record lock，不会造成锁范围扩大
模拟测试
下面用SQL Server和MySQL做测试，看一下锁的情况
SQL Server 2012
use test
go
CREATE TABLE t (
id int NOT NULL primary key,
c int DEFAULT NULL,
d int DEFAULT NULL
)
CREATE NONCLUSTERED INDEX [ix_t_c] ON [dbo].[t]
(
[c] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
insert into t values(5,5,5),(10,10,10),(20,20,20),(25,25,25);
使用下面的执行顺序
在session1执行下面语句
--session 1
USE test
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
begin transaction
select id from t where c >10 and c <= 24
delete from t where c = 25
--commit
在session2执行下面语句
--session 2
USE test
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
insert into t(id,c,d) values(27,27,27); --(blocked)
申请的锁，情况如下
分析：首先我们要关注的加锁对象是二级索引【ix_t_c】，可以看到有三个range锁，这里锁住的范围是
rangeS-S(10,20]
rangeX-X(20, 25]
rangeS-U[25, +∞) 正无穷
正因为rangeS-U 锁，session 2的insert操作被阻塞了，也就是删除 c=25 这行数据，导致键范围锁扩大到 正无穷
MySQL 8.0.28
set global transaction isolation level REPEATABLE READ;
select @@global.transaction_isolation;
use test;
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(5,5,5),(10,10,10),(20,20,20),(25,25,25);
SQL语句执行顺序跟SQL Server一样
在session1执行下面语句
-- session 1
begin;
select id from t where c >10 and c <= 24 for update;
delete from t where c = 25;
--commit
在session2执行下面语句
-- session 2
insert into t(id,c,d) values(27,27,27); --（blocked）
申请的锁，情况如下
select * from performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140111552409600:1217:140111564061632
ENGINE_TRANSACTION_ID: 7643
THREAD_ID: 331
EVENT_ID: 8
OBJECT_SCHEMA: test
OBJECT_NAME: t
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140111564061632
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140111552409600:59:5:1:140111564058528
ENGINE_TRANSACTION_ID: 7643
THREAD_ID: 331
EVENT_ID: 8
OBJECT_SCHEMA: test
OBJECT_NAME: t
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: c
OBJECT_INSTANCE_BEGIN: 140111564058528
LOCK_TYPE: RECORD
LOCK_MODE: X,INSERT_INTENTION
LOCK_STATUS: WAITING
LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140111552408792:1217:140111564055552
ENGINE_TRANSACTION_ID: 7642
THREAD_ID: 330
EVENT_ID: 12
OBJECT_SCHEMA: test
OBJECT_NAME: t
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140111564055552
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140111552408792:59:5:1:140111564052496
ENGINE_TRANSACTION_ID: 7642
THREAD_ID: 330
EVENT_ID: 12
OBJECT_SCHEMA: test
OBJECT_NAME: t
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: c
OBJECT_INSTANCE_BEGIN: 140111564052496
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: supremum pseudo-record
*************************** 5. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140111552408792:59:5:4:140111564052496
ENGINE_TRANSACTION_ID: 7642
THREAD_ID: 330
EVENT_ID: 12
OBJECT_SCHEMA: test
OBJECT_NAME: t
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: c
OBJECT_INSTANCE_BEGIN: 140111564052496
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 20, 20
*************************** 6. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140111552408792:59:5:5:140111564052496
ENGINE_TRANSACTION_ID: 7642
THREAD_ID: 330
EVENT_ID: 12
OBJECT_SCHEMA: test
OBJECT_NAME: t
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: c
OBJECT_INSTANCE_BEGIN: 140111564052496
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 25, 25
*************************** 7. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140111552408792:59:4:4:140111564052840
ENGINE_TRANSACTION_ID: 7642
THREAD_ID: 330
EVENT_ID: 12
OBJECT_SCHEMA: test
OBJECT_NAME: t
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140111564052840
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 20
*************************** 8. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140111552408792:59:4:5:140111564052840
ENGINE_TRANSACTION_ID: 7642
THREAD_ID: 330
EVENT_ID: 12
OBJECT_SCHEMA: test
OBJECT_NAME: t
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140111564052840
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 25
8 rows in set (0.00 sec)
分析：这里我们要关注的加锁对象依然是二级索引【c】，这里MySQL的情况跟SQL Server一样
LOCK_MODE: X LOCK_STATUS: GRANTED LOCK_DATA: supremum pseudo-record
锁住的范围是 [25, +∞) 正无穷， 所以session 2的insert操作被阻塞了，也就是删除 c=25 这行数据，导致gap lock 扩大到 正无穷
总结
通过SQL Server和MySQL的测试结果可以看出，即使删除操作留下了Ghost Record，删除事务导致的gap lock范围并没有如阿里云月报所述缩小为record lock，
反而锁的范围扩大到正无穷，阻塞后续插入操作。这表明阿里云月报中的说法存在偏差，误导了读者
参考文章
https://blog.csdn.net/weixin_41645135/article/details/136637147
https://www.yisu.com/jc/869725.html
https://blog.51cto.com/u_16099221/11210197
https://blog.csdn.net/weixin_43310500/article/details/135366601
https://blog.csdn.net/lusklusklusk/article/details/127519007
加入我们的微信群，与我们一起探讨数据库技术，以及SQL Server、 MySQL、PostgreSQL、MongoDB 的相关话题。
微信群仅供学习交流使用，没有任何广告或商业活动。
