MySQL Forums
Forum List  »  InnoDB

Re: About Next-Key Locks
Posted by: Peter Brawley
Date: April 14, 2021 01:42PM

Under repeatable_read after ...

drop table if exists test;
create table test( id int primary key, i int, key(i) );
insert into test values(1,10),(2,11),(3,13),(4,20);
start transaction;
select * from test where i=17 for update ;

... then in another client, this ...

insert into test set id=5,i=13;

... times out. InnoDB row locks are ...

SELECT * from performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 1727614845088:2428:1727578871128
ENGINE_TRANSACTION_ID: 121052
            THREAD_ID: 222
             EVENT_ID: 129
        OBJECT_SCHEMA: test
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 1727578871128
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 1727614845088:987:5:5:1727578868344
ENGINE_TRANSACTION_ID: 121052
            THREAD_ID: 222
             EVENT_ID: 129
        OBJECT_SCHEMA: test
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: i
OBJECT_INSTANCE_BEGIN: 1727578868344
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 20,

Still under repeatable_read and the same setup, after ...

select * from test where i between 13 and 20 for update ;

on one client, another client trying to execute ...

insert into test set id=6,i=99;

... times out like your id=5,i=13 example. InnoDB locks are ...

SELECT * from performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 1727614845088:2426:1727578871128
ENGINE_TRANSACTION_ID: 120997
            THREAD_ID: 222
             EVENT_ID: 106
        OBJECT_SCHEMA: test
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 1727578871128
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 1727614845088:985:5:1:1727578868344
ENGINE_TRANSACTION_ID: 120997
            THREAD_ID: 222
             EVENT_ID: 106
        OBJECT_SCHEMA: test
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: i
OBJECT_INSTANCE_BEGIN: 1727578868344
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 1727614845088:985:5:4:1727578868344
ENGINE_TRANSACTION_ID: 120997
            THREAD_ID: 222
             EVENT_ID: 106
        OBJECT_SCHEMA: test
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: i
OBJECT_INSTANCE_BEGIN: 1727578868344
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 13, 3
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 1727614845088:985:5:5:1727578868344
ENGINE_TRANSACTION_ID: 120997
            THREAD_ID: 222
             EVENT_ID: 106
        OBJECT_SCHEMA: test
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: i
OBJECT_INSTANCE_BEGIN: 1727578868344
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 20, 4
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 1727614845088:985:4:4:1727578868688
ENGINE_TRANSACTION_ID: 120997
            THREAD_ID: 222
             EVENT_ID: 106
        OBJECT_SCHEMA: test
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 1727578868688
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 3
*************************** 6. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 1727614845088:985:4:5:1727578868688
ENGINE_TRANSACTION_ID: 120997
            THREAD_ID: 222
             EVENT_ID: 106
        OBJECT_SCHEMA: test
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 1727578868688
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 4

The issue appears to be more complicated than just the left border of one gap lock.

Options: ReplyQuote


Subject
Views
Written By
Posted
208
April 14, 2021 06:30AM
74
April 14, 2021 06:33AM
Re: About Next-Key Locks
71
April 14, 2021 01:42PM
58
April 15, 2021 07:49AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.