Fixed: Deadlock on INSERT After SELECT FOR UPDATE on a Missing Row in MySQL

"Lock the row if it's there, and insert it if it isn't" sounds like the careful way to do it. On MySQL, two sessions doing that at once deadlock on the INSERT, with ERROR 1213. Asking for a row that doesn't exist locks a gap, and gap locks don't block each other, until both sessions try to insert.

What I Was Doing

I wanted to add a product only if nobody had added it yet, on MySQL 9.7.4 in Docker. So each of two sessions checked first, inside a transaction:

mysql: terminal 1

START TRANSACTION;
SELECT Id, Sku FROM products WHERE Id = 1000 FOR UPDATE;

mysql: terminal 2

START TRANSACTION;
SELECT Id, Sku FROM products WHERE Id = 1000 FOR UPDATE;

Both came back with Empty set, so both went on to insert:

mysql: both terminals

INSERT INTO products (Sku, Name, CategoryId, UnitPrice, QuantityOnHand, ReorderLevel, IsArchived, Version, CreatedAtUtc, UpdatedAtUtc)
VALUES ('GAP-A', 'Gap A', 1, 1, 0, 0, 0, 0, NOW(6), NOW(6));

What I Expected

FOR UPDATE was meant to make one of them wait, so that the second would find the row.

What I Got

Neither SELECT waited. Terminal 1's INSERT waited, and when terminal 2 ran its own:

Output (terminal 2's INSERT)

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

What I Checked

What each SELECT locked. With no row to lock, it locks the gap where the row would be:

Output (performance_schema.data_locks, from a root session)

+------------+-----------+-------------+------------------------+
| INDEX_NAME | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
+------------+-----------+-------------+------------------------+
| PRIMARY    | X         | GRANTED     | supremum pseudo-record |
+------------+-----------+-------------+------------------------+
1 row in set (0.001 sec)

What the docs say about gap locks. The InnoDB locking page says: "Gap locks in InnoDB are 'purely inhibitive', which means that their only purpose is to prevent other transactions from inserting to the gap." So two sessions can hold the same gap, and each stops the other's insert.

Why It Happens

Both sessions got the gap lock, as these locks don't conflict. Each INSERT then needs that gap to be free, and each is stopped by the other's lock. That's a cycle, so MySQL rolled one back. How Gap Locks Block an INSERT in MySQL shows the lock itself.

The Fix

Skip the check. Insert directly, and let a unique index decide who wins. The second session then waits for the first one to finish, instead of deadlocking:

mysql: terminal 1

START TRANSACTION;
INSERT INTO products (Sku, Name, CategoryId, UnitPrice, QuantityOnHand, ReorderLevel, IsArchived, Version, CreatedAtUtc, UpdatedAtUtc)
VALUES ('GAP-A', 'Gap A', 1, 1, 0, 0, 0, 0, NOW(6), NOW(6));

mysql: terminal 2

START TRANSACTION;
INSERT INTO products (Sku, Name, CategoryId, UnitPrice, QuantityOnHand, ReorderLevel, IsArchived, Version, CreatedAtUtc, UpdatedAtUtc)
VALUES ('GAP-A', 'Gap A', 1, 1, 0, 0, 0, 0, NOW(6), NOW(6));

mysql: terminal 1

COMMIT;

Then treat the duplicate error as "someone else got there first".

After the Fix

Output (terminal 2's INSERT, then a SELECT after its ROLLBACK)

ERROR 1062 (23000): Duplicate entry 'GAP-A' for key 'products.IX_products_Sku'

+----+-------+
| Id | Sku   |
+----+-------+
| 29 | GAP-A |
+----+-------+
1 row in set (0.001 sec)

One row, one clear answer for the loser, and no deadlock. Fixed: ERROR 1062 and How to Return 409 Conflict for a Duplicate Key handle the error.

Common Mistakes

Mistake	What happens	Do this instead
"Check with FOR UPDATE, then insert"	Gap locks, and deadlocks	Insert, and handle the duplicate
Checking with a plain SELECT instead	Both find nothing, and both insert	Keep a unique index
No unique index on the column	Duplicates go in	Add one

Troubleshooting

Symptom	Likely cause	Fix
ERROR 1213 on an INSERT	Two gap locks	This post
An INSERT waits, and then times out	Another session holds a gap lock	How Gap Locks Block an INSERT
Row-exists checks are all over the code	An old habit	Replace them with the insert-first pattern

Conclusion

SELECT ... FOR UPDATE on a missing row locks a gap, and two sessions can hold the same gap, so both inserts wait for each other. Insert directly, keep a unique index, and treat the duplicate error as the answer.

Additional Resources

• InnoDB Locking — gap locks.
• Locks Set by Different SQL Statements in InnoDB — what INSERT locks.

Related

• How Gap Locks Block an INSERT in MySQL
• Fixed: "ERROR 1062 (23000): Duplicate entry 'adapters' for key 'categories.IX_categories_Name'" in MySQL
• Fixed: "ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction" in MySQL