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

Innodb_deadlocks status variable counts wrong

    XMLWordPrintable

Details

    Description

      When I provoke ONE deadlock the Innodb_deadlocks status counter is NOT increased. This contradicts the documentation:

      Documentation states: Total number of InnoDB deadlocks.
      https://mariadb.com/kb/en/innodb-status-variables/#innodb_deadlocks

      SQL> SHOW GLOBAL STATUS LIKE '%dead%';
      +------------------+-------+
      | Variable_name    | Value |
      +------------------+-------+
      | Innodb_deadlocks | 0     |
      +------------------+-------+
       
      SQL 1> START TRANSACTION;   -- Session 1
      SQL 2> START TRANSACTION;   -- Session 2
      SQL 1> UPDATE test SET data = 'bla' WHERE id = 2;
      SQL 2> UPDATE test SET data = 'bla' WHERE id = 1524;
      SQL 1> UPDATE test SET data = 'bla' WHERE id = 1524;
      -- Session 1 hangs 
      SQL 2> UPDATE test SET data = 'bla' WHERE id = 2;
      ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
       
      SQL> SHOW GLOBAL STATUS LIKE '%dead%';
      +------------------+-------+
      | Variable_name    | Value |
      +------------------+-------+
      | Innodb_deadlocks | 0     |
      +------------------+-------+
       
      SQL> SHOW ENGINE INNODB STATUS\G
      ------------------------
      LATEST DETECTED DEADLOCK
      ------------------------
      2024-04-18 14:30:34 0x7f47ef687640
      *** (1) TRANSACTION:
      TRANSACTION 1723789, ACTIVE 14 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
      MySQL thread id 382, OS thread handle 139946933352000, query id 139416 localhost root Updating
      UPDATE test SET data = 'bla' WHERE id = 1524
      ...
       
      *** (2) TRANSACTION:
      TRANSACTION 1723792, ACTIVE 8 sec starting index read
      mysql tables in use 1, locked 1
      3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
      MySQL thread id 383, OS thread handle 139946936006208, query id 139449 localhost root Updating
      UPDATE test SET data = 'bla' WHERE id = 2
      ...
       
      *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
      RECORD LOCKS space id 13389 page no 4 n bits 480 index PRIMARY of table `test`.`test` trx id 1723792 lock_mode X locks rec but not gap waiting
      Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
      ...
       
      *** WE ROLL BACK TRANSACTION (2)
      

      Table:

      CREATE TABLE `test` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `data` varchar(128) DEFAULT NULL,
        `ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
        PRIMARY KEY (`id`)
      );
      

      Data:

      INSERT INTO test SELECT NULL, 'Some data', NOW();
      INSERT INTO test SELECT NULL, 'Some data', NOW() FROM test;
      ...
      

      Attachments

        Issue Links

          Activity

            People

              thiru Thirunarayanan Balathandayuthapani
              oli Oli Sennhauser
              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.