由delete引起的锁扩大
由delete引起的锁扩大
阿里云月报中的一句话，出处：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 testgo
CREATETABLE t (
id intNOTNULLprimarykey,
c intDEFAULTNULL,
d intDEFAULTNULL
)
CREATENONCLUSTEREDINDEX[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
insertinto t values(5,5,5),(10,10,10),(20,20,20),(25,25,25);
使用下面的执行顺序
在session1执行下面语句
--session 1USE test
GO
SETTRANSACTIONISOLATIONLEVELSERIALIZABLE
GO
begintransaction
select id from t where c >10and c <=24
deletefrom t where c =25
--commit
在session2执行下面语句
--session 2USE test
GO
SETTRANSACTIONISOLATIONLEVELSERIALIZABLE
GO
insertinto 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 transactionisolationlevelREPEATABLEREAD;select@@global.transaction_isolation;use test;CREATETABLE `t` (`id`
int(11) NOTNULL,`c`
int(11) DEFAULTNULL,`d`
int(11) DEFAULTNULL,PRIMARYKEY (`id`),KEY `c` (`c`)) ENGINE
=InnoDB;insertinto t values(5,5,5),(10,10,10),(20,20,20),(25,25,25);
SQL语句执行顺序跟SQL Server一样
在session1执行下面语句
-- session 1begin;
select id from t where c >10and c <=24forupdate;
deletefrom t where c =25;
--commit
在session2执行下面语句
-- session 2insertinto t(id,c,d) values(27,27,27); （blocked）
申请的锁如下
select*from performance_schema.data_locksG***************************1. row ***************************ENGINE: INNODB
ENGINE_LOCK_ID:
140111552409600:1217:140111564061632ENGINE_TRANSACTION_ID:
7643THREAD_ID:
331EVENT_ID:
8OBJECT_SCHEMA: test
OBJECT_NAME: tPARTITION_NAME:
NULLSUBPARTITION_NAME:
NULLINDEX_NAME:
NULLOBJECT_INSTANCE_BEGIN:
140111564061632LOCK_TYPE:
TABLELOCK_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 inset (0.00 sec)
分析：这里我们要关注的加锁对象依然是二级索引【c】，这里跟SQL Server一样
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: supremum pseudo-record
锁住的范围是 [25, +∞) 正无穷， 所以session 2的insert操作被阻塞了，也就是删除 c=25 这行数据，导致gap lock 扩大到 正无穷
通过上面两个测试，可以知道即使delete操作留下了Ghost Records，但是delete事务造成的gap lock没缩小为Ghost Record的 record lock
因此，阿里云月报中的说法有失偏颇
本文版权归作者所有，未经作者同意不得转载。
以上是 由delete引起的锁扩大 的全部内容， 来源链接： utcz.com/z/536302.html
