MySQL Bugs: #106546: The gap lock range is too large

Bug #106546	The gap lock range is too large
Submitted:	23 Feb 2022 9:15	Modified:	24 Feb 2022 4:27
Reporter:	yonghua Lin	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	8.0.28	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	gap lock, next-key lock

Description:
After deleting a row of data, the gap lock should not be expanded to positive infinity, and the gap lock should continue to be retained on the deleted row of records.Because after deleting a row of data, ghost records are actually retained

How to repeat:

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

set global transaction isolation level REPEATABLE READ;
select @@global.transaction_isolation;

time	           session1    	                                                        session2
T1	              begin;
                  select id from t where c >10 and c <= 24 for update;
                  delete from t where c = 25;       
					
T2	                                                                                  insert into  t(id,c,d) values(27,27,27);  (blocked)
T3		           commit

Suggested fix:
When deleted the record C = 25, the locking range should remain unchanged. The locking range is (10,25].
So ,this can improve concurrency and reduce lock overhead.

Hi Mr. Lin,

Thank you for your bug report.

However, this is not a bug.

There are two reasons why this is not a bug. First of all, while the transaction is active, no DML is executed physically, but only that row gets a lock, in this case it gets an exclusive lock.

The other reason is that you have not taken into the account infimum and supremum records.

Not a bug.

Thank you for your reply.
Of course I know the lock will extend to Infinium and supremum records.

This is another example

truncate table t;
insert into t values(5,5,5),(10,10,10),(20,20,20),(25,25,25);
set global transaction isolation level REPEATABLE READ;

time	           session1    	                                                        session2
T1	              begin;
                 delete from t where c = 25;      
					
T2	                                                                              select id from t where c >10 and c <= 20 for update;  (blocked)
T3		           commit

As the example above shows
Session 2 is blocked.

This phenomenon is abnormal.
Here is the table performance_schema.data_locks  Output 

select * from performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140111552409600:1216:140111564061632
ENGINE_TRANSACTION_ID: 7611
            THREAD_ID: 325
             EVENT_ID: 11
        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:58:5:4:140111564058528
ENGINE_TRANSACTION_ID: 7611
            THREAD_ID: 325
             EVENT_ID: 11
        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
          LOCK_STATUS: GRANTED
            LOCK_DATA: 20, 20
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140111552409600:58:4:4:140111564058872
ENGINE_TRANSACTION_ID: 7611
            THREAD_ID: 325
             EVENT_ID: 11
        OBJECT_SCHEMA: test
          OBJECT_NAME: t
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140111564058872
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 20
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140111552409600:58:5:5:140111564059216
ENGINE_TRANSACTION_ID: 7611
            THREAD_ID: 325
             EVENT_ID: 11
        OBJECT_SCHEMA: test
          OBJECT_NAME: t
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: c
OBJECT_INSTANCE_BEGIN: 140111564059216
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: WAITING
            LOCK_DATA: 25, 25
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140111552408792:1216:140111564055552
ENGINE_TRANSACTION_ID: 7608
            THREAD_ID: 324
             EVENT_ID: 37
        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
*************************** 6. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140111552408792:58:5:1:140111564052496
ENGINE_TRANSACTION_ID: 7608
            THREAD_ID: 324
             EVENT_ID: 37
        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
*************************** 7. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140111552408792:58:5:5:140111564052496
ENGINE_TRANSACTION_ID: 7608
            THREAD_ID: 324
             EVENT_ID: 37
        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
*************************** 8. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140111552408792:58:4:5:140111564052840
ENGINE_TRANSACTION_ID: 7608
            THREAD_ID: 324
             EVENT_ID: 37
        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)

------------------------------------------------------------------
Note the output of the line C = 25
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140111552409600:58:5:5:140111564059216
ENGINE_TRANSACTION_ID: 7611
            THREAD_ID: 325
             EVENT_ID: 11
        OBJECT_SCHEMA: test
          OBJECT_NAME: t
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: c
OBJECT_INSTANCE_BEGIN: 140111564059216
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: WAITING
            LOCK_DATA: 25, 25

C = 25 this row of data should not be locked!

This problem is similar to (bug #29508068)

This is still not a bug.

First of all, before transaction is committed or rolled back, that lock has to be held. In the case that a record to be deleted is a last record, then a gap lock extends to the supremum record.

That is one of the basic premises of the ACID standard.