*** WE ROLL BACK TRANSACTION (0)

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;

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)

------------------------

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)

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.