Fixed: Deadlock When a MySQL Transaction Inserts a Child Row Before Updating the Parent

Two transactions touch only one product, and in the same order, and they still deadlock with ERROR 1213. The hidden lock comes from the foreign key: inserting a child row takes a shared lock on its parent. When both then try to update the parent, neither can go on. The fix is to swap two lines.

What I Was Doing

I was recording a stock movement and updating the product, on MySQL 9.7.4 in Docker. stock_movements.ProductId has a foreign key to products. Each of two sessions wrote the movement first:

mysql: terminal 1

START TRANSACTION;
INSERT INTO stock_movements (ProductId, Type, QuantityChange, OccurredAtUtc) VALUES (3, 'Receipt', 1, NOW(6));

mysql: terminal 2

START TRANSACTION;
INSERT INTO stock_movements (ProductId, Type, QuantityChange, OccurredAtUtc) VALUES (3, 'Receipt', 1, NOW(6));

Then both updated the product:

mysql: both terminals

UPDATE products SET QuantityOnHand = QuantityOnHand + 1 WHERE Id = 3;

What I Expected

Both inserts add a different row, and the updates take turns on the one row.

What I Got

Terminal 1's UPDATE waited. When terminal 2 ran its own:

Output (terminal 2)

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

What I Checked

The locks after the two inserts. Neither insert changed products, and yet:

mysql: root session

SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
FROM performance_schema.data_locks
WHERE LOCK_TYPE = 'RECORD';

Output

+-------------+------------+-----------+---------------+-------------+-----------+
| OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-------------+------------+-----------+---------------+-------------+-----------+
| products    | PRIMARY    | RECORD    | S,REC_NOT_GAP | GRANTED     | 3         |
| products    | PRIMARY    | RECORD    | S,REC_NOT_GAP | GRANTED     | 3         |
+-------------+------------+-----------+---------------+-------------+-----------+
2 rows in set (0.001 sec)

Two S,REC_NOT_GAP locks on product 3, one for each session.

The locks page. It says: "If a FOREIGN KEY constraint is defined on a table, any insert, update, or delete that requires the constraint condition to be checked sets shared record-level locks on the records that it looks at to check the constraint."

Why It Happens

Each INSERT checked that the product exists, and kept a shared lock on it, so nobody can delete it in the meantime. Shared locks can be held together. Then each UPDATE needs an exclusive lock, which it can't get while the other session holds a shared one. Each waits for the other, for good. It's the same trap as two FOR SHARE readers who both write.

The Fix

Update the parent first. Then the first session holds the exclusive lock from the start, and the second waits at its very first statement, holding nothing:

mysql: terminal 1

START TRANSACTION;
UPDATE products SET QuantityOnHand = QuantityOnHand + 1 WHERE Id = 3;

mysql: terminal 2

START TRANSACTION;
UPDATE products SET QuantityOnHand = QuantityOnHand + 1 WHERE Id = 3;

mysql: terminal 1

INSERT INTO stock_movements (ProductId, Type, QuantityChange, OccurredAtUtc) VALUES (3, 'Receipt', 1, NOW(6));
COMMIT;

mysql: terminal 2

INSERT INTO stock_movements (ProductId, Type, QuantityChange, OccurredAtUtc) VALUES (3, 'Receipt', 1, NOW(6));
COMMIT;
SELECT Id, QuantityOnHand FROM products WHERE Id = 3;

After the Fix

Output (terminal 2, from its UPDATE)

mysql> UPDATE products SET QuantityOnHand = QuantityOnHand + 1 WHERE Id = 3;
Query OK, 1 row affected (2.136 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> INSERT INTO stock_movements (ProductId, Type, QuantityChange, OccurredAtUtc) VALUES (3, 'Receipt', 1, NOW(6));
Query OK, 1 row affected (0.001 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.004 sec)

mysql> SELECT Id, QuantityOnHand FROM products WHERE Id = 3;
+----+----------------+
| Id | QuantityOnHand |
+----+----------------+
|  3 |             52 |
+----+----------------+
1 row in set (0.001 sec)

A short wait instead of a deadlock, and both changes counted.

Through a web API, the same swap removed every deadlock here: 8 requests at once took 9 retries with the child first, and none with the parent first.

Common Mistakes

Mistake	What happens	Do this instead
Writing the history row before the row it describes	This deadlock, under load	Update the parent first
Assuming the same row order means no deadlock	Shared locks from foreign keys are in play too	Look at data_locks
Retrying without changing the order	It keeps deadlocking, and retries cost time	Swap the statements

Troubleshooting

Symptom	Likely cause	Fix
Deadlocks only under load, with one parent row	This pattern	This post
Deadlock report shows lock mode S on the parent	The foreign key's lock	This post
The same thing with an EF Core SaveChanges	EF Core inserts first in that call	Update the parent first, in its own statement

Conclusion

Inserting a child row takes a shared lock on its parent, so two transactions that insert first and then update the parent will deadlock. Update the parent first, and the second transaction simply waits its turn.

Additional Resources

• Locks Set by Different SQL Statements in InnoDB — the foreign key's lock.
• How to Minimize and Handle Deadlocks — general advice.

Related

• FOR SHARE vs FOR UPDATE in MySQL
• How to Avoid Deadlocks in MySQL by Locking Rows in the Same Order
• How to See the Locks a MySQL Transaction Holds