Symptoms
Deadlocks don't announce themselves with a bang — they show up as intermittent failures that are maddeningly hard to reproduce on demand. The most common symptom is an application error bubbling up from your MySQL driver:
SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transactionIn the MySQL error log at /var/log/mysql/error.log, you'll see warnings clustering around peak traffic windows:
2026-04-16T09:14:32.443521Z 47 [Warning] InnoDB: Transactions deadlock detected, dumping detailed information.On the application side you'll notice retry loops thrashing, latency spikes on write-heavy endpoints, and — if you're running a monitoring stack — a clear uptick in transaction rollback counters. Users see intermittent save failures with no obvious pattern. Database dashboards show lock wait timeouts climbing alongside the deadlock count. If you have SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks' wired into your metrics, that counter will be moving upward steadily.
One critical thing to understand before diving in: InnoDB automatically detects deadlocks and resolves them by rolling back the transaction it estimates is cheapest to undo — usually the one with fewer row changes. The database doesn't crash. But your application has to handle the retry, and if it doesn't, those write operations get silently dropped.
Root Cause 1: Circular Lock Dependency
This is the textbook deadlock. Two transactions each hold a lock the other one needs, and neither can proceed. Transaction A holds a lock on row 10 and is waiting for row 20. Transaction B holds a lock on row 20 and is waiting for row 10. MySQL detects the cycle and kills one. Straightforward in theory — devastating in production at scale.
In my experience, this almost always happens when application code doesn't enforce a consistent lock acquisition order. Two code paths update the same set of rows but in different sequences. It's especially common in e-commerce systems with order and inventory tables, or financial systems where simultaneous transfers between two accounts trigger opposite lock orders.
To identify it, run this immediately after a deadlock occurs — the data only persists until the next deadlock overwrites it:
SHOW ENGINE INNODB STATUS\GLook for the LATEST DETECTED DEADLOCK section. Here's what a real circular dependency looks like in that output:
------------------------
LATEST DETECTED DEADLOCK
------------------------
2026-04-16 09:14:32 0x7f3e2c0b2700
*** (1) TRANSACTION:
TRANSACTION 5891023, ACTIVE 2 sec starting index read
MySQL thread id 47, OS thread handle 139901034455808, query id 184921 192.168.10.22 infrarunbook-admin updating
UPDATE orders SET status = 'processing' WHERE order_id = 1001
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 291 page no 4 n bits 72 index PRIMARY of table `shop`.`inventory`
trx id 5891023 lock_mode X locks rec but not gap
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 290 page no 3 n bits 72 index PRIMARY of table `shop`.`orders`
trx id 5891023 lock_mode X locks rec but not gap
*** (2) TRANSACTION:
TRANSACTION 5891024, ACTIVE 1 sec starting index read
MySQL thread id 48, OS thread handle 139901034456064, query id 184922 192.168.10.22 infrarunbook-admin updating
UPDATE inventory SET qty = qty - 1 WHERE product_id = 501
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 290 page no 3 n bits 72 index PRIMARY of table `shop`.`orders`
trx id 5891024 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 291 page no 4 n bits 72 index PRIMARY of table `shop`.`inventory`
trx id 5891024 lock_mode X locks rec but not gap
*** WE ROLL BACK TRANSACTION (2)Transaction 1 holds a lock on inventory and wants orders. Transaction 2 holds a lock on orders and wants inventory. Classic cycle. MySQL picked transaction 2 as the victim and rolled it back.
The fix is straightforward in principle: enforce a global lock ordering across every code path that touches these tables. If every transaction always updates orders before inventory — no exceptions — the circular dependency can't form. Make it a documented convention and a code review checklist item. For complex systems with many contended resources, consider a queue-based pattern where a single worker serializes access rather than relying on lock ordering discipline across dozens of developers.
Root Cause 2: Transaction Not Committing Fast Enough
Long-running transactions are deadlock magnets. The longer a transaction holds its locks, the wider the window for another transaction to grab a conflicting lock and create a cycle. I've seen this most often when developers do expensive work inside a transaction — external API calls, file I/O, complex in-memory computation — while keeping the database connection open the whole time.
To find long-running transactions, query the InnoDB transaction table directly:
SELECT
trx_id,
trx_state,
trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS seconds_running,
trx_rows_locked,
trx_rows_modified,
trx_query
FROM information_schema.innodb_trx
WHERE trx_started < NOW() - INTERVAL 10 SECOND
ORDER BY trx_started ASC;A healthy OLTP system should rarely show transactions running longer than a second or two. If you're seeing 30, 60, or 120 seconds in the seconds_running column, that's your smoking gun. Here's what a misbehaving system looks like:
+----------+-----------+---------------------+-----------------+-----------------+------------------+--------------------------------+
| trx_id | trx_state | trx_started | seconds_running | trx_rows_locked | trx_rows_modified| trx_query |
+----------+-----------+---------------------+-----------------+-----------------+------------------+--------------------------------+
| 5891100 | RUNNING | 2026-04-16 09:08:12 | 87 | 143 | 12 | UPDATE payments SET status=... |
| 5891115 | LOCK WAIT | 2026-04-16 09:09:11 | 28 | 47 | 0 | UPDATE payments SET status=... |
+----------+-----------+---------------------+-----------------+-----------------+------------------+--------------------------------+Transaction 5891100 has been open for 87 seconds holding 143 locked rows. Anything that needs those rows is blocked, and if the blockers hold other locks, deadlock conditions stack up fast.
The fix is to restructure your transaction logic. Do all expensive non-database work before opening the transaction. Inside the transaction, do only the database operations, then commit immediately. For bulk operations, batch your writes into smaller chunks — update 500 rows, commit, repeat — rather than loading 50,000 rows into a single transaction. Also tighten your innodb_lock_wait_timeout; the default is 50 seconds, which is far too generous for OLTP. Set it to 5-10 seconds so stuck transactions fail fast rather than piling up and generating secondary deadlocks.
Root Cause 3: Missing Index Causing Lock on Full Row Set
This one bites people constantly and the mechanism is subtle. When InnoDB executes a query without a usable index, it has to scan the entire table to find matching rows. During that scan, it acquires next-key locks on every row it touches — even rows that don't match the WHERE clause. The lock footprint of the query explodes from a handful of rows to potentially the entire table.
Diagnose it by running EXPLAIN on the queries you pulled from INNODB STATUS:
EXPLAIN SELECT * FROM orders
WHERE customer_email = 'ops@solvethenetwork.com'
FOR UPDATE\G*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 482391
filtered: 0.10
Extra: Using where
1 row in set, 1 warning (0.00 sec)type: ALL and key: NULL are the red flags. This query scans all 482,391 rows and acquires a lock on every single one. Any other transaction that tries to modify any order — even a completely unrelated customer's — is going to block. Add the index and verify the access pattern changes:
ALTER TABLE orders ADD INDEX idx_customer_email (customer_email);
EXPLAIN SELECT * FROM orders
WHERE customer_email = 'ops@solvethenetwork.com'
FOR UPDATE\G*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: ref
possible_keys: idx_customer_email
key: idx_customer_email
key_len: 1022
ref: const
rows: 3
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)Three rows instead of 482,391. Lock footprint reduced by five orders of magnitude. This single fix has resolved full-blown deadlock storms on production systems I've managed. When hunting deadlocks, always cross-reference the queries from INNODB STATUS with EXPLAIN before you change anything else — it's the fastest path to a root cause.
Root Cause 4: Explicit Table Lock
Explicit table-level locking with LOCK TABLES is a pattern inherited from MyISAM days, and it causes serious damage in a concurrent InnoDB environment. When one session holds a table lock, every other session that needs any row in that table blocks — not just rows being actively modified. The blast radius is enormous compared to InnoDB's native row-level locking.
You'll usually catch this in SHOW PROCESSLIST, where multiple threads pile up in a waiting state:
SHOW FULL PROCESSLIST;+----+--------------------+---------------------+------+---------+------+------------------------------+--------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+--------------------+---------------------+------+---------+------+------------------------------+--------------------------------------------------+
| 47 | infrarunbook-admin | 192.168.10.22:51234 | shop | Query | 92 | Waiting for table lock | UPDATE orders SET status='shipped' WHERE id=2200 |
| 48 | infrarunbook-admin | 192.168.10.22:51235 | shop | Query | 91 | Waiting for table lock | INSERT INTO orders (customer_id, total) VALUES.. |
| 51 | infrarunbook-admin | 192.168.10.22:51238 | shop | Query | 103 | NULL | LOCK TABLES orders WRITE, inventory WRITE |
+----+--------------------+---------------------+------+---------+------+------------------------------+--------------------------------------------------+Thread 51 has held a WRITE lock on orders and inventory for 103 seconds. Threads 47 and 48 are completely frozen. You can also confirm through the Performance Schema:
SELECT
object_type,
object_schema,
object_name,
lock_type,
lock_status,
owner_thread_id
FROM performance_schema.metadata_locks
WHERE object_type = 'TABLE'
AND lock_status IN ('GRANTED', 'PENDING')
ORDER BY lock_status DESC;The fix depends on why LOCK TABLES was used. If it's legacy code left over from a MyISAM migration, remove it entirely — InnoDB manages row-level locking automatically and doesn't need manual table locking for consistency. If it's there for a backup or export, switch to mysqldump with --single-transaction, which uses a consistent read snapshot without blocking writes at all. For application code that needs exclusive access to a set of rows, replace LOCK TABLES with SELECT ... FOR UPDATE inside a proper InnoDB transaction. You get exclusive locking scoped to exactly the rows you need, nothing more.
Root Cause 5: Autocommit Disabled
This one is insidious because it's often invisible. MySQL's autocommit setting defaults to ON, meaning each statement is its own transaction and commits immediately after execution. When autocommit is disabled — either globally in the config or silently at the session level by application code or ORMs — every statement implicitly starts a transaction that stays open until you explicitly issue COMMIT or ROLLBACK.
Developers who disable autocommit and forget to commit leave transactions holding locks indefinitely. The connection might stay alive for hours. I've seen this create slow-burn deadlock storms that only surface late in the business day when enough of these ghost transactions have accumulated across a connection pool.
Check the global setting first:
SHOW VARIABLES LIKE 'autocommit';+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+Then look for sleeping connections with open transactions — the combination that screams leaked transaction:
SELECT
p.id AS process_id,
p.user,
p.host,
p.command,
p.time AS idle_seconds,
t.trx_id,
t.trx_state,
t.trx_rows_locked
FROM information_schema.processlist p
JOIN information_schema.innodb_trx t
ON p.id = t.trx_mysql_thread_id
WHERE p.command = 'Sleep'
AND t.trx_state = 'RUNNING'
ORDER BY p.time DESC;+------------+--------------------+---------------------+---------+--------------+----------+-----------+------------------+
| process_id | user | host | command | idle_seconds | trx_id | trx_state | trx_rows_locked |
+------------+--------------------+---------------------+---------+--------------+----------+-----------+------------------+
| 312 | infrarunbook-admin | 192.168.10.22:54201 | Sleep | 3847 | 5893201 | RUNNING | 27 |
| 318 | infrarunbook-admin | 192.168.10.22:54209 | Sleep | 1203 | 5893287 | RUNNING | 8 |
+------------+--------------------+---------------------+---------+--------------+----------+-----------+------------------+Process 312 has been sleeping for 3847 seconds — over an hour — with an open transaction holding 27 row locks. That's a ghost transaction keeping resources locked for no productive reason whatsoever.
Enable autocommit globally in your MySQL config and restart the service:
[mysqld]
autocommit = 1For sessions that genuinely need multi-statement transactions, use explicit BEGIN and COMMIT blocks with well-defined commit points. Never rely on implicit transaction semantics with autocommit off — it makes the code hard to reason about and creates exactly these kinds of lock leaks. Pair this with a sensible wait_timeout setting (300 seconds is a reasonable starting point) so idle connections with open transactions get reaped automatically rather than accumulating indefinitely.
Root Cause 6: Gap Locks Under REPEATABLE READ
InnoDB's default isolation level is REPEATABLE READ, and it uses gap locks to prevent phantom reads. A gap lock doesn't lock a specific row — it locks the gap between index values to prevent inserts into that range. Two transactions can each hold gap locks on overlapping ranges, and when either tries to insert a row into the other's locked gap, deadlock follows.
This surfaces most commonly with INSERT statements on tables with unique indexes, or with range queries using BETWEEN or open-ended WHERE clauses. In INNODB STATUS output you'll see it as lock_mode X locks gap before rec. If your workload doesn't actually require REPEATABLE READ semantics — and most standard OLTP workloads don't — switching to READ COMMITTED eliminates gap locking entirely:
-- Apply immediately to the current session
SET SESSION transaction_isolation = 'READ-COMMITTED';
-- Persist globally in my.cnf
[mysqld]
transaction_isolation = READ-COMMITTEDREAD COMMITTED uses only record locks, which are more granular and far less prone to gap-lock deadlocks. The trade-off is losing phantom read protection, which is an acceptable compromise for most applications. Test your application's behavior under this isolation level before rolling it globally — some ORM features assume REPEATABLE READ semantics.
Prevention
Deadlocks are never fully preventable — concurrent systems under load will occasionally collide. But you can get the frequency low enough that they become non-events your retry logic handles silently. Here's what actually works in production.
Enforce lock ordering at the application layer. Document which table or resource gets locked first, and make it a code review checklist item. If every transaction always touches table A before table B — no exceptions anywhere in the codebase — circular dependencies can't form. This is the single highest-leverage change you can make, and it costs nothing to implement beyond discipline.
Keep transactions short and tightly scoped. Move all non-database work outside transaction boundaries. Do the computation, make the API call, prepare the data — then open the transaction, execute the writes, commit, close. Set innodb_lock_wait_timeout to 5-10 seconds for OLTP workloads so stuck transactions fail fast instead of queuing up and generating cascading deadlocks. Alert on any transaction that exceeds 30 seconds in information_schema.innodb_trx.
Index every column that appears in WHERE clauses of UPDATE and DELETE statements. Run EXPLAIN on all write queries as part of your deployment checklist. A missing index on a high-update table is a deadlock waiting to happen at scale — you may not see it in staging, but you'll see it in production under real concurrency.
Keep innodb_deadlock_detect enabled. It's ON by default but verify it hasn't been disabled in pursuit of marginal throughput gains. Disabling it means deadlocked transactions sit and wait until innodb_lock_wait_timeout expires, which is much worse for latency and throughput than fast deadlock detection and rollback.
Build retry logic into your application. MySQL deadlocks return error code 1213, which is explicitly safe to retry — the rolled-back transaction left no partial state. Implement exponential backoff with jitter: retry after 50ms, then 100ms, then 200ms. Cap retries at 3-5 attempts and surface a proper error to the caller if they're all exhausted. Don't replay stale values — re-read any data your transaction needs on each retry attempt.
Watch the metrics continuously. Track Innodb_deadlocks from SHOW GLOBAL STATUS over time. A sustained baseline of near-zero is healthy. Any upward trend — even gradual — is a leading indicator that something in your access patterns has shifted: new queries were added, an index was inadvertently dropped, concurrency increased beyond a threshold. Catch it with monitoring before users catch it with errors.