Why is MySQL Delete on Foreign Key Index locking a record not part of the index (leads to DEADLOCK)

I have a situation where I am executing a delete statement by a FK index that sometimes tries to lock a row that is not part of that index. I'm not sure why this could happen. **Here's my scenario:** **SETUP** Using MySQL 8.0.31 with transaction_isolation READ-COMMITTED I have this very simplified relationship where a phone number refers back to a person entity through a foreign key relationship: Schema is as follows: create table PERSON ( ID BIGINT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(100) NOT NULL); create table PHONE ( ID BIGINT PRIMARY KEY AUTO_INCREMENT, PERSON_ID BIGINT NOT NULL, PHONE VARCHAR(100) NOT NULL, constraint PERSON_ID_FK foreign key (PERSON_ID) references PERSON(ID)); **Data looks like this** PERSON: PHONE: **SCENARIO:** I then create two transactions that are running concurrently: **Transaction-71 does a series of insert / deletes for phone number against person #1** START TRANSACTION; insert into PHONE (PERSON_ID, PHONE) values (1, '416-111-1111'); delete from PHONE where PERSON_ID = 1; insert into PHONE (PERSON_ID, PHONE) values (1, '416-111-1111'); delete from PHONE where PERSON_ID = 1; insert into PHONE (PERSON_ID, PHONE) values (1, '416-111-1111'); delete from PHONE where PERSON_ID = 1; insert into PHONE (PERSON_ID, PHONE) values (1, '416-111-1111'); delete from PHONE where PERSON_ID = 1; NOTE: It does not commit though.... **Transaction-72 does a series of insert / deletes for phone number against person #2** START TRANSACTION; insert into PHONE (PERSON_ID, PHONE) values (2, '416-222-2222'); delete from PHONE where PERSON_ID = 2; insert into PHONE (PERSON_ID, PHONE) values (2, '416-222-2222'); delete from PHONE where PERSON_ID = 2; insert into PHONE (PERSON_ID, PHONE) values (2, '416-222-2222'); delete from PHONE where PERSON_ID = 2; insert into PHONE (PERSON_ID, PHONE) values (2, '416-222-2222'); delete from PHONE where PERSON_ID = 2; For a while this works as you might expect....neither transaction steps on each others because person_id is indexed and our inserts and deletes are based off this index. **But at some point (non-deterministic) one of the transactions suddenly tries to lock the other's primary index row on delete even though that row is not in the index for the person they sql is trying to delete** In this example below *delete from PHONE where PERSON_ID = 1;* results in the database trying to lock row with id 9. But that row is indexed to PERSON_ID = 2...so this query should not be trying to lock it?? select * from performance_schema.data_locks dl where OBJECT_NAME = 'phone'; And then it will eventually lead to deadlock. ===================================== 2022-11-22 15:41:34 0x7000052b9000 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 22 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 139 srv_active, 0 srv_shutdown, 30634 srv_idle srv_master_thread log flush and writes: 0 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 42 OS WAIT ARRAY INFO: signal count 42 RW-shared spins 0, rounds 0, OS waits 0 RW-excl spins 0, rounds 0, OS waits 0 RW-sx spins 0, rounds 0, OS waits 0 Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2022-11-22 15:41:12 0x7000042f2000 *** (1) TRANSACTION: TRANSACTION 2748, ACTIVE 3904 sec mysql tables in use 1, locked 1 LOCK WAIT 6 lock struct(s), heap size 1128, 14 row lock(s), undo log entries 12 MySQL thread id 27, OS thread handle 123145389051904, query id 2015 localhost 127.0.0.1 root updating /* ApplicationName=DBeaver 22.2.3 - SQLEditor */ delete from PHONE where PERSON_ID = 1 *** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 7 page no 4 n bits 72 index PRIMARY of table foo.phone trx id 2748 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 8000000000000008; asc ;; 1: len 6; hex 000000000abc; asc ;; 2: len 7; hex 01000001050348; asc H;; 3: len 8; hex 8000000000000001; asc ;; 4: len 12; hex 3431362d3131312d31313131; asc 416-111-1111;; Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 800000000000000a; asc ;; 1: len 6; hex 000000000abc; asc ;; 2: len 7; hex 0100000105037e; asc ~;; 3: len 8; hex 8000000000000001; asc ;; 4: len 12; hex 3431362d3131312d31313131; asc 416-111-1111;; Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 800000000000000c; asc ;; 1: len 6; hex 000000000abc; asc ;; 2: len 7; hex 010000010503b4; asc ;; 3: len 8; hex 8000000000000001; asc ;; 4: len 12; hex 3431362d3131312d31313131; asc 416-111-1111;; Record lock, heap no 8 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 800000000000000e; asc ;; 1: len 6; hex 000000000abc; asc ;; 2: len 7; hex 010000010503ea; asc ;; 3: len 8; hex 8000000000000001; asc ;; 4: len 12; hex 3431362d3131312d31313131; asc 416-111-1111;; Record lock, heap no 10 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 8000000000000010; asc ;; 1: len 6; hex 000000000abc; asc ;; 2: len 7; hex 01000001050420; asc ;; 3: len 8; hex 8000000000000001; asc ;; 4: len 12; hex 3431362d3131312d31313131; asc 416-111-1111;; *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 7 page no 4 n bits 88 index PRIMARY of table foo.phone trx id 2748 lock_mode X locks rec but not gap waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 8000000000000009; asc ;; 1: len 6; hex 000000000ac1; asc ;; 2: len 7; hex 02000001690151; asc i Q;; 3: len 8; hex 8000000000000002; asc ;; 4: len 12; hex 3431362d3232322d32323232; asc 416-222-2222;; *** (2) TRANSACTION: TRANSACTION 2753, ACTIVE 3893 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 6 lock struct(s), heap size 1128, 16 row lock(s), undo log entries 19 MySQL thread id 28, OS thread handle 123145390116864, query id 2053 localhost 127.0.0.1 root updating /* ApplicationName=DBeaver 22.2.3 - SQLEditor */ delete from PHONE where PERSON_ID = 2 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 7 page no 4 n bits 72 index PRIMARY of table foo.phone trx id 2753 lock_mode X locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 8000000000000009; asc ;; 1: len 6; hex 000000000ac1; asc ;; 2: len 7; hex 02000001690151; asc i Q;; 3: len 8; hex 8000000000000002; asc ;; 4: len 12; hex 3431362d3232322d32323232; asc 416-222-2222;; Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 800000000000000b; asc ;; 1: len 6; hex 000000000ac1; asc ;; 2: len 7; hex 02000001690187; asc i ;; 3: len 8; hex 8000000000000002; asc ;; 4: len 12; hex 3431362d3232322d32323232; asc 416-222-2222;; Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 800000000000000d; asc ;; 1: len 6; hex 000000000ac1; asc ;; 2: len 7; hex 020000016901bd; asc i ;; 3: len 8; hex 8000000000000002; asc ;; 4: len 12; hex 3431362d3232322d32323232; asc 416-222-2222;; Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 800000000000000f; asc ;; 1: len 6; hex 000000000ac1; asc ;; 2: len 7; hex 020000016901f3; asc i ;; 3: len 8; hex 8000000000000002; asc ;; 4: len 12; hex 3431362d3232322d32323232; asc 416-222-2222;; Record lock, heap no 11 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 8000000000000011; asc ;; 1: len 6; hex 000000000ac1; asc ;; 2: len 7; hex 02000001690229; asc i );; 3: len 8; hex 8000000000000002; asc ;; 4: len 12; hex 3431362d3232322d32323232; asc 416-222-2222;; Record lock, heap no 16 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 8000000000000016; asc ;; 1: len 6; hex 000000000ac1; asc ;; 2: len 7; hex 0200000169025f; asc i _;; 3: len 8; hex 8000000000000002; asc ;; 4: len 12; hex 3431362d3232322d32323232; asc 416-222-2222;; Record lock, heap no 17 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 8000000000000017; asc ;; 1: len 6; hex 000000000ac1; asc ;; 2: len 7; hex 02000001690295; asc i ;; 3: len 8; hex 8000000000000002; asc ;; 4: len 12; hex 3431362d3232322d32323232; asc 416-222-2222;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 7 page no 4 n bits 88 index PRIMARY of table foo.phone trx id 2753 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 8000000000000008; asc ;; 1: len 6; hex 000000000abc; asc ;; 2: len 7; hex 01000001050348; asc H;; 3: len 8; hex 8000000000000001; asc ;; 4: len 12; hex 3431362d3131312d31313131; asc 416-111-1111;; *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 2789 Purge done for trx's n:o < 2789 undo n:o < 0 state: running but idle History list length 5 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421805873481168, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421805873479584, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421805873478792, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421805873478000, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421805873477208, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 2753, ACTIVE 3915 sec 6 lock struct(s), heap size 1128, 25 row lock(s), undo log entries 34 MySQL thread id 28, OS thread handle 123145390116864, query id 2104 localhost 127.0.0.1 root -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (read thread) I/O thread 4 state: waiting for i/o request (read thread) I/O thread 5 state: waiting for i/o request (read thread) I/O thread 6 state: waiting for i/o request (write thread) I/O thread 7 state: waiting for i/o request (write thread) I/O thread 8 state: waiting for i/o request (write thread) I/O thread 9 state: waiting for i/o request (write thread) Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] , ibuf aio reads:, log i/o's: Pending flushes (fsync) log: 0; buffer pool: 0 880 OS file reads, 5566 OS file writes, 3904 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 3.01 writes/s, 2.43 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 4 buffer(s) Hash table size 34679, node heap has 1 buffer(s) Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 1 buffer(s) Hash table size 34679, node heap has 1 buffer(s) 1.50 hash searches/s, 4.27 non-hash searches/s --- LOG --- Log sequence number 20296174 Log buffer assigned up to 20296174 Log buffer completed up to 20296174 Log written up to 20296174 Log flushed up to 20296174 Added dirty pages up to 20296174 Pages flushed up to 20296174 Last checkpoint at 20296174 Log minimum file id is 5 Log maximum file id is 6 1236 log i/o's done, 0.91 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 0 Dictionary memory allocated 548441 Buffer pool size 8191 Free buffers 6972 Database pages 1212 Old database pages 427 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 919, not young 369 0.00 youngs/s, 0.00 non-youngs/s Pages read 856, created 359, written 2997 0.00 reads/s, 0.09 creates/s, 1.48 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 1212, unzip_LRU len: 0 I/O sum:cur, unzip sum:cur -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Process ID=5057, Main thread ID=0x7000044fe000 , state=sleeping Number of rows inserted 229, updated 0, deleted 112, read 253 0.23 inserts/s, 0.00 updates/s, 0.45 deletes/s, 1.18 reads/s Number of system rows inserted 150, updated 455, deleted 93, read 65988 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ **Question is why is mySQL trying to acquire a lock on a record that is not indexed for person_id = 1 ??**