beautifulsoups.dev
Published on

MySQL InnoDB 데드락 로그로 원인 쿼리 추적

Authors

운영 MySQL에서 데드락이 터지면 가장 곤란한 점은 재현이 어렵고, 애플리케이션 로그에는 보통 Deadlock found when trying to get lock 정도만 남는다는 것입니다. 하지만 InnoDB는 데드락이 발생한 순간의 “잠금 그래프”를 꽤 자세히 남깁니다. 이 로그를 제대로 읽으면 어떤 트랜잭션이 어떤 인덱스의 어떤 레코드를 잡고 있었고, 어떤 쿼리가 다음 잠금을 기다리다가 교착 상태가 됐는지까지 추적할 수 있습니다.

이 글은 다음 흐름으로 진행합니다.

  • 데드락 로그를 확보하는 방법
  • 로그의 핵심 필드(트랜잭션, 락, 인덱스, 레코드)를 해석하는 방법
  • 로그에서 “원인 쿼리”를 특정하는 실전 절차
  • 재발 방지(쿼리/인덱스/트랜잭션 설계) 체크리스트

참고로, 장애 디버깅은 결국 “관측 가능성” 싸움입니다. 비슷한 맥락의 디버깅 글로는 GitHub Actions 캐시가 안 먹을 때 디버깅 체크리스트도 함께 보면 문제를 구조적으로 쪼개는 감각에 도움이 됩니다.

데드락 로그 확보: 어디서, 어떻게 보나

1) 즉시 확인: SHOW ENGINE INNODB STATUS

InnoDB는 “마지막 데드락 1건”을 상태 출력에 포함합니다.

SHOW ENGINE INNODB STATUS\G

출력에서 LATEST DETECTED DEADLOCK 섹션이 핵심입니다. 단, 이 정보는 “마지막 1건”만 유지되므로 데드락이 연속으로 발생하면 덮어써집니다. 운영에서 데드락이 간헐적으로 터진다면, 애플리케이션에서 에러를 감지한 즉시 이 출력을 수집해 두는 자동화가 유용합니다.

2) 지속 기록: InnoDB 데드락을 에러 로그로 남기기

MySQL은 데드락 정보를 에러 로그에도 남길 수 있습니다.

SET GLOBAL innodb_print_all_deadlocks = ON;
  • 장점: 마지막 1건이 아니라 “발생할 때마다” 남음
  • 주의: 에러 로그가 빠르게 커질 수 있으니 로테이션 정책과 함께 적용

MySQL 에러 로그 경로는 환경별로 다르지만, 예를 들어 다음처럼 확인할 수 있습니다.

SHOW VARIABLES LIKE 'log_error';

컨테이너 환경이라면 표준 출력으로 흘러가는 경우도 많습니다.

InnoDB 데드락 로그, 어디를 봐야 하나

데드락 로그는 길지만, 원인 쿼리를 찾는 데 필요한 정보는 생각보다 정형화돼 있습니다. 핵심은 “(1) 어떤 트랜잭션들이 엮였는지”와 “(2) 각 트랜잭션이 잡은 락과 기다리는 락이 무엇인지”입니다.

아래는 전형적인 로그 형태를 단순화한 예시입니다(실제 출력은 더 길고, 레코드 덤프도 포함될 수 있습니다).

------------------------
LATEST DETECTED DEADLOCK
------------------------

*** (1) TRANSACTION:
TRANSACTION 123456, ACTIVE 0 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 1400, query id 999 localhost app_user updating
UPDATE orders SET status='PAID' WHERE id=10;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 42 page no 123 n bits 72 index PRIMARY of table `shop`.`orders`
trx id 123456 lock_mode X locks rec but not gap waiting

*** (2) TRANSACTION:
TRANSACTION 123457, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 112, OS thread handle 1401, query id 1000 localhost app_user updating
UPDATE orders SET status='CANCEL' WHERE id=10;

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 42 page no 123 n bits 72 index PRIMARY of table `shop`.`orders`
trx id 123457 lock_mode X locks rec but not gap

*** WE ROLL BACK TRANSACTION (1)

이 예시는 동일 row id=10에 대해 두 트랜잭션이 경쟁하는 단순 사례입니다. 하지만 실전 데드락은 보통 다음이 섞여 더 복잡해집니다.

  • 서로 다른 테이블을 서로 다른 순서로 업데이트
  • 보조 인덱스(secondary index)에서 먼저 잠금 후 PK 레코드 잠금
  • SELECT ... FOR UPDATE 또는 UPDATE ... WHERE가 범위 스캔을 하면서 gap lock/next-key lock
  • 외래키 제약으로 인해 “부모/자식 테이블” 잠금이 연쇄적으로 발생

로그로 “원인 쿼리”를 찾는 7단계 절차

1) 어떤 트랜잭션이 롤백됐는지부터 확인

로그 마지막에 보통 다음 문장이 있습니다.

  • *** WE ROLL BACK TRANSACTION (N)

InnoDB는 데드락을 끊기 위해 한 트랜잭션을 희생합니다. 희생된 트랜잭션이 애플리케이션에서 에러를 발생시킨 주체입니다. 하지만 “원인”이 꼭 희생자 쿼리인 것은 아닙니다. 구조적으로는 둘 다 원인입니다.

실무에서는 다음을 함께 봅니다.

  • 희생 트랜잭션의 쿼리: 사용자 영향(에러) 관점
  • 반대 트랜잭션이 잡고 있던 락: 구조적 병목(락 순서/범위) 관점

2) 각 트랜잭션의 쿼리 문장을 확보

TRANSACTION 블록 안쪽에 쿼리가 그대로 찍히는 경우가 많습니다. 다만 다음 케이스에서는 쿼리가 잘리지 않게 추가 설정이 필요할 수 있습니다.

  • 긴 쿼리(동적 SQL, 대량 IN)
  • 스토어드 프로시저 내부
  • ORM이 생성한 쿼리

가능하면 애플리케이션에서도 다음을 함께 남기면 추적이 쉬워집니다.

  • 트랜잭션 시작 시점과 커밋/롤백 시점
  • 동일 요청의 correlation id
  • MySQL thread id 또는 connection id

3) “기다리는 락”을 먼저 읽고, “이미 가진 락”으로 거슬러 올라가기

각 트랜잭션 블록에는 보통 두 섹션이 있습니다.

  • WAITING FOR THIS LOCK TO BE GRANTED
  • HOLDS THE LOCK(S)

읽는 순서는 다음이 효율적입니다.

  1. 트랜잭션 (1)의 WAITING을 본다
  2. 그 락을 누가 HOLDS하고 있는지 (보통 트랜잭션 (2))를 본다
  3. 트랜잭션 (2)가 또 무엇을 WAITING하는지 본다

이렇게 하면 교착 고리가 눈에 들어옵니다.

4) 테이블과 인덱스를 확인해서 “어떤 접근 경로로 잠금이 잡혔는지” 추정

락 라인에는 보통 다음 정보가 나옵니다.

  • of table db.table``
  • index PRIMARY 또는 index idx_xxx
  • lock_mode X 또는 lock_mode S
  • locks rec but not gap 또는 locks gap 또는 locks rec but not gap waiting

여기서 중요한 포인트:

  • index PRIMARY에 대한 레코드 락이면 “특정 PK row”를 잡고 있을 가능성이 큼
  • index idx_xxx에 대한 락이면 “보조 인덱스 레코드”를 먼저 잠갔을 수 있음
  • gap 또는 next-key 성격이 보이면 범위 조건이 개입했을 가능성이 큼

예를 들어 UPDATE ... WHERE user_id = 123 AND created_at BETWEEN ... 같은 쿼리가 created_at 인덱스를 타며 범위 잠금을 만들어 다른 트랜잭션의 insert/update를 막는 형태가 흔합니다.

5) 레코드 덤프(있는 경우)로 실제 키 값을 복원

데드락 로그는 종종 Record lock, heap no ... 같은 레코드 덤프를 포함합니다. 이 덤프는 사람이 읽기 어렵지만, “어떤 키 값이 잠겼는지”를 복원하는 실마리가 됩니다.

다만 MySQL 버전과 설정에 따라 덤프 형식이 달라서, 실무에서는 다음의 우회가 더 빠를 때가 많습니다.

  • 해당 인덱스(PRIMARY 또는 idx_xxx)로 EXPLAIN을 떠서 접근 범위를 파악
  • 동일 조건으로 SELECT를 실행해 어떤 row가 매칭되는지 확인
  • 애플리케이션 로그의 파라미터(예: order_id=10)와 대조

6) INFORMATION_SCHEMA로 “그 시점에 살아있는 락”을 보조 확인

데드락 로그는 사건의 스냅샷이고, 발생 직후에는 여전히 관련 락이 남아있을 수 있습니다. 특히 데드락이 빈번하면, 현재 락 대기 상황을 같이 보면 패턴이 더 선명해집니다.

MySQL 8.0 기준으로는 performance_schema가 더 강력하지만, 여기서는 범용적으로 많이 쓰는 뷰를 예시로 듭니다.

-- 현재 락 대기(버전/설정에 따라 다를 수 있음)
SELECT *
FROM information_schema.innodb_trx;

SELECT *
FROM information_schema.innodb_lock_waits;

SELECT *
FROM information_schema.innodb_locks;

이 결과에서 trx_mysql_thread_id를 데드락 로그의 MySQL thread id와 매칭하면, “로그에 찍힌 커넥션이 지금도 무엇을 하고 있는지”를 확인할 수 있습니다.

7) 최종적으로 “충돌하는 잠금 순서”를 문장으로 재구성

원인 쿼리를 특정하는 마지막 단계는, 로그를 사람이 이해하는 시나리오로 바꾸는 것입니다. 예:

  • 트랜잭션 A: orders의 특정 row를 업데이트하면서 PRIMARY 레코드 X 락 획득
  • 트랜잭션 B: order_items를 먼저 업데이트해 자식 row X 락 획득
  • 트랜잭션 A는 이후 order_items를 업데이트하려다 B가 잡은 락을 기다림
  • 트랜잭션 B는 이후 orders를 업데이트하려다 A가 잡은 락을 기다림
  • 결과적으로 A와 B가 서로를 기다려 데드락

이 “락 순서의 교차”가 보이면, 해결책도 자연스럽게 후보가 좁혀집니다.

실전에서 자주 나오는 데드락 패턴 4가지

1) 서로 다른 테이블을 다른 순서로 업데이트

가장 흔한 케이스입니다.

  • 요청 1: orders 업데이트 후 order_items 업데이트
  • 요청 2: order_items 업데이트 후 orders 업데이트

해결의 핵심은 “항상 같은 순서로 잠금을 획득”하도록 만드는 것입니다. 즉, 애플리케이션에서 업데이트 순서를 통일하거나, 하나의 트랜잭션에서 테이블 접근 순서를 강제합니다.

2) 보조 인덱스 잠금과 PK 잠금의 조합

InnoDB는 보조 인덱스로 row를 찾은 다음 PK 레코드를 잠그는 과정에서 여러 락이 얽힐 수 있습니다. 특히 다음 조건이 있으면 위험도가 올라갑니다.

  • 조건절이 인덱스를 애매하게 타서 스캔 범위가 넓음
  • 유니크하지 않은 인덱스(동일 키가 많음)

대응은 보통 다음 둘 중 하나입니다.

  • 더 선택도가 좋은 인덱스 설계(복합 인덱스)
  • 쿼리를 PK 기반으로 쪼개서 잠금 대상을 축소

예를 들어 다음처럼 “먼저 PK를 좁히고, 그 PK로 업데이트”하는 형태가 데드락/락 경합을 줄이는 데 도움이 됩니다.

-- 1) 잠금 범위를 줄이기 위해 먼저 PK 목록을 좁힘
SELECT id
FROM orders
WHERE user_id = 123
  AND status = 'PENDING'
ORDER BY id
LIMIT 50
FOR UPDATE;

-- 2) 이후 PK로만 업데이트
UPDATE orders
SET status = 'PAID'
WHERE id IN ( ... );

3) 범위 조건으로 인한 gap lock/next-key lock

REPEATABLE READ에서 범위 조건이 걸리면 next-key lock이 생겨 insert/update를 막으면서 교착이 발생할 수 있습니다.

특히 다음 패턴이 위험합니다.

  • SELECT ... FOR UPDATE가 범위를 넓게 잡음
  • 동시에 다른 트랜잭션이 그 범위에 INSERT 또는 UPDATE

해결 옵션:

  • 범위를 줄이는 인덱스/조건 개선
  • 트랜잭션 격리 수준을 READ COMMITTED로 낮출 수 있는지 검토(업무 정합성 확인 필수)

격리 수준 확인:

SHOW VARIABLES LIKE 'transaction_isolation';

4) 외래키로 인한 연쇄 잠금

외래키가 있으면 부모/자식 테이블 검증 과정에서 예상보다 락이 더 잡힙니다. 예를 들어 자식 insert가 부모 row를 참조 확인하면서 부모에 잠금이 걸리거나, 부모 delete/update가 자식 검사 때문에 잠금 대기가 발생할 수 있습니다.

이 경우 로그에서 서로 다른 테이블이 엮여 나오며, 원인 쿼리가 “단일 UPDATE”처럼 보여도 실제로는 FK 검사까지 포함한 잠금 그래프가 원인입니다.

원인 쿼리를 찾았으면: 재발 방지 체크리스트

1) 트랜잭션을 짧게: 네트워크 호출/외부 I/O를 트랜잭션 밖으로

데드락은 결국 “잠금을 오래 쥐고 있는 시간”이 길수록 확률이 올라갑니다.

  • 트랜잭션 안에서 HTTP 호출, 메시지 발행, 파일 I/O를 하지 않기
  • 사용자 입력 대기 같은 비동기 흐름을 트랜잭션 밖으로 빼기

2) 업데이트 순서 통일

앞서 말한 것처럼 테이블 접근 순서를 강제하면 데드락 확률이 크게 줄어듭니다.

  • 항상 부모 테이블 먼저, 자식 테이블 나중
  • 항상 A 테이블 후 B 테이블

3) 인덱스로 잠금 범위를 줄이기

WHERE 조건이 인덱스를 잘 타면 “잠그는 row 수”가 줄고, 데드락/락 대기 모두 감소합니다.

  • EXPLAIN으로 실제 사용 인덱스 확인
  • 복합 인덱스는 “자주 같이 필터링되는 컬럼” 순서로 설계
EXPLAIN UPDATE orders
SET status='PAID'
WHERE user_id=123 AND status='PENDING';

4) 재시도 전략을 애플리케이션에 넣기

완벽히 제거가 어려운 데드락도 있습니다(특히 고경합 카운터/재고/좌석 같은 도메인). 이때는 데드락을 “일시적 충돌”로 보고 재시도하는 전략이 현실적입니다.

  • 데드락 에러 코드(예: ER_LOCK_DEADLOCK)를 잡아 짧은 지수 백오프 후 재시도
  • 재시도 횟수 제한과 idempotency 보장

5) 로그/관측 자동화

데드락은 발생 순간을 놓치면 추적 비용이 급증합니다.

  • innodb_print_all_deadlocks 활성화 + 에러 로그 수집
  • 애플리케이션에서 데드락 감지 시 connection id, 쿼리, 바인딩 파라미터를 함께 기록

운영에서 “증상이 보일 때 바로 원인을 좁히는” 관점은 다른 장애에서도 동일합니다. 예를 들어 캐시/빌드 문제를 체크리스트로 좁혀가는 방식은 GitHub Actions 캐시가 안 먹을 때 디버깅 체크리스트와 유사한 접근입니다.

데드락 로그 기반 추적 미니 예제: 로그에서 쿼리까지 역추적

가령 다음과 같은 상황을 가정해봅시다.

  • 트랜잭션 A: 사용자 주문 결제 처리
  • 트랜잭션 B: 주문 취소 처리
  • 둘 다 orders와 order_items를 만지는데 순서가 다름

데드락 로그에 다음처럼 나온다고 합시다.

*** (1) TRANSACTION:
... query id 2001 ...
UPDATE orders SET status='PAID' WHERE id=10;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
... of table `shop`.`order_items` index idx_order_id ... lock_mode X ... waiting

*** (2) TRANSACTION:
... query id 2002 ...
UPDATE order_items SET canceled=1 WHERE order_id=10;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
... of table `shop`.`orders` index PRIMARY ... lock_mode X ... waiting

이 로그만으로도 결론은 명확합니다.

  • A는 orders를 먼저 잠그고 order_items로 이동
  • B는 order_items를 먼저 잠그고 orders로 이동
  • 테이블 접근 순서가 반대라서 데드락

해결은 보통 다음 중 하나입니다.

  • 결제/취소 모두 orders를 먼저 업데이트하고 order_items를 나중에 업데이트하도록 순서 통일
  • 가능하면 한쪽 로직을 “상태 전이” 중심으로 단순화해서 한 테이블만 잠그게 만들기

마무리

InnoDB 데드락은 “운이 나쁘면 생기는” 현상처럼 보이지만, 로그를 잠금 관점으로 읽으면 대부분 구조적 원인이 드러납니다. 핵심은 데드락 로그에서 다음 3가지를 뽑아내는 것입니다.

  • 어떤 트랜잭션들이 엮였는가
  • 각 트랜잭션이 무엇을 잡고 무엇을 기다렸는가
  • 그 잠금이 어떤 인덱스/접근 경로에서 발생했는가

이 3가지만 정리되면, 원인 쿼리는 높은 확률로 특정되고(혹은 최소한 후보가 1~2개로 좁혀지고), 해결책도 업데이트 순서 통일, 인덱스 개선, 트랜잭션 단축, 재시도 도입 같은 실전 옵션으로 연결됩니다.

다음 단계로는, 실제 데드락 로그 한 건을 가져와서 WAITING과 HOLDS를 표로 정리하고 “락 순서”를 문장으로 재구성해보면 팀 내 공유와 재발 방지에 큰 도움이 됩니다.

Discuss on Twitter • View on GitHub