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

Deadlock on implicit locking and parallel read

    XMLWordPrintable

Details

    Description

      Reproduce

      --source include/have_innodb.inc
       
      create or replace table t1 (pk int primary key, x int) engine innodb;
      start transaction;
      insert into t1 values (3, 1);
      connect (con1,localhost,root,,test);
      send select * from t1 for update;
      connection default;
      --let $wait_condition= select count(*) from information_schema.innodb_lock_waits
      --source include/wait_condition.inc
      insert into t1 values (1, 1);
      commit;
      connection con1;
      --error ER_LOCK_DEADLOCK
      reap;
      connection default;
      show engine innodb status;
      disconnect con1;
      drop table t1;
      

      Result

      ------------------------
      LATEST DETECTED DEADLOCK
      ------------------------
      2019-08-07 13:20:09 0x7f37e1cee700
      *** (1) TRANSACTION:
      TRANSACTION 37, ACTIVE 0 sec starting index read
      mysql tables in use 1, locked 1
      LOCK WAIT 2 lock struct(s), heap size 1160, 1 row lock(s)
      MySQL thread id 10, OS thread handle 139877988054784, query id 24 localhost root Sending data
      select * from t1 for update
      *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
      RECORD LOCKS space id 5 page no 3 n bits 72 index PRIMARY of table `test`.`t1` trx id 37 lock_mode X waiting
      Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
       0: len 4; hex 80000003; asc     ;;
       1: len 6; hex 000000000024; asc      $;;
       2: len 7; hex 830000013702c3; asc     7  ;;
       3: len 4; hex 80000001; asc     ;;
       
      *** (2) TRANSACTION:
      TRANSACTION 36, ACTIVE 0 sec inserting
      mysql tables in use 1, locked 1
      3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1
      MySQL thread id 9, OS thread handle 139877988361984, query id 26 localhost root Update
      insert into t1 values (1, 1)
      *** (2) HOLDS THE LOCK(S):
      RECORD LOCKS space id 5 page no 3 n bits 72 index PRIMARY of table `test`.`t1` trx id 36 lock_mode X locks rec but not gap
      Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
       0: len 4; hex 80000003; asc     ;;
       1: len 6; hex 000000000024; asc      $;;
       2: len 7; hex 830000013702c3; asc     7  ;;
       3: len 4; hex 80000001; asc     ;;
       
      *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
      RECORD LOCKS space id 5 page no 3 n bits 72 index PRIMARY of table `test`.`t1` trx id 36 lock_mode X locks gap before rec insert intention waiting
      Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
       0: len 4; hex 80000003; asc     ;;
       1: len 6; hex 000000000024; asc      $;;
       2: len 7; hex 830000013702c3; asc     7  ;;
       3: len 4; hex 80000001; asc     ;;
      

      Cause

      When implicit lock is converted to explicit it does not lock the gap: LOCK_REC | LOCK_X | LOCK_REC_NOT_GAP. On the other hand explicit insert intention does lock the gap: LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION. Explicit lock is skipped when there is no existing locks for the record.

      Fix candidates

      1. Implicit lock converted into insert intention: LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION. I don't see a reason why it is converted into different mode.
      2. When inserting into a gap locked by the same transaction do not create explicit lock. We don't have to wait for locks in same transaction.

      Analysis

      Attachments

        Issue Links

          Activity

            People

              midenok Aleksey Midenkov
              midenok Aleksey Midenkov
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.