Description:
There are occasions where the extra locks generated to check FK constraints create deadlocks where they are not necessary. By changing the sequence in which locks are requested, we can avoid many deadlock situations.
How to repeat:
Establish two connections, each with their own multi-statement transaction (tx1 and tx2)
Establish two tables fk_parent and fk_child both are populated.
The following sequence of commands will deadlock:
tx1) DELETE FROM fk_child
tx2) INSERT fk_child
tx1) DELETE FROM fk_parent
In the InnoDB "latest deadlock" report we see
tx1 holds an X lock on the PK supremum of fk_child (from the DELETE on the child table)
tx2 wants an X-intention lock on the PK supremum of fk_child (this is naturally waiting for the DELETE FROM fk_child to complete so that it can add a new row)
Then tx1 wants an X on fk_parent (for the DELETE FROM fk_parent). This trips the deadlock because tx2 already has a lock on this table.
I know that tx2 needs to take a lock on fk_parent to check the FK constraint but since it is waiting for its own x-intention to be granted to access the child table (to add data to it), why does it already have a lock on fk_parent?
Suggested fix:
Invert the sequence of the locking.
In the case of tx2, the primary action to perform is an INSERT to fk_child. However, it must wait to do this until it can get a lock on fk_child (an X-intention lock in this case).
Since tx2 is waiting for a lock to perform its primary function (the insert), why does it already have a lock to perform its secondary function (ensure the FK constraint is satisfied) ?
I suggest that this INSERT would have happened just as accurately and much more smoothly if tx2 had delayed ASKING for the lock on fk_parent until after the lock on fk_child had been granted.
Both locks need to happen for the INSERT to happen safely. However taking the parent lock only after the child lock had been granted would have avoided this deadlock.
© 2025, Oracle Corporation and/or its affiliates