由delete引起的锁扩大[亲测有效]

大家好，我是考100分的小小码，祝大家学习进步，加薪顺利呀。今天说一说由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 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);

使用下面的执行顺序

由delete引起的锁扩大[亲测有效]

在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)

申请的锁如下

由delete引起的锁扩大[亲测有效]

分析：首先我们要关注的加锁对象是二级索引【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_locksG
*************************** 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】，这里跟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

因此，阿里云月报中的说法有失偏颇

本文版权归作者所有，未经作者同意不得转载。

原文地址：https://www.cnblogs.com/lyhabc/archive/2022/02/24/15883562.html

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/5487.html

由delete引起的锁扩大[亲测有效]

由delete引起的锁扩大

相关推荐

发表回复