[MDEV-33935] Innodb_deadlocks status variable counts wrong - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.5.17, 10.6.17, 10.11.7, 11.5.1
Fix Version/s: 10.6.19, 10.11.9, 11.1.6, 11.2.5, 11.4.3, 11.5.2
Component/s: Storage Engine - InnoDB
Labels:
Environment:
Ubuntu Linux 22.04, n.a.

Description

When I provoke ONE deadlock the Innodb_deadlocks status counter is increase by TWO. This oversubscribes the number of deadlocks happening in a mariadb database.

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

So IMHO it should be 1 and not 2.

Reproducible:

SQL> SHOW GLOBAL STATUS LIKE '%dead%';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| Innodb_deadlocks | 0     |

+------------------+-------+

Session 1> START TRANSACTION;

Session 2> START TRANSACTION;

Session 1> UPDATE test SET data = 'bla' WHERE id = 2;

Session 2> UPDATE test SET data = 'bla' WHERE id = 1524;

Session 1> UPDATE test SET data = 'bla' WHERE id = 1524;

Session 2> UPDATE test SET data = 'bla' WHERE id = 2;

SQL> SHOW GLOBAL STATUS LIKE '%dead%';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| Innodb_deadlocks | 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

blocks

MDEV-20043 Innodb_deadlocks status counter

Closed

relates to

MDEV-33937 Innodb_deadlocks status variable counts wrong

Open

Activity

Ascending order - Click to sort in descending order

Oli Sennhauser created issue - 2024-04-18 07:24

Oli Sennhauser made changes - 2024-04-18 07:24

Field	Original Value	New Value
Link		This issue blocks ~~MDEV-20043~~ [ ~~MDEV-20043~~ ]

Sergei Golubchik made changes - 2024-04-18 09:38

Description

When I provoke ONE deadlock the Innodb_deadlocks status counter is increase by TWO. This oversubscribes the number of deadlocks happening in a mariadb database.

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

So IMHO it should be 1 and not 2.

Reproducible:
SQL> SHOW GLOBAL STATUS LIKE '%dead%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_deadlocks | 0 |
+------------------+-------+

SQL> START TRANSACTION; -- Session 1
SQL> UPDATE test SET data = 'bla' WHERE id = 2;
SQL> UPDATE test SET data = 'bla' WHERE id = 1524;

SQL> START TRANSACTION; -- Session 2
SQL> UPDATE test SET data = 'bla' WHERE id = 1524;
SQL> UPDATE test SET data = 'bla' WHERE id = 2;

SQL> SHOW GLOBAL STATUS LIKE '%dead%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_deadlocks | 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;
...

When I provoke ONE deadlock the Innodb_deadlocks status counter is increase by TWO. This oversubscribes the number of deadlocks happening in a mariadb database.

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

So IMHO it should be 1 and not 2.

Reproducible:
{noformat}
SQL> SHOW GLOBAL STATUS LIKE '%dead%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_deadlocks | 0 |
+------------------+-------+

SQL> START TRANSACTION; -- Session 1
SQL> UPDATE test SET data = 'bla' WHERE id = 2;
SQL> UPDATE test SET data = 'bla' WHERE id = 1524;

SQL> START TRANSACTION; -- Session 2
SQL> UPDATE test SET data = 'bla' WHERE id = 1524;
SQL> UPDATE test SET data = 'bla' WHERE id = 2;

SQL> SHOW GLOBAL STATUS LIKE '%dead%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_deadlocks | 2 |
+------------------+-------+
{noformat}
Table:
{code:sql}
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`)
);
{code}
Data:
{code:sql}
INSERT INTO test SELECT NULL, 'Some data', NOW();
INSERT INTO test SELECT NULL, 'Some data', NOW() FROM test;
...
{code}

Sergei Golubchik made changes - 2024-04-18 09:39

Assignee

Thirunarayanan Balathandayuthapani [ thiru ]

Oli Sennhauser made changes - 2024-04-18 12:38

Link

This issue relates to MDEV-33937 [ MDEV-33937 ]

Andrew Hutchings (Inactive) made changes - 2024-04-18 12:54

Description

When I provoke ONE deadlock the Innodb_deadlocks status counter is increase by TWO. This oversubscribes the number of deadlocks happening in a mariadb database.

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

So IMHO it should be 1 and not 2.

Reproducible:
{noformat}
SQL> SHOW GLOBAL STATUS LIKE '%dead%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_deadlocks | 0 |
+------------------+-------+

SQL> START TRANSACTION; -- Session 1
SQL> UPDATE test SET data = 'bla' WHERE id = 2;
SQL> UPDATE test SET data = 'bla' WHERE id = 1524;

SQL> START TRANSACTION; -- Session 2
SQL> UPDATE test SET data = 'bla' WHERE id = 1524;
SQL> UPDATE test SET data = 'bla' WHERE id = 2;

SQL> SHOW GLOBAL STATUS LIKE '%dead%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_deadlocks | 2 |
+------------------+-------+
{noformat}
Table:
{code:sql}
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`)
);
{code}
Data:
{code:sql}
INSERT INTO test SELECT NULL, 'Some data', NOW();
INSERT INTO test SELECT NULL, 'Some data', NOW() FROM test;
...
{code}

When I provoke ONE deadlock the Innodb_deadlocks status counter is increase by TWO. This oversubscribes the number of deadlocks happening in a mariadb database.

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

So IMHO it should be 1 and not 2.

Reproducible:
{noformat}
SQL> SHOW GLOBAL STATUS LIKE '%dead%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_deadlocks | 0 |
+------------------+-------+

Session 1> START TRANSACTION;
Session 2> START TRANSACTION;
Session 1> UPDATE test SET data = 'bla' WHERE id = 2;
Session 2> UPDATE test SET data = 'bla' WHERE id = 1524;
Session 1> UPDATE test SET data = 'bla' WHERE id = 1524;
Session 2> UPDATE test SET data = 'bla' WHERE id = 2;

SQL> SHOW GLOBAL STATUS LIKE '%dead%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_deadlocks | 2 |
+------------------+-------+
{noformat}
Table:
{code:sql}
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`)
);
{code}
Data:
{code:sql}
INSERT INTO test SELECT NULL, 'Some data', NOW();
INSERT INTO test SELECT NULL, 'Some data', NOW() FROM test;
...
{code}

Sergei Golubchik made changes - 2024-06-11 14:49

Assignee

Thirunarayanan Balathandayuthapani [ thiru ]

Sergei Golubchik [ serg ]