Fixing MySQL "Deadlock found" Errors: A Guide to Analyzing INNODB STATUS Logs

Diagnosing and Fixing MySQL "Deadlock found" Errors: A Guide to Analyzing INNODB STATUS Logs

You probably landed here after spotting Deadlock found when trying to get lock; try restarting transaction in your ops logs. Maybe an alert fired at 2 a.m., or payment and order transactions are failing intermittently. Rest easy: a deadlock does not corrupt data. It is InnoDB detecting a deadlock and deliberately rolling back one side — a normal safety mechanism.

This post does not linger on isolation-level theory. It follows error log → pinpoint the cause → fix → retry code, covering only what you can apply right now.

Why Deadlocks Happen: Circular Wait Explained

The essence of a deadlock is simple. Two transactions wait on each other's locks and stall.

Session A: holds lock on row 1 → waiting for lock on row 2
Session B: holds lock on row 2 → waiting for lock on row 1

   ┌──────────┐         ┌──────────┐
   │ Session A │  HOLDS  │  row 1   │
   │          │────────▶│          │
   │          │◀────────│          │ WAITS
   └──────────┘         └──────────┘
        │ WAITS              ▲ HOLDS
        ▼                    │
   ┌──────────┐         ┌──────────┐
   │  row 2   │◀────────│ Session B │
   └──────────┘  HOLDS  └──────────┘

There are three InnoDB lock types you should know:

• Record Lock: a lock on a specific index record
• Gap Lock: a lock on the "empty space" between records (prevents phantoms)
• Next-Key Lock: record lock + gap lock (default behavior under REPEATABLE READ)

The key point is this: without an index, locks are taken widely, and wide locks explode the chance of collisions. And if transactions lock rows in different orders, you get the circular wait shown in the diagram above.

Catching the Culprit: How to Read SHOW ENGINE INNODB STATUS

When a deadlock fires, this is the first command to run.

SHOW ENGINE INNODB STATUS\G

In the output, find the LATEST DETECTED DEADLOCK section. Real output looks like this.

------------------------
LATEST DETECTED DEADLOCK
------------------------
2026-06-20 02:13:44 0x7f8a
*** (1) TRANSACTION:
TRANSACTION 84211, ACTIVE 6 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
UPDATE orders SET status='PAID' WHERE id=2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 58 page no 4 n bits 80 index PRIMARY of table `shop`.`orders`
trx id 84211 lock_mode X locks rec but not gap waiting

*** (2) TRANSACTION:
TRANSACTION 84210, ACTIVE 9 sec starting index read
UPDATE orders SET status='SHIP' WHERE id=1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 58 page no 4 n bits 80 index PRIMARY of table `shop`.`orders`
trx id 84210 lock_mode X locks rec but not gap

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 58 page no 4 n bits 80 index PRIMARY of table `shop`.`orders`
trx id 84210 lock_mode X locks rec but not gap waiting

*** WE ROLL BACK TRANSACTION (1)

Let's dissect it line by line.

The key reading: if (1) is waiting for a lock that (2) holds, and (2) is waiting for a lock that (1) holds, circular wait is established. And because it says index PRIMARY, you know this is a PK-based row-lock collision. If you see traces of a full scan instead of an index name, suspect scenario 4-1.

On MySQL 8.0 you can inspect live lock state more precisely.

-- 현재 어떤 트랜잭션이 무슨 락을 들고/기다리는지
SELECT * FROM performance_schema.data_locks;

-- 누가 누구를 기다리는지 (대기 관계)
SELECT * FROM performance_schema.data_lock_waits;

Reproduce It Yourself: Creating a Deadlock with Two Sessions

The fastest way to internalize the principle is to do it by hand. Open two terminals and follow along.

-- 사전 준비
CREATE TABLE orders (
  id INT PRIMARY KEY,
  status VARCHAR(20)
);
INSERT INTO orders VALUES (1,'NEW'), (2,'NEW');

-- 세션 A
BEGIN;
UPDATE orders SET status='SHIP' WHERE id=1;   -- id=1 락 획득

-- 세션 B
BEGIN;
UPDATE orders SET status='SHIP' WHERE id=2;   -- id=2 락 획득

-- 세션 A (id=2를 추가로 잠그려 시도 → 대기)
UPDATE orders SET status='PAID' WHERE id=2;

-- 세션 B (id=1을 추가로 잠그려 시도 → 순환 발생!)
UPDATE orders SET status='PAID' WHERE id=1;
-- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Session A requested locks in 1→2 order; Session B requested 2→1. Crossed lock order is the direct cause of the deadlock.

Real Causes and Fixes by Scenario

4-1. Missing index expands the lock range to the whole table

This is the most common case and the easiest to miss. If the WHERE column has no index, InnoDB locks every row it scans.

-- email에 인덱스가 없는 상태
EXPLAIN UPDATE users SET grade='VIP' WHERE email='a@x.com';
-- type: ALL  → 풀 테이블 스캔. 모든 행에 락이 걸린다!

The fix is simple: add an index on the filter column.

ALTER TABLE users ADD INDEX idx_email (email);

EXPLAIN UPDATE users SET grade='VIP' WHERE email='a@x.com';
-- type: ref  → 해당 행만 락. 충돌 확률 급감

I once saw deadlocks explode in a high-traffic settlement batch; the cause was a missing index on the WHERE settled_at = ? column. Adding one index made the deadlocks almost disappear. When you see a deadlock, make EXPLAIN your first habit.

4-2. Inconsistent UPDATE order inside transactions

The reproduction above is exactly this case. The fix is to unify lock-acquisition order across all code.

-- 나쁜 예: 코드마다 제각각
-- A 서비스: id 1 → 2
-- B 서비스: id 2 → 1

-- 좋은 예: 항상 PK 오름차순으로 처리
UPDATE orders SET ... WHERE id IN (1,2) ORDER BY id;

When updating multiple rows, sort the IDs at the application level and process them in order. A single rule like "always smallest key first" cuts off circular wait at the source.

4-3. Parent–child locks from foreign-key constraints

On INSERT/UPDATE of a child table, InnoDB takes a shared lock (S) on the parent row. If that crosses a transaction that is modifying the parent, you get a deadlock.

-- 부모를 먼저 잠그는 트랜잭션과 자식 INSERT가 충돌
-- 해결: 비즈니스 로직에서 "부모 → 자식" 접근 순서 일관화
-- 또는 외래키 인덱스 점검 (자식 FK 컬럼 인덱스 필수)
SELECT * FROM information_schema.STATISTICS
WHERE TABLE_NAME='order_items' AND COLUMN_NAME='order_id';

Never Get Woken Up at 2 a.m. Again: Retry Code and Prevention

Drop-in retry logic (Python)

You cannot eliminate deadlocks completely. The standard approach is to catch error code 1213 and retry with exponential backoff.

import time
import random
from sqlalchemy.exc import OperationalError

def run_with_retry(session, work, max_retries=3):
    for attempt in range(max_retries):
        try:
            result = work(session)
            session.commit()
            return result
        except OperationalError as e:
            session.rollback()
            # MySQL 데드락 에러코드 1213
            if e.orig.args[0] == 1213 and attempt < max_retries - 1:
                backoff = (2 ** attempt) * 0.1 + random.uniform(0, 0.05)
                time.sleep(backoff)  # 지수 백오프 + 지터
                continue
            raise

Spring (Java)

@Retryable(
    retryFor = DeadlockLoserDataAccessException.class,
    maxAttempts = 3,
    backoff = @Backoff(delay = 100, multiplier = 2))
@Transactional
public void processOrder(Long orderId) {
    // 데드락 발생 시 자동 재시도
}

Pseudocode in a nutshell: for retry count { try → on 1213 rollback and wait → retry; on last failure throw }

Related variables and production recommendations

Turning on innodb_print_all_deadlocks=ON leaves every deadlock in the error log, overcoming SHOW STATUS's "last one only" limitation.

Cloud managed DB monitoring

On AWS Aurora/RDS MySQL 8.0, track lock waits visually in Performance Insights and query the performance_schema.data_lock_waits table for live wait relationships. In high-concurrency microservice environments, deadlocks are a "constant," not a "bug," so baking retry logic into your baseline infrastructure is the safe move.

Prevention checklist

• Unify lock-acquisition order in all code (e.g., PK ascending)
• Guarantee indexes on WHERE/JOIN columns and FK columns (verify with EXPLAIN)
• Minimize transaction scope — do not put external API calls or waits inside a transaction
• Don't overuse SELECT ... FOR UPDATE; lock only the rows you need
• Apply retry logic for 1213 errors
• innodb_print_all_deadlocks=ON + run monitoring queries periodically

FAQ

Q. Does a deadlock corrupt data? A. No. When InnoDB detects a deadlock it automatically rolls back only one transaction, so data integrity is preserved. Just retry the rolled-back transaction.

Q. Won't retrying cause an infinite loop? A. Cap retries (usually 3) and use exponential backoff. If it still keeps failing, inspect the root cause — lock order or indexes — with SHOW ENGINE INNODB STATUS.

Q. I only see the last deadlock, so analysis is hard. A. Set innodb_print_all_deadlocks=ON and every deadlock is written to the MySQL error log, which enables pattern analysis. On RDS/Aurora, set it in the parameter group.