You hit MySQL error 1213 — Deadlock found when trying to get lock; try restarting transaction — and you're probably staring at a query that looks harmless. Annoying, right? The fix isn't in the query alone. It's in how you structure your transactions and indexes.
The Fast Fix: Shorten Your Transactions
Deadlocks happen when two transactions each hold a lock the other needs. The longer a transaction stays open, the more locks it piles up, and the higher the chance of a collision. So step one: wrap only the absolutely necessary statements in a transaction. Don't do slow SELECTs, API calls, or user input handling inside the transaction.
-- BAD: Long transaction with external calls
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- (network call to payment gateway, 2 seconds)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
-- GOOD: Minimal transaction
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- (single statement auto-commits, no deadlock window)If you really need multiple statements, keep them quick and commit as soon as you're done.
Why This Works
What's actually happening is InnoDB uses row-level locks. A deadlock requires a circular wait: Transaction A holds a lock on row 1 and wants row 2; Transaction B holds row 2 and wants row 1. InnoDB detects this instantly and rolls back one transaction (the one that did the least work). By shortening your transaction, you shrink the number of rows you lock and the time you hold them, so the chance of two transactions ever forming a cycle drops to near zero.
The Real-World Trigger
I saw this most often in a payment system processing concurrent webhooks. Two webhooks for the same user would fire almost simultaneously, each doing a SELECT then an UPDATE on the same account row. The SELECT didn't lock, but the UPDATE did — and each transaction also touched a shared transaction log row, creating the cycle. Reducing it to a single UPDATE killed it.
The Second Fix: Lock Rows in a Consistent Order
If you must update multiple rows, always lock them in the same order across all your code. If one transaction updates users 1 then 2, another must not update 2 then 1. Otherwise you get a deadlock even with short transactions.
-- Always ORDER BY the primary key
UPDATE accounts SET balance = balance - 50 WHERE id IN (2, 1) ORDER BY id;
UPDATE accounts SET balance = balance - 50 WHERE id IN (1, 2) ORDER BY id;The ORDER BY id forces InnoDB to lock row 1 before row 2 in both cases. No cycle possible.
Why Index Order Matters
InnoDB doesn't lock rows in the order they appear in your WHERE clause. It locks them in the order it scans the index. Without an ORDER BY, the planner might choose a different index each time, leading to different lock orders. Consistent ordering removes that variable.
Less Common Variations
1. Deadlock on INSERT with Auto-Increment
If you insert into a table with a foreign key that references a parent row, and two transactions insert children referencing the same parent, they both take a shared lock on the parent row. Then each tries to upgrade to an exclusive lock — classic deadlock. Fix: lock the parent row explicitly with SELECT ... FOR UPDATE before inserting children.
2. Deadlock on Range Scans
Queries like UPDATE ... WHERE date BETWEEN ? AND ? can lock gaps (next-key locks) that don't exist yet. Two such queries on overlapping ranges can deadlock. Solve by ensuring your transaction isolation is READ COMMITTED, which disables gap locks (unless you're using statement-based replication — then think twice). Or narrow your range.
3. Deadlock from Missing Index
When a foreign key column isn't indexed, an UPDATE on the parent table may lock the entire child table, causing major contention. Add an index on the foreign key column. This isn't a deadlock fix by itself, but removing table-level lock pressure reduces deadlock frequency.
What to Do When You Still Hit One
Even with perfect code, deadlocks happen under heavy load. The correct response is to retry the entire transaction. Don't catch the error and pretend it didn't happen — you'll lose data.
-- Pseudo-code for retry logic
max_retries = 3
for attempt in 1..max_retries:
try:
BEGIN
-- your queries
COMMIT
break
except deadlock_error:
ROLLBACK
sleep(random(0.1, 0.5)) # jitter avoids thundering herdThe random sleep is critical. If all retries fire at the same time, they'll just deadlock again.
Diagnose With SHOW ENGINE INNODB STATUS
When a deadlock occurs, run this immediately (it's ephemeral):
SHOW ENGINE INNODB STATUS;Look at the LATEST DETECTED DEADLOCK section. It shows the two transactions, which locks they held, and which one got rolled back. That output tells you the exact queries involved. Most of the time, you'll see the same two queries or the same table. Use that to spot the ordering issue.
Prevention: Design for Short Locks
The real prevention is architectural. Break large transactions into smaller ones. If you're batch updating a million rows, do it in chunks of 500 with a commit between each. That way, no transaction holds thousands of locks at once.
Also, keep your transaction isolation at REPEATABLE READ (the default) only if you need it. Switching to READ COMMITTED eliminates gap locks and cuts deadlock rate dramatically. Just check if your app depends on the default behavior — for most web apps, it doesn't.
And set a sane innodb_lock_wait_timeout (default 50 seconds). If a query waits that long, something's wrong. Lower it to 5 seconds so you fail fast and retry, rather than hanging.
Deadlocks aren't a bug in MySQL. They're a signal that your locking strategy is too greedy. Shorten, order, retry — that's the whole game.