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

Deadlock output in InnoDB status always shows transaction (0)

    XMLWordPrintable

Details

    Description

      The deadlock section in SHOW ENGINE INNODB STATUS is always showing transaction 0 as being rolled back:

      *** WE ROLL BACK TRANSACTION (0)
      

      How to reproduce:

      Use MariaDB 10.8 and generate a deadlock, then check SHOW ENGINE INNODB STATUS\G in the LATEST DETECTED DEADLOCK section.

      mariadb session1> create table t1 (id int primary key, f1 int);
      mariadb session1> insert into t1 values (1,1), (2,2);
      mariadb session1> begin;
      mariadb session1> update t1 set f1 = 3 where id = 1;
       
      mariadb session2> begin;
      mariadb session2> update t1 set f1 = 4 where id = 2;
       
      mariadb session1> update t1 set f1 = 3 where id = 2;
       
      mariadb session2> update t1 set f1 = 5 where id = 1;
      

      – deadlock will be detected here. Check with:

      mariadb> show engine innodb status\G
       
      ------------------------
      LATEST DETECTED DEADLOCK
      ------------------------
      2022-07-28 02:21:20 0x7f1ab00f9700
      *** (1) TRANSACTION:
      TRANSACTION 54, ACTIVE 15 sec starting index read
      mysql tables in use 1, locked 1
      LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
      MariaDB thread id 12, OS thread handle 139752599688960, query id 105 localhost root Updating
      update t1 set f1 = 5 where id = 1
      *** WAITING FOR THIS LOCK TO BE GRANTED:
      RECORD LOCKS space id 6 page no 3 n bits 8 index PRIMARY of table `test`.`t1` trx id 54 lock_mode X locks rec but not gap waiting
      Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
       0: len 4; hex 80000001; asc     ;;
       1: len 6; hex 000000000035; asc      5;;
       2: len 7; hex 16000001480110; asc     H  ;;
       3: len 4; hex 80000003; asc     ;;
       
      *** CONFLICTING WITH:
      RECORD LOCKS space id 6 page no 3 n bits 8 index PRIMARY of table `test`.`t1` trx id 53 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 80000001; asc     ;;
       1: len 6; hex 000000000035; asc      5;;
       2: len 7; hex 16000001480110; asc     H  ;;
       3: len 4; hex 80000003; asc     ;;
       
       
      *** (2) TRANSACTION:
      TRANSACTION 53, ACTIVE 46 sec starting index read
      mysql tables in use 1, locked 1
      LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
      MariaDB thread id 13, OS thread handle 139752600917760, query id 104 localhost root Updating
      update t1 set f1 = 3 where id = 2
      *** WAITING FOR THIS LOCK TO BE GRANTED:
      RECORD LOCKS space id 6 page no 3 n bits 8 index PRIMARY of table `test`.`t1` trx id 53 lock_mode X locks rec but not gap waiting
      Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
       0: len 4; hex 80000002; asc     ;;
       1: len 6; hex 000000000036; asc      6;;
       2: len 7; hex 17000001490110; asc     I  ;;
       3: len 4; hex 80000004; asc     ;;
       
      *** CONFLICTING WITH:
      RECORD LOCKS space id 6 page no 3 n bits 8 index PRIMARY of table `test`.`t1` trx id 54 lock_mode X locks rec but not gap
      Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
       0: len 4; hex 80000002; asc     ;;
       1: len 6; hex 000000000036; asc      6;;
       2: len 7; hex 17000001490110; asc     I  ;;
       3: len 4; hex 80000004; asc     ;;
       
      *** WE ROLL BACK TRANSACTION (0)
      

      As we can see there, there is no TRANSACTION listed with ID 0 in there.
      This is the same if we generate a deadlock involving three transactions:

      ------------------------
      LATEST DETECTED DEADLOCK
      ------------------------
      2022-07-28 01:44:51 0x7f1ab018f700
      *** (1) TRANSACTION:
      TRANSACTION 36, ACTIVE 185 sec starting index read
      ...
      *** (2) TRANSACTION:
      TRANSACTION 37, ACTIVE 171 sec starting index read
      ...
      *** (3) TRANSACTION:
      TRANSACTION 38, ACTIVE 160 sec starting index read
       
      *** WE ROLL BACK TRANSACTION (0)
      

      Let me know if there is anything else I should provide here.

      shell> mysql
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 15
      Server version: 10.8.3-MariaDB-log MariaDB Server
       
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
      

      Thanks!

      Attachments

        Issue Links

          Activity

            People

              danblack Daniel Black
              guliandro Agustin
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.