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

Several locks taken to same record inside a transaction.

    XMLWordPrintable

Details

    • 5.5.55

    Description

      CREATE TABLE `tu`(`id` int(11), `a` int(11) DEFAULT NULL, `b` varchar(10) DEFAULT NULL, `c` varchar(10) DEFAULT NULL, PRIMARY KEY(`id`), UNIQUE KEY `u`(`a`,`b`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0;
       
      insert into tu values(1,1,'a','a'),(2,9999,'xxxx','x'),(3,10000,'b','b'),(4,4,'c','c');
       
      set global innodb_status_output=ON;
      set global innodb_status_output_locks=ON;
       
      start transaction;
      delete from tu where a = 9999 and b = 'xxxx';
      show engine innodb status\G
      

      In the output you'll see:

      ------------
      TRANSACTIONS
      ------------
      Trx id counter 636202
      Purge done for trx's n:o < 636201 undo n:o < 0 state: running but idle
      History list length 37
      LIST OF TRANSACTIONS FOR EACH SESSION:
      ---TRANSACTION 636201, ACTIVE 4 sec
      3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
      MySQL thread id 1, OS thread handle 0x7f9e513a7700, query id 90 localhost root init
      show engine innodb status
      TABLE LOCK table `test`.`tu` trx id 636201 lock mode IX
      RECORD LOCKS space id 11 page no 4 n bits 72 index `u` of table `test`.`tu` trx id 636201 lock_mode X locks rec but not gap
      RECORD LOCKS space id 11 page no 3 n bits 72 index `PRIMARY` of table `test`.`tu` trx id 636201 lock_mode X locks rec but not gap
      --------
      

      Normal and expected so far, record X locks on the unique index and primary key, we deleted and could delete one and only one row. Now, repeat the same delete in the same transaction:

      delete from tu where a = 9999 and b = 'xxxx';
      show engine innodb status\G
      

      and you'll see this beautiful set of locks:

      LIST OF TRANSACTIONS FOR EACH SESSION:
      ---TRANSACTION 636201, ACTIVE 202 sec
      5 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 1
      MySQL thread id 1, OS thread handle 0x7f9e513a7700, query id 92 localhost root init
      show engine innodb status
      TABLE LOCK table `test`.`tu` trx id 636201 lock mode IX
      RECORD LOCKS space id 11 page no 4 n bits 72 index `u` of table `test`.`tu` trx id 636201 lock_mode X locks rec but not gap
      RECORD LOCKS space id 11 page no 3 n bits 72 index `PRIMARY` of table `test`.`tu` trx id 636201 lock_mode X locks rec but not gap
      RECORD LOCKS space id 11 page no 4 n bits 72 index `u` of table `test`.`tu` trx id 636201 lock_mode X
      RECORD LOCKS space id 11 page no 4 n bits 72 index `u` of table `test`.`tu` trx id 636201 lock_mode X locks gap before rec
      --------
      

      Suggested fix:
      Check if there is any good reason to ask for a gap X lock on a record in the secondary unique index when transaction already has the next key lock on it.

      Alternatively, if this is just the way locks are reported, please, fix it so the output makes sense.

      In any case, please, document in the manual what locks this kind of DELETE sets when it encountered a record already marked as deleted, and why.

      Attachments

        Issue Links

          Activity

            People

              marko Marko Mäkelä
              jplindst Jan Lindström (Inactive)
              Votes:
              2 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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