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

Gap lock on delete in 10.5 using READ COMMITTED

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

          is caused by

          Task - A task that needs to be done. MDEV-19544 Remove innodb_locks_unsafe_for_binlog

          • Major - Major loss of function.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-16232 Use fewer mini-transactions

          • Major - Major loss of function.
          • Stalled

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20605 Awaken transaction can miss inserted by other transaction records due to wrong persistent cursor restoration

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20605 Awaken transaction can miss inserted by other transaction records due to wrong persistent cursor restoration

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.