MySQL "Deadlock Found When Trying to Get Lock; Try Restarting Transaction" on Concurrent INSERTS

Deadlocks between concurrent INSERT statements specifically (rather than mixed read/write or UPDATE-heavy workloads) have their own distinct cause — InnoDB's gap locking behavior around unique indexes and auto-increment handling, which is a different mechanism than the row-locking conflicts that cause most other deadlock scenarios.

The Problem

Multiple concurrent transactions each performing INSERT statements into the same table fail intermittently:

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

This is confusing specifically because INSERT operations, intuitively, seem like they shouldn't conflict with each other the way concurrent updates to the same row obviously would — each insert is adding a genuinely new, distinct row.

Why It Happens

InnoDB's default isolation level, REPEATABLE READ, uses gap locking (locking the space between index records, not just the records themselves) specifically to prevent phantom reads — but this same mechanism means concurrent inserts near each other in an index's ordering can conflict in ways that aren't immediately obvious from application code alone. Specific contributing patterns:

• Concurrent inserts into a table with a unique index (beyond just the primary key) can acquire gap locks around where the new value would be inserted in that index's ordering, and two transactions inserting different but nearby values can end up waiting on each other's gap locks, forming a genuine deadlock
• Auto-increment columns, particularly under certain innodb_autoinc_lock_mode settings, involve their own specific locking behavior around allocating the next value, which can interact with other locking in ways that occasionally produce deadlocks under high concurrent insert load
• Foreign key constraints requiring InnoDB to also lock the referenced parent table's row during an insert into a child table, adding an additional locking dimension beyond the child table's own insert operation, which can also participate in a deadlock cycle
• A higher-than-necessary isolation level for the specific workload, where a less strict level would avoid some of this gap-locking behavior without genuinely compromising the actual data integrity requirements of the specific application

The Fix

First, capture the actual deadlock detail from MySQL's own diagnostic output, which shows exactly which locks and queries were involved on both sides of the conflict:

SHOW ENGINE INNODB STATUS\G

Review the "LATEST DETECTED DEADLOCK" section specifically, which names the exact transactions, queries, and lock types involved — this level of detail is essential for understanding whether your specific deadlock is genuinely the gap-lock-on-unique-index pattern described here, or something else entirely.

Implement application-level retry logic for this specific error, since InnoDB deadlocks between concurrent inserts are a documented, expected possibility under sufficient concurrency, not necessarily a sign of a fundamental design flaw requiring elimination — a well-designed application should gracefully retry rather than treating every deadlock as a fatal, unretriable error:

import time
import random

for attempt in range(3):
    try:
        execute_insert()
        break
    except DeadlockError:
        time.sleep(random.uniform(0.05, 0.2))

If deadlocks are frequent enough to be a genuine performance concern (not just an occasional, gracefully-retried event), consider whether a lower isolation level is appropriate for your specific workload's actual consistency requirements:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

READ COMMITTED uses less aggressive gap locking than the default REPEATABLE READ, which can significantly reduce this specific class of insert-vs-insert deadlock — but this is a genuine tradeoff affecting the guarantees your transactions provide around repeatable reads and phantom read prevention, and should be a deliberate choice based on your application's actual consistency needs, not applied blindly just to make deadlock errors go away.

If unique index gap locking is specifically implicated, consider whether inserting rows in a more consistent, sorted order across concurrent transactions (similar to the general batch-update deadlock advice) reduces the collision frequency, though this is often less practical to enforce for genuinely independent, concurrent insert operations compared to controlled batch update scenarios.

Still Not Working?

If deadlocks specifically correlate with periods of very high auto-increment-driven insert concurrency, review your innodb_autoinc_lock_mode setting, since different modes offer different tradeoffs between insert concurrency and strict auto-increment value ordering guarantees:

SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';

Mode 2 (interleaved), the default in modern MySQL versions, generally offers the best concurrency for simple inserts, but understanding your current setting and its specific tradeoffs (particularly if this was explicitly changed at some point in your environment's configuration history) helps clarify whether the current setting is actually appropriate for your specific concurrent insert workload, or whether it's inadvertently contributing to the deadlock pattern you're observing.