A MySQL deadlock happens when two or more transactions try to lock the same rows in opposite order and end up waiting on each other forever. InnoDB detects this dead end, picks one side as the "victim" and rolls it back; your application then sees the error Deadlock found when trying to get lock; try restarting transaction. On a game server, an e-commerce backend, or any system with heavy write traffic this error feels inevitable, but the vast majority of deadlocks can be prevented with better transaction design.
What exactly is a deadlock?
The classic scenario goes like this: transaction A first locks row 1, then asks for row 2. At the same moment transaction B has already locked row 2 and now asks for row 1. Each one is waiting for a lock the other holds, so nobody can move forward. This is a circular wait.
An important distinction: a deadlock is not the same as a lock wait timeout. In a lock wait timeout a single transaction waits for a lock to be released and gets an error once innodb_lock_wait_timeout (default 50 seconds) expires. In a deadlock the wait is circular, so InnoDB does not wait at all — it intervenes instantly and rolls one side back.
Why do InnoDB locks appear?
InnoDB uses row-level locking, but locks are not always "exactly one row." A few key behaviours produce deadlocks:
- Unindexed UPDATE/DELETE: If a
WHEREclause can't use a suitable index, InnoDB locks every row it scans. It looks like you're updating one row, but you may have locked thousands. - Gap and next-key locks: Under the
REPEATABLE READisolation level (MySQL's default) InnoDB locks not only rows but also the "gaps" between them. This prevents phantom reads but creates unexpected conflicts. - Foreign key and unique index checks: When inserting or updating a row, InnoDB also locks the related parent/child key rows.
- Inconsistent access order: Transactions that update the same tables in different orders across different code paths are the single most common cause of deadlocks.
Reading the deadlock log
Before guessing, read what InnoDB is telling you. You get the details of the most recent deadlock with:
SHOW ENGINE INNODB STATUS\GFind the LATEST DETECTED DEADLOCK section in the output. It shows the two transactions, the locks they hold (HOLDS THE LOCK(S)), the locks they are waiting for (WAITING FOR THIS LOCK) and which query was running. Seeing which table and which index got locked is half of the solution.
To log every deadlock permanently, add this to your MySQL configuration:
[mysqld]
innodb_print_all_deadlocks = ONNow every deadlock lands in the MySQL error log, so you can investigate even rare conflicts after the fact.
Fix 1: Pin a consistent transaction order
Most deadlocks come from inconsistent lock ordering. The fix is simple but requires discipline: make every transaction touch resources in the same order, every time. For example, when transferring a balance between two accounts, always lock rows from the smaller id to the larger:
START TRANSACTION;
-- The smaller id is always locked first
UPDATE accounts SET balance = balance - 100
WHERE id = LEAST(@from, @to);
UPDATE accounts SET balance = balance + 100
WHERE id = GREATEST(@from, @to);
COMMIT;Even if two transactions touch the same two rows, no circular wait can form because both now proceed in the same order. This rule requires you to apply the same access order everywhere in your code.
Fix 2: Keep transactions small and short
The longer a transaction stays open, the longer it holds its locks and the higher the chance of conflict. Practical rules:
- Don't make HTTP requests, write files or call external APIs inside a transaction. Move slow work outside it.
- Only put writes that truly must be atomic into a single transaction.
- Don't use
SELECT ... FOR UPDATEunnecessarily; lock only the row you will actually update. - Split bulk updates into small batches instead of one giant query.
Fix 3: Add the right indexes
An unindexed WHERE clause forces InnoDB to lock far more rows than needed. Check which index your query uses with EXPLAIN:
EXPLAIN UPDATE orders SET status = 'shipped'
WHERE customer_id = 42 AND status = 'paid';If the type column shows ALL, a full table scan is happening. Adding a suitable composite index on customer_id and status reduces the number of locked rows to a minimum and sharply lowers the deadlock risk.
Fix 4: Retry on the application side
Driving deadlocks to zero is often unrealistic; rare conflicts can always happen. So your application should catch the deadlock error and retry the transaction. In Laravel you can do this out of the box:
use Illuminate\Support\Facades\DB;
// Third argument: retry up to 3 times on deadlock
DB::transaction(function () {
DB::table('accounts')->where('id', 1)->decrement('balance', 100);
DB::table('accounts')->where('id', 2)->increment('balance', 100);
}, 3);Key point: retrying only makes sense for the side that was chosen as the victim, and it works when the transaction is short. Retry is not there to hide bad design — it's there to smooth over the rare, unavoidable conflicts.
Frequently Asked Questions
Does a deadlock cause data loss?
No. InnoDB fully rolls back the victim transaction, so no half-finished change remains. The real danger is your application swallowing the error and never retrying, so the user's change silently disappears. That's exactly why retry logic is essential.
Does raising innodb_lock_wait_timeout fix deadlocks?
No. That setting applies to lock wait timeouts, not deadlocks. InnoDB already detects and resolves deadlocks instantly; raising the timeout only affects timeout scenarios and won't prevent real deadlocks.
Will using a table lock end deadlocks?
Technically one coarse lock can reduce conflicts, but it destroys concurrency and slows your server dramatically. The right approach is row locks taken in a consistent order with short transactions; a table lock is almost always the wrong fix.
Stuck with recurring deadlocks on your server? We can review your InnoDB logs together and fix your transaction order and indexes. Get in touch and let's make your database stable.