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

Deadlock on implicit locking and parallel read

    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

              • Assignee:
                midenok Aleksey Midenkov
                Reporter:
                midenok Aleksey Midenkov
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated: