create table ParentTable

(

    id  bigint auto_increment primary key,

    uid varchar(50) not null

);

 create table ChildTable

(

    id             bigint auto_increment primary key,

    uid            varchar(50) not null,

    parentTable_id bigint not null,

    constraint FK_ChildTable_To_ParentTable foreign key (parentTable_id) references ParentTable (id)

);

create index IDX_ParentTable_UID on ParentTable (uid);

create index IDX_ChildTable_UID on ChildTable (uid);

INSERT INTO ParentTable(uid) VALUES('a');

INSERT INTO ChildTable(uid, parentTable_id) VALUES('a', 1);

INSERT INTO ChildTable(uid, parentTable_id) VALUES('b', 1);

SET autocommit=0;

START TRANSACTION;

insert into ChildTable(uid, parentTable_id) VALUES('c', 1);

SET autocommit=0;

START TRANSACTION;

update ParentTable set uid='tran-2' where id = 1;

UPDATE ParentTable SET uid = 'tran-1' WHERE id = 1;

Error Code: 1213. Deadlock found when trying to get lock; try restarting transaction

 =====================================

 2024-07-26 11:52:53 0x7f4b8c060640 INNODB MONITOR OUTPUT

 =====================================

 Per second averages calculated from the last 0 seconds

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

 BACKGROUND THREAD

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

 srv_master_thread loops: 0 srv_active, 15401 srv_idle

 srv_master_thread log flush and writes: 15391

 ----------

 SEMAPHORES

 ----------

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

 LATEST DETECTED DEADLOCK

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

 2024-07-26 11:52:45 0x7f4b8c0f6640

 *** (1) TRANSACTION:

 TRANSACTION 136, ACTIVE 31 sec starting index read

 mysql tables in use 1, locked 1

 LOCK WAIT 5 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1

 MariaDB thread id 4, OS thread handle 139962449094208, query id 251 192.168.14.89 root Updating

 UPDATE ParentTable SET uid = 'tran-1' WHERE id = 1

 *** WAITING FOR THIS LOCK TO BE GRANTED:

 RECORD LOCKS space id 8 page no 3 n bits 8 index PRIMARY of table `prusi`.`ParentTable` trx id 136 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 8; hex 8000000000000001; asc         ;;

  1: len 6; hex 000000000000; asc       ;;

  2: len 7; hex 80000000000000; asc        ;;

  3: len 6; hex 7472616e2d31; asc tran-1;;

 *** CONFLICTING WITH:

 RECORD LOCKS space id 8 page no 3 n bits 8 index PRIMARY of table `prusi`.`ParentTable` trx id 136 lock mode S locks rec but not gap

 Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

  0: len 8; hex 8000000000000001; asc         ;;

  1: len 6; hex 000000000000; asc       ;;

  2: len 7; hex 80000000000000; asc        ;;

  3: len 6; hex 7472616e2d31; asc tran-1;;

 *** (2) TRANSACTION:

 TRANSACTION 137, ACTIVE 12 sec starting index read

 mysql tables in use 1, locked 1

 LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s)

 MariaDB thread id 6, OS thread handle 139962448479808, query id 249 192.168.14.89 root Updating

 update ParentTable

 set uid='tran-2'

 where id = 1

 *** WAITING FOR THIS LOCK TO BE GRANTED:

 RECORD LOCKS space id 8 page no 3 n bits 8 index PRIMARY of table `prusi`.`ParentTable` trx id 137 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 8; hex 8000000000000001; asc         ;;

  1: len 6; hex 000000000000; asc       ;;

  2: len 7; hex 80000000000000; asc        ;;

  3: len 6; hex 7472616e2d31; asc tran-1;;

 *** CONFLICTING WITH:

 RECORD LOCKS space id 8 page no 3 n bits 8 index PRIMARY of table `prusi`.`ParentTable` trx id 136 lock mode S locks rec but not gap

 Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

  0: len 8; hex 8000000000000001; asc         ;;

  1: len 6; hex 000000000000; asc       ;;

  2: len 7; hex 80000000000000; asc        ;;

  3: len 6; hex 7472616e2d31; asc tran-1;;

 *** WE ROLL BACK TRANSACTION (1)

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

 TRANSACTIONS

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

 Trx id counter 138

 Purge done for trx's n:o < 136 undo n:o < 0 state: running but idle

 History list length 0

 LIST OF TRANSACTIONS FOR EACH SESSION:

 ---TRANSACTION (0x7f4b8c9df680), not started

 0 lock struct(s), heap size 1128, 0 row lock(s)

 ---TRANSACTION 136, ACTIVE 39 sec

 5 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1

 MariaDB thread id 4, OS thread handle 139962449094208, query id 251 192.168.14.89 root 

 --------

 FILE I/O

 --------

 Pending flushes (fsync): 0

 188 OS file reads, 17 OS file writes, 18 OS fsyncs

 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

 ---

 LOG

 ---

 Log sequence number 132359

 Log flushed up to   132359

 Pages flushed up to 110251

 Last checkpoint at  110235

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

 BUFFER POOL AND MEMORY

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

 Total large memory allocated 167772160

 Dictionary memory allocated 857160

 Buffer pool size   8064

 Free buffers       7759

 Database pages     305

 Old database pages 0

 Modified db pages  27

 Percent of dirty pages(LRU & free pages): 0.335

 Max dirty pages percent: 90.000

 Pending reads 0

 Pending writes: LRU 0, flush list 0

 Pages made young 0, not young 0

 0.00 youngs/s, 0.00 non-youngs/s

 Pages read 167, created 138, written 0

 0.00 reads/s, 0.00 creates/s, 0.00 writes/s

 No buffer pool page gets since the last printout

 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s

 LRU len: 305, unzip_LRU len: 0

 I/O sum[0]:cur[0], unzip sum[0]:cur[0]

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

 ROW OPERATIONS

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

 0 read views open inside InnoDB

 state: sleeping

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

 END OF INNODB MONITOR OUTPUT

 ============================