Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
-
None
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
- relates to
-
MDEV-24738 Improve the InnoDB deadlock checker
- Closed