Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24224

Gap lock on delete in 10.5 using READ COMMITTED

    XMLWordPrintable

    Details

      Description

      During regression testing of our application on MariaDB 10.5 we hit a gap lock that doesn't occur in previous versions. Our transactions use isolation level READ COMMITTED so there shouldn't be any gap locks.

      Test:

      DROP TABLE IF EXISTS `test`;
      CREATE TABLE `test` (
      	ID varchar(40) NOT NULL,
      	TEST1 varchar(40) DEFAULT NULL,
      	TEST2 varchar(15) NOT NULL,
      	TEST3 bigint(20) DEFAULT NULL,
      	KEY `IDX_TEST` (TEST1, TEST2, TEST3) 
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
       
      INSERT INTO test (ID, TEST1, TEST2, TEST3) VALUES ('row_a', 'A.123', 'C', 3);
      INSERT INTO test (ID, TEST1, TEST2, TEST3) VALUES ('row_a', 'B.456', 'C', 3);
       
      SET GLOBAL INNODB_STATUS_OUTPUT_LOCKS = 'ON';
      SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
      BEGIN; 
      DELETE FROM test WHERE TEST1 = 'A.123a' and TEST2 = 'C' and TEST3 = 3;
      SHOW ENGINE INNODB STATUS\G
      

      The delete statement matches 0 rows, but creates a gap lock.

      Using a different delete statement doesn't create a gap lock.

      DELETE FROM test WHERE TEST1 = 'G.123a' and TEST2 = 'X' and TEST3 = 31;
      

      Expected Behavior:
      no gap lock on the delete statement.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              marko Marko Mäkelä
              Reporter:
              benowen Ben Owen
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: