MySQL Transaction Deadlocks Explained

As business features and modules pile up in a MySQL-backed system, the number of database transactions grows with them. In day-to-day feature work we frequently open transactions to operate on data — or open distributed global transactions — and once there are enough transactions running concurrently, there's a real chance of transaction deadlocks.

Why This Topic

What makes deadlocks nasty is that they aren't stably reproducible bugs: run the test environment single-threaded a hundred times and nothing happens, then a concurrency spike in production makes them pop up, leaving nothing in the logs but Deadlock found when trying to get lock. Without understanding the cause, it's easy to dismiss them as flukes — until one day they erupt en masse right at business peak. This post walks through deadlocks end to end: how they arise, how they're detected, and how to avoid them.

First, some background. InnoDB row locks are taken on index records. When a transaction updates a row, it acquires an exclusive lock (X lock) on that row and holds it until the transaction commits or rolls back — that's what the two-phase locking protocol requires. Lock hold time equals transaction lifetime, and this is the foundation for everything that follows: the longer the transaction, the bigger the window in which someone else collides with your locks.

How a Deadlock Forms

Let's break down how a transaction deadlock comes about.

Two sessions are connected: one session holds transaction T1, the other holds transaction T2.

1. T1 modifies rows1

2. T2 modifies rows2

3. T2 modifies rows1

4. T2 waits for T1 to release the X lock on rows1

5. T1 modifies rows2

6. T1 waits for T2 to release the X lock on rows2

7. Mutual waiting — deadlock

The crux is in steps 3 and 5: the two transactions touch each other's already-locked rows in opposite orders. T1 clutches rows1 while waiting for rows2; T2 clutches rows2 while waiting for rows1. Neither will let go first — because letting go means rolling back. The wait relationships form a cycle, and that is the essence of deadlock: circular waiting on resources.

Without outside intervention, the program cannot resolve this on its own. It takes human action or a separate watchdog thread to break the deadlock.

How MySQL Picks the Victim

The way out of a deadlock is simple: release one of the transactions by rolling it back.

But rollback raises a question — which transaction is the more reasonable one to roll back? MySQL decides by undo log: whichever transaction has more undo log entries carries more weight, and the lower-weight transaction is the one sacrificed and rolled back. This resolves the deadlock while keeping the amount of rolled-back data — and thus the performance cost — as small as possible.

The logic is easy to follow: undo log records the modifications a transaction has already made, so more entries means the transaction has done more work, and rolling it back costs more. InnoDB therefore prefers to roll back the transaction that has "done less," minimizing the cost of redoing work. The chosen transaction receives a deadlock error, and the application layer, upon catching it, can retry the whole transaction — which is why transactions should be designed to be retryable in the first place.

Estimating Deadlock Probability

The probability of any given transaction in the system hitting a deadlock is roughly n2r4/4R2.

n: the number of transactions — the more transactions there are, the higher the deadlock probability (transactions nested within transactions).

r: the number of operations per transaction — the more rows each transaction touches, the higher the deadlock probability.

R: the set of data being operated on — the smaller it is, the higher the deadlock probability (different transactions hitting the same slice of data; the more scattered the modified data and the larger the data set, the smaller the chance of X locks landing on the same row).

The formula points straight at the optimization levers: r has an exponent of 4, so its impact is the most violent — splitting big transactions into small ones and reducing the rows each transaction touches is the highest-yield way to cut deadlock probability. And R sits in the denominator, meaning hot data is a breeding ground for deadlocks: when every transaction crowds in to update the same handful of rows (a counter row, an inventory row), deadlock probability gets amplified drastically.

Active Detection: the Wait-for Graph

MySQL's active transaction detection: the wait-for graph.

Before each session's transaction proceeds, an algorithm can determine ahead of time whether a transaction cycle exists (two transactions each blocking on data the other holds X locks on, forming a loop), and preemptively release the transaction with the smaller undo log weight.

The wait-for graph models the waiting relationships as a directed graph: each transaction is a node, and "T2 is waiting on T1's lock" draws an edge from T2 to T1. Whenever a transaction blocks because it can't acquire a lock, InnoDB adds the edge to the graph and checks for a cycle — a cycle means deadlock, and the lower-weight transaction is picked out and rolled back immediately, with no idle waiting.

Beyond active detection, InnoDB has a safety net: lock wait timeout. Once a row-lock wait exceeds the time set by innodb_lock_wait_timeout, the waiting statement errors out. It doesn't distinguish a true deadlock from plain lock contention — it just guarantees that a transaction won't hang forever.

-- View details of the most recent deadlock (the LATEST DETECTED DEADLOCK section)
-- Shows the locks each transaction held, the locks it waited on, and which side was rolled back
SHOW ENGINE INNODB STATUS;

-- View the lock wait timeout (in seconds)
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

That said, in complex business scenarios it's not uncommon for a deadlock to already have happened by the time MySQL's wait-for graph detection would have kicked in.

So we need to sort out our business flows and the range of data each one affects, and shrink the odds of deadlock — or risk ending up resolving deadlocks by hand.

Pitfalls and Notes

Building on the mechanics above, a few lessons are worth keeping in mind for real-world development:

1. Standardize lock ordering. Look back at how the deadlock formed: the root cause was two transactions accessing the same rows in opposite orders. If all business code agrees to update data in a single consistent order (say, ascending primary key), the circular-wait loop can never close.

2. Keep transactions short. Don't make RPC calls, publish messages, or wait for user input inside a transaction. Stretching the lock hold time is holding the door open for deadlocks.

3. Watch out for gap locks. Under repeatable-read isolation, range-based updates and deletes take gap locks. Two transactions locking adjacent gaps and then inserting into each other's gaps will also deadlock — and this kind is often invisible if you only look at row data; you need the lock information in SHOW ENGINE INNODB STATUS.

4. Retry at the application layer. Once a deadlock is detected, one side always gets rolled back. Business code must catch the deadlock error and retry the entire transaction — not just the last statement.

warning

Deadlock detection itself has a cost: when large numbers of transactions queue on a hot row, every new waiter has to traverse the wait graph, and the detection overhead grows with concurrency. For hot-row update scenarios, split the hotspot at the business level rather than expecting the database to muscle through.

Wrapping Up

The essence of a deadlock is circular waiting created by interleaved lock ordering. InnoDB uses the wait-for graph to actively spot cycles, then rolls back the cheaper transaction by undo log weight as the fallback. But detection and rollback are only remedies — the real work happens beforehand: split transactions small, unify lock ordering, and spread out hot data. Only then does deadlock probability drop to a level you can ignore.