beautifulsoups.dev
Published on

MySQL InnoDB 데드락(1213) 로그로 범인 찾기

Authors

운영에서 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction가 발생하면 보통 애플리케이션은 재시도로 숨기고 지나갑니다. 하지만 데드락이 반복되면 처리량이 떨어지고, 특정 요청이 계속 롤백되며, 최악의 경우 사용자 체감 장애로 이어집니다.

이 글은 “데드락이 났다”에서 끝내지 않고, InnoDB 데드락 로그로 실제 범인(쿼리/트랜잭션/인덱스/락 종류)을 찾아내는 절차를 실전 관점에서 정리합니다. 핵심은 데드락 로그의 구조를 이해하고, 로그에 나온 trx id, thread id, index, record lock 정보를 애플리케이션의 SQL/코드와 연결하는 것입니다.

데드락을 ‘증상’이 아니라 ‘사건’으로 보기

InnoDB 데드락은 “두 트랜잭션이 서로가 가진 락을 기다리며 교착 상태가 된 사건”입니다. InnoDB는 이를 감지하면 한 쪽 트랜잭션을 희생(victim)으로 롤백해 교착을 해소합니다.

따라서 우리가 찾아야 하는 범인은 보통 다음 중 하나입니다.

  • 서로 다른 순서로 같은 자원(행/갭/인덱스)을 잠그는 쿼리 조합
  • 인덱스 부재로 인한 과도한 범위 잠금(갭 락, 넥스트키 락)
  • SELECT ... FOR UPDATE 또는 UPDATE ... WHERE의 조건이 애매해 넓게 잠그는 패턴
  • 트랜잭션이 길어서 락 보유 시간이 늘어나는 패턴

1) 가장 먼저: 데드락 로그를 확보하는 3가지 방법

방법 A: SHOW ENGINE INNODB STATUS로 최근 데드락 확인

InnoDB는 “가장 최근 데드락 1건”을 상태 출력에 포함합니다.

SHOW ENGINE INNODB STATUS\G

출력에서 LATEST DETECTED DEADLOCK 섹션을 찾으면 됩니다. 단점은 최근 1건만 남는다는 점이라, 빈번한 데드락에서는 로그가 금방 덮입니다.

방법 B: MySQL 에러 로그에 데드락을 남기기

운영에서는 에러 로그에 데드락을 남기도록 설정하는 편이 좋습니다.

SET GLOBAL innodb_print_all_deadlocks = ON;
  • 장점: 모든 데드락이 에러 로그에 남음
  • 주의: 로그가 많아질 수 있으니 로깅/보관 정책 필요

방법 C: Performance Schema로 ‘누가 누구를 기다렸는지’ 보기

MySQL 8.x에서는 Performance Schema로 락/대기 관계를 더 구조적으로 볼 수 있습니다.

SELECT *
FROM performance_schema.data_lock_waits;

데드락 “직전”의 대기 관계를 파악하는 데 유용하지만, 이미 희생 트랜잭션이 롤백된 뒤에는 단서가 사라질 수 있어 A/B와 같이 쓰는 게 안전합니다.

2) 데드락 로그에서 반드시 읽어야 하는 필드

LATEST DETECTED DEADLOCK 섹션은 대체로 아래 순서로 나옵니다.

  • TRANSACTION (1) / TRANSACTION (2)
  • 각 트랜잭션의 상태(몇 초 실행, 몇 개 락 보유)
  • “기다리는 락” 정보
  • “보유 중인 락” 정보
  • 마지막에 WE ROLL BACK TRANSACTION (x)로 희생자 표시

아래는 형태를 보여주기 위한 축약 예시입니다(실제 출력은 더 깁니다).

------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 123456, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 111, OS thread handle 140..., query id 999 localhost appuser
UPDATE orders SET status='PAID' WHERE id=10
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 456 n bits 72 index PRIMARY of table `app`.`orders` trx id 123456 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: ...

*** (2) TRANSACTION:
TRANSACTION 123457, ACTIVE 2 sec
MySQL thread id 112, OS thread handle 141..., query id 1000 localhost appuser
UPDATE order_items SET qty=qty+1 WHERE order_id=10 AND sku='A'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 123 page no 456 n bits 72 index PRIMARY of table `app`.`orders` trx id 123457 lock_mode X locks rec but not gap

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 124 page no 777 n bits 80 index idx_order_sku of table `app`.`order_items` trx id 123457 lock_mode X locks rec but not gap

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 124 page no 777 n bits 80 index idx_order_sku of table `app`.`order_items` trx id 123456 lock_mode X locks rec but not gap

*** WE ROLL BACK TRANSACTION (1)

여기서 범인 찾기에 중요한 포인트는 다음입니다.

  • MySQL thread id: 같은 커넥션/세션을 가리키는 단서
  • query id: 서버 내부 식별자(상황에 따라 유용)
  • UPDATE ...: 데드락 당시 실행 중이던 SQL(가장 직접적인 단서)
  • index PRIMARY 또는 index idx_...: 어떤 인덱스에서 충돌났는지
  • lock_mode X / S: 배타/공유 락
  • locks rec but not gap / locks gap / locks rec but gap: 레코드 락/갭 락/넥스트키 락 여부

위 예시는 전형적인 패턴입니다.

  • 트랜잭션 (1)은 orders의 PK 레코드를 잡으려다 대기
  • 트랜잭션 (2)는 이미 orders PK를 잡고 있고, order_items 인덱스를 잡으려다 대기
  • 트랜잭션 (1)은 반대로 order_items를 잡고 있고 orders를 기다림
  • 즉, 락 획득 순서가 서로 반대라서 데드락

3) “범인”을 애플리케이션 코드까지 연결하는 실전 절차

3-1) 데드락 로그에서 트랜잭션 2개를 표로 정리

로그를 보면 머리가 복잡해집니다. 아래 4가지만 표로 빼면 정리됩니다.

  • 트랜잭션 번호: (1), (2)
  • 실행 SQL
  • 기다리는 락(테이블/인덱스/락 타입)
  • 보유 중인 락(테이블/인덱스)

이 표를 만들면 “서로 무엇을 들고 무엇을 기다리는지”가 한눈에 보입니다.

3-2) MySQL thread id로 현재/과거 세션 추적

데드락이 난 순간에 바로 대응할 수 있다면, 해당 스레드의 현재 상태를 볼 수 있습니다.

SHOW PROCESSLIST;

또는 더 자세히:

SELECT *
FROM performance_schema.threads
WHERE PROCESSLIST_ID IN (111, 112);

다만 데드락은 빠르게 해소되므로, 운영에서는 애플리케이션 로그에 커넥션 식별자/트랜잭션 식별자를 남기는 습관이 더 중요합니다.

3-3) “인덱스 이름”으로 원인 쿼리의 조건을 역추적

데드락 로그에 index PRIMARY 또는 index idx_order_sku처럼 인덱스가 명시됩니다.

  • PK에서 충돌이면 보통 같은 id를 동시에 업데이트
  • 보조 인덱스에서 충돌이면 WHERE 조건이 그 인덱스를 타고 특정 범위를 잠갔을 가능성

이때 다음을 확인합니다.

SHOW INDEX FROM order_items;
  • 해당 인덱스의 컬럼 순서가 쿼리의 조건과 맞는지
  • 조건이 인덱스를 못 타서 더 넓은 범위를 잠그는지

3-4) 갭 락/넥스트키 락이 보이면 격리 수준과 쿼리를 의심

로그에 locks gap 또는 locks rec but gap이 보이면, 단순히 “한 행”이 아니라 범위를 잠그고 있다는 뜻입니다.

주로 아래 상황에서 자주 등장합니다.

  • 격리 수준이 REPEATABLE READ이고 범위 조건으로 SELECT ... FOR UPDATE 수행
  • 유니크하지 않은 보조 인덱스를 조건으로 업데이트
  • 인덱스가 부실해서 스캔 범위가 넓어짐

격리 수준 확인:

SELECT @@transaction_isolation;

해법은 케이스별로 다르지만, 범인 찾기 관점에서는 “왜 범위 락이 생겼는지”를 먼저 파악해야 합니다.

4) 대표 데드락 패턴 3가지와 로그에서의 신호

패턴 A: 업데이트 순서 뒤집힘(가장 흔함)

  • 주문 테이블 업데이트 후 아이템 테이블 업데이트
  • 다른 코드 경로에서는 아이템 먼저, 주문 나중

로그 신호:

  • (1)이 orders를 기다리고 order_items를 보유
  • (2)가 order_items를 기다리고 orders를 보유

해결:

  • 항상 동일한 순서로 잠그기(예: 부모 orders 먼저, 자식 order_items 나중)
  • 필요한 경우 “락 선점”을 명시적으로 수행
-- 예: 트랜잭션 시작 후 항상 같은 순서로 잠금
START TRANSACTION;
SELECT id FROM orders WHERE id = 10 FOR UPDATE;
SELECT id FROM order_items WHERE order_id = 10 FOR UPDATE;
-- 이후 업데이트
COMMIT;

패턴 B: 인덱스 부재로 인한 과도한 잠금

로그 신호:

  • 특정 index가 아니라 엉뚱한 인덱스를 타거나
  • 갭 락/넥스트키 락이 빈번

점검:

EXPLAIN UPDATE order_items
SET qty = qty + 1
WHERE order_id = 10 AND sku = 'A';

해결:

  • 조건에 맞는 복합 인덱스 추가(예: (order_id, sku))
  • 불필요한 범위 조건 제거

패턴 C: 유니크 제약/세컨더리 인덱스 경합

예: INSERT ... ON DUPLICATE KEY UPDATE가 동시에 들어오며 유니크 인덱스에서 충돌

로그 신호:

  • 특정 유니크 인덱스 이름이 반복적으로 등장
  • 같은 키에 대한 레코드 락 경합

해결:

  • 키 설계 재검토(핫키 분산)
  • 재시도 정책(백오프) 적용

5) 재시도는 필요하지만, “증거”를 남겨야 한다

데드락은 완전히 없애기 어렵고, InnoDB도 “재시도 하라”고 말합니다. 문제는 재시도를 해버리면 원인 분석에 필요한 맥락이 사라진다는 점입니다.

권장 로깅(애플리케이션):

  • 에러 코드 1213 발생 시
    • 실행 SQL(또는 쿼리 템플릿과 바인딩 값)
    • 비즈니스 키(예: order_id)
    • 트랜잭션 경계(어느 유스케이스인지)
    • 재시도 횟수

Java/Spring 예시(개념 코드):

int maxRetries = 3;
for (int i = 0; i < maxRetries; i++) {
  try {
    // do transactional work
    return;
  } catch (SQLException e) {
    if (e.getErrorCode() == 1213) {
      log.warn("deadlock 1213: orderId={}, retry={}", orderId, i + 1, e);
      Thread.sleep(50L * (i + 1));
      continue;
    }
    throw e;
  }
}
throw new RuntimeException("deadlock retry exhausted");

이런 로그가 있으면, InnoDB 로그의 “두 쿼리”를 서비스 코드/요청과 연결하기가 훨씬 쉬워집니다.

6) 운영에서 빠르게 재현·검증하는 체크리스트

  1. SHOW ENGINE INNODB STATUS\G에서 LATEST DETECTED DEADLOCK 확보
  2. (1)과 (2)의 SQL, 인덱스, 락 타입을 표로 정리
  3. 두 SQL의 “락 획득 순서”가 뒤집혔는지 확인
  4. EXPLAIN으로 인덱스/스캔 범위 확인
  5. 갭 락이 보이면 격리 수준과 범위 조건을 함께 점검
  6. 해결책 적용 후 같은 부하에서 재현 테스트

트랜잭션 경합은 데이터베이스만의 문제가 아니라 아키텍처/업무 흐름의 문제로 번지는 경우도 많습니다. 분산 환경에서 “보상 트랜잭션”이나 중복 실행 방지까지 고려해야 하는 상황이라면 MSA SAGA 보상 트랜잭션 중복 실행 방지법도 함께 참고하면, 데드락 재시도와 비즈니스 멱등성 설계를 연결해 볼 수 있습니다.

또한 MySQL만 보지 말고 다른 DB의 유지보수/진단 관점도 비교해보면 문제를 보는 눈이 넓어집니다. 예를 들어 PostgreSQL의 진단 루틴은 VACUUM 관점에서 전혀 다른 단서를 제공합니다: PostgreSQL VACUUM 안 도는 이유 7가지와 해법.

7) 결론: 데드락 로그는 “추리 소설”이 아니라 “포렌식 보고서”다

InnoDB 데드락 로그에는 이미 범인을 특정할 수 있는 정보가 들어 있습니다.

  • 어떤 두 트랜잭션이
  • 어떤 SQL을 실행하다가
  • 어떤 테이블/인덱스의 어떤 락에서
  • 서로 무엇을 기다리며 교착됐는지

핵심은 로그를 읽는 순서를 정해두는 것입니다.

  1. 트랜잭션 두 개의 SQL을 먼저 본다
  2. 각각의 WAITING FOR와 HOLDS를 비교한다
  3. 인덱스/락 타입으로 “왜 그 범위를 잠갔는지”를 확인한다
  4. 코드에서 락 순서/인덱스/트랜잭션 길이를 고친다

이 과정을 몇 번만 반복하면, 1213은 더 이상 “운영에서 가끔 나는 미스터리”가 아니라, 재현 가능하고 수정 가능한 성능/정합성 이슈로 바뀝니다.

Discuss on Twitter • View on GitHub