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

Deadlock on current acquisition of multiple indexes

    XMLWordPrintable

    Details

      Description

      While MDEV-10962 is deadlock on concurrent acquisition of multiple types similarly deadlock happens on concurrent acquisition of multiple indexes.

      Reproduce

      --source include/have_innodb.inc
      create table t1 (a int unique) engine innodb;
      insert into t1 values (1);
      connect (con1, localhost, root,, test);
      send delete from t1;
      connection default;
      --error 0, ER_LOCK_DEADLOCK
      delete from t1 where a = 1;
      show engine innodb status;
      connection con1;
      reap;
      connection default;
      disconnect con1;
      drop table t1;
      

      Result

      *** (1) TRANSACTION:
      TRANSACTION 1287, ACTIVE 0 sec starting index read
      mysql tables in use 1, locked 1
      LOCK WAIT 3 lock struct(s), heap size 1152, 2 row lock(s)
      MySQL thread id 9, OS thread handle 140665229768448, query id 22 localhost root updating
      delete from t1 where a = 1
      *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
      RECORD LOCKS space id 4 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t1` trx id 1287 lock_mode X locks rec but not gap waiting
      Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
       0: len 6; hex 000000000200; asc       ;;
       1: len 6; hex 000000000506; asc       ;;
       2: len 7; hex 04000001460110; asc     F  ;;
       3: len 4; hex 80000001; asc     ;;
       
      *** (2) TRANSACTION:
      TRANSACTION 1286, ACTIVE 0 sec updating or deleting
      mysql tables in use 1, locked 1
      3 lock struct(s), heap size 1152, 2 row lock(s), undo log entries 1
      MySQL thread id 10, OS thread handle 140665229461248, query id 21 localhost root updating
      delete from t1
      *** (2) HOLDS THE LOCK(S):
      RECORD LOCKS space id 4 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t1` trx id 1286 lock_mode X
      Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
       0: len 6; hex 000000000200; asc       ;;
       1: len 6; hex 000000000506; asc       ;;
       2: len 7; hex 04000001460110; asc     F  ;;
       3: len 4; hex 80000001; asc     ;;
       
      *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
      RECORD LOCKS space id 4 page no 4 n bits 72 index a of table `test`.`t1` trx id 1286 lock_mode X locks rec but not gap waiting
      Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
       0: len 4; hex 80000001; asc     ;;
       1: len 6; hex 000000000200; asc       ;;
       
      *** WE ROLL BACK TRANSACTION (1)
      

      Fix

      The fix could be the same principle as for MDEV-10962: prioritization over waiting locks.

        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: