2

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:

Database Entity Picture

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: 1    Bob Newhart
2   Mick Jagger

PHONE: enter image description here

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';

enter image description here enter image description here

enter image description here

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 <Script-9.sql> */ 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 <Script-10.sql> */ 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[57]:cur[0], unzip sum[0]:cur[0]
    --------------
    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 ??

4
  • 2
    The PRIMARY KEY is, by definition, Unique. Remove the unnecessary extra uniqueness constraint on ID.
    – Rick James
    Nov 22, 2022 at 21:22
  • @RickJames Thanks - correct, but that has no impact on the issue described. Question remains why is delete from PHONE where PERSON_ID = 2; trying to lock a row not part of the person_id index? Why is it trying to lock row 1 here? Nov 23, 2022 at 12:10
  • @ChrisLandry Are you looking for an explanation as to why exactly this is happening, or are you looking for a solution to avoid this problem? Nov 23, 2022 at 13:49
  • @WillemRenzema explanation on why it's locking the row that's not part of the index it's using the in where clause. Nov 30, 2022 at 20:28

2 Answers 2

1

So I got some help from my friends at Percona.

There are some conditions in which the database will ignore the person_id index and decide it's more performant to run an operation using a full table scan. This includes if the table is small, or there's a high percentage of the table rows that indices will return (Low Cardinality). See more on that here: https://docs.oracle.com/cd/E17952_01/mysql-5.6-en/table-scan-avoidance.html

So in our case mySQL starts out using the PERSON_ID index and then determined to do the full table scan which then went after locking rows for other PERSON_IDs.

To resolve this you can use the FORCE INDEX in your sql statements:

DELETE PHONE.* FROM PHONE FORCE INDEX (PERSON_ID_FK) where PHONE.PERSON_ID = 1;

OR you can set this more globally using

SET SESSION max_seeks_for_key=1;

NOTE: This could have impacts on completely different scenarios where you have low cardinality so your index is much slower than the table scan.

0

I may characterize this as: run 2+ transactions to use where clause to identify the records and run insert/delete records (that actually change the answer of where clause).

I am thinking,imagine it is a filter against a live list, insert/delete changes the answer of where clause. so I feel it is not possible to make the two transations atomic and concurrent, we either end up with deadlock or phantom read /race condition by choosing different isolation level.

Would it make sense to insert primary keys into a temp table to 'freeze' the list, then update statement will be temp table join phone table on primary key? Or we can try to use delete flag column to soft delete. (a cleanup can be done periodically)

(I am nowhere close to database expert, so it maybe very naif thought)

https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

I feel this section of document may be related: in lock condition, each record is LOCKED until where statement is evaluated.

Using READ COMMITTED has additional effects:

For UPDATE or DELETE statements, InnoDB holds locks only for rows that it updates or deletes. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition. This greatly reduces the probability of deadlocks, but they can still happen.

For UPDATE statements, if a row is already locked, InnoDB performs a “semi-consistent” read, returning the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE. If the row matches (must be updated), MySQL reads the row again and this time InnoDB either locks it or waits for a lock on it.

1
  • thanks for the response. I think if this statement was true: "in lock condition, each record is LOCKED until where statement is evaluated." then we should have had the lock issue immediately.....as soon as the second transaction tried its first delete because the first transaction had a lock on that id: 1...in fact it works fine for many iterations before it suddenly tries for the exclusive lock on record 1? (Why not for the previous 5 deletes?). Nov 24, 2022 at 0:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.