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)

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`)

);

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

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

...