beautifulsoups.dev
Published on

MySQL InnoDB 데드락 로그로 원인 쿼리 찾기

Authors

서버가 멀쩡해 보이는데도 애플리케이션에서 간헐적으로 Deadlock found when trying to get lock; try restarting transaction 오류가 터지면, 대부분의 시간은 **"대체 어떤 쿼리끼리 싸우는지"**를 찾는 데 쓰입니다. InnoDB는 데드락을 감지하면 한 트랜잭션을 희생(rollback)시키고, 그 순간의 스냅샷을 데드락 로그로 남깁니다.

이 글은 그 로그를 단순히 “데드락이 났다” 수준이 아니라, **원인 쿼리(또는 원인 패턴)**까지 찾아내고, 재발 방지까지 이어지는 실전 절차를 다룹니다.

> 운영 환경에서는 데드락 자체가 완전히 사라지지 않을 수 있습니다. 목표는 (1) 원인 쿼리 식별, (2) 락 순서/인덱스/트랜잭션 범위 개선으로 빈도와 영향도를 줄이는 것입니다.

1) InnoDB 데드락 로그를 확보하는 3가지 방법

1-1. SHOW ENGINE INNODB STATUS로 즉시 확인

데드락이 발생하면 InnoDB는 마지막 데드락 1건을 상태 출력에 포함합니다.

SHOW ENGINE INNODB STATUS\G

출력에서 다음 섹션을 찾습니다.

  • LATEST DETECTED DEADLOCK

주의할 점:

  • 마지막 1건만 남습니다. 연속 발생 시 이전 기록은 덮어써집니다.
  • 자동 수집이 필요하면 스케줄러/모니터링이 주기적으로 긁어 저장해야 합니다.

1-2. MySQL 에러 로그에 데드락을 남기기(권장)

MySQL 5.7/8.0에서는 innodb_print_all_deadlocks를 켜면, 데드락이 발생할 때마다 에러 로그로 출력됩니다.

SET GLOBAL innodb_print_all_deadlocks = ON;

영구 적용은 설정 파일에 추가합니다.

[mysqld]
innodb_print_all_deadlocks=1

운영 팁:

  • 에러 로그 수집을 중앙화(예: fluent-bit)하고, 데드락 패턴을 알람으로 걸면 “덮어쓰기” 문제를 피할 수 있습니다.
  • 로그 누락/지연 이슈가 있다면 EKS에서 fluent-bit 로그 누락·지연 원인 9가지처럼 파이프라인 안정성도 함께 점검하는 편이 좋습니다.

1-3. Performance Schema로 잠금 대기/트랜잭션 관찰(보조)

데드락 “발생 직후”가 아니라, 지속적인 락 경합을 추적할 때 유용합니다.

  • performance_schema.data_locks
  • performance_schema.data_lock_waits
  • information_schema.innodb_trx

다만, 데드락이 이미 해결(희생 트랜잭션 rollback)된 뒤에는 그 순간의 “정확한 두 쿼리”는 LATEST DETECTED DEADLOCK이 더 결정적입니다.

2) 데드락 로그를 읽는 핵심: “트랜잭션 2개 + 서로의 락을 기다림”

LATEST DETECTED DEADLOCK 섹션은 대체로 다음 흐름입니다.

  1. (1)번 트랜잭션 정보
  2. (1)이 보유한 락 / 기다리는 락
  3. (2)번 트랜잭션 정보
  4. (2)가 보유한 락 / 기다리는 락
  5. WE ROLL BACK TRANSACTION (x) 희생자 표시

이 구조를 이해하면, 로그가 길어도 “원인 쿼리”는 비교적 빠르게 잡힙니다.

3) 예시 로그로 원인 쿼리 뽑아내기(읽는 순서)

아래는 이해를 위한 축약 예시입니다(실제 출력은 더 길 수 있음).

------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 123456, ACTIVE 2 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 101, OS thread handle 1400, query id 8888 app_user updating
UPDATE orders SET status='PAID' WHERE user_id=10 AND id=500;

*** (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`
lock_mode X locks rec but not gap waiting
Record lock, heap no 15 PHYSICAL RECORD: ...

*** (2) TRANSACTION:
TRANSACTION 123457, ACTIVE 2 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 102, OS thread handle 1401, query id 8889 app_user updating
UPDATE orders SET status='CANCEL' WHERE user_id=10 AND id=501;

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

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

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

*** WE ROLL BACK TRANSACTION (1)

3-1. 가장 먼저 볼 것: “실제 실행 중인 SQL”

각 트랜잭션 블록에 실제 SQL이 찍힙니다.

  • (1) UPDATE orders ... id=500
  • (2) UPDATE orders ... id=501

여기서 끝이 아닙니다. 중요한 건 “왜 서로 다른 row 업데이트가 데드락이냐”입니다.

3-2. 두 번째: 어떤 인덱스/레코드 락을 잡았는가

로그의 핵심 줄은 이런 형태입니다.

  • index 'PRIMARY' of table 'shop'.'orders'
  • lock_mode X locks rec but not gap
  • waiting

즉, 두 트랜잭션 모두 PRIMARY 키 레코드에 X(배타) 락을 잡거나 기다리고 있습니다.

3-3. 세 번째: “서로가 가진 락을 서로가 기다리는” 교차를 찾기

예시에서는:

  • (1)은 page 123의 레코드를 기다림(그 레코드는 (2)가 보유)
  • (2)는 page 124의 레코드를 기다림(그 레코드는 (1)이 보유)

이 교차가 데드락의 정체입니다.

3-4. 희생자 확인: WE ROLL BACK TRANSACTION (1)

MySQL은 보통 “비용이 적은” 트랜잭션(변경량이 적거나, 락이 적거나 등)을 희생자로 고릅니다. 애플리케이션 입장에서는 희생된 쿼리만 에러가 발생하고, 나머지는 성공합니다.

따라서 장애 분석 시:

  • 에러가 난 쿼리(희생자)만 보면 **상대 트랜잭션(승자)**를 놓칠 수 있습니다.
  • 데드락 로그에는 양쪽 쿼리가 함께 나오므로 반드시 같이 봐야 합니다.

4) “원인 쿼리”를 더 정확히 특정하는 실전 체크리스트

4-1. 동일 SQL이라도 “실제 잠근 범위”는 인덱스에 따라 달라진다

WHERE user_id=10 AND id=500처럼 보이지만, 인덱스가 애매하면 MySQL이 다른 인덱스를 타거나(혹은 풀스캔), 그 과정에서 더 많은 레코드/갭을 잠글 수 있습니다.

  • 로그에 찍힌 index '...'를 보고, 해당 인덱스가 의도한 것인지 확인합니다.
EXPLAIN UPDATE orders SET status='PAID'
WHERE user_id=10 AND id=500;

의도:

  • PRIMARY 또는 (user_id, id) 복합 인덱스 등 “정확히 좁히는” 인덱스
  • 불가피하게 범위 스캔이 발생하면 next-key lock(레코드+갭)로 데드락 확률이 급증

4-2. locks gap / locks rec but not gap 문구로 범위락 여부 판단

자주 보게 되는 패턴:

  • locks rec but not gap: 레코드 락 위주(상대적으로 단순)
  • locks gap 또는 locks rec but not gap + 범위 조건: next-key lock 가능성

특히 REPEATABLE READ(기본 격리수준)에서 범위 조건이 섞이면, 팬텀 방지를 위해 갭/넥스트키 락이 늘어납니다.

4-3. 한 트랜잭션 안에서 “락 획득 순서”가 뒤틀리는지 확인

데드락의 전형적 원인:

  • 트랜잭션 A: 테이블/레코드 1 → 2 순으로 잠금
  • 트랜잭션 B: 테이블/레코드 2 → 1 순으로 잠금

애플리케이션 코드에서 다음을 점검합니다.

  • 여러 UPDATE/SELECT ... FOR UPDATE를 수행할 때 항상 동일한 순서로 접근하는지
  • 배치/비동기 작업이 다른 순서로 업데이트하고 있지 않은지

4-4. “보이지 않는 쿼리”를 의심: 트리거, FK CASCADE, 보조 인덱스

로그에 보이는 SQL이 단순 UPDATE라도, 내부적으로 잠금이 늘어날 수 있습니다.

  • FK 제약: 부모/자식 테이블에 추가 잠금
  • ON UPDATE/DELETE CASCADE
  • 트리거: 다른 테이블 갱신
  • 보조 인덱스 다수: 변경 시 인덱스 레코드 잠금도 증가

이 경우 로그에 다른 테이블 락도 같이 찍히는지 확인합니다.

5) 데드락을 재현/검증하는 최소 실험 스크립트

원인을 파악했으면, 로컬이나 스테이징에서 “같은 락 순서”로 재현해보는 게 가장 빠릅니다.

5-1. 세션 1

START TRANSACTION;
UPDATE orders SET status='PAID' WHERE id=500;
-- 일부러 지연
SELECT SLEEP(5);
UPDATE orders SET status='PAID' WHERE id=501;
COMMIT;

5-2. 세션 2

START TRANSACTION;
UPDATE orders SET status='CANCEL' WHERE id=501;
SELECT SLEEP(5);
UPDATE orders SET status='CANCEL' WHERE id=500;
COMMIT;

이렇게 “서로 반대 순서”로 잠그면 데드락이 쉽게 발생합니다. 재현이 되면 해결책(락 순서 통일, 쿼리 변경, 인덱스 변경)의 효과도 검증할 수 있습니다.

6) 해결 전략: 로그에서 보이는 ‘경쟁 지점’을 줄이는 방법

6-1. 락 획득 순서를 통일한다(가장 효과적)

여러 row를 갱신해야 한다면, 애플리케이션에서 정렬 후 동일한 순서로 접근합니다.

예: id 목록을 항상 오름차순으로 잠금

-- 예: 먼저 잠글 레코드를 고정 순서로 가져오기
SELECT id FROM orders
WHERE id IN (500, 501)
ORDER BY id
FOR UPDATE;

-- 이후 업데이트
UPDATE orders SET status='PAID' WHERE id=500;
UPDATE orders SET status='PAID' WHERE id=501;

6-2. 트랜잭션을 짧게: 불필요한 작업을 트랜잭션 밖으로

  • 외부 API 호출
  • 파일/네트워크 I/O
  • 긴 비즈니스 로직

이런 것들이 트랜잭션 안에 있으면 락 보유 시간이 길어져 데드락/락대기가 증가합니다.

6-3. 인덱스로 “잠그는 범위”를 줄인다

  • 조건절이 인덱스를 제대로 타는지 확인
  • 범위 조건/정렬/조인에서 불필요한 스캔 제거

필요하면 복합 인덱스 추가를 검토합니다.

6-4. 격리수준/잠금 방식 재검토

상황에 따라 READ COMMITTED로 낮추면 갭락이 줄어드는 경우가 있습니다(특히 범위 조건에서). 다만 일관성 요구사항을 먼저 확인해야 합니다.

6-5. 애플리케이션 레벨 재시도(필수에 가깝다)

완벽히 제거가 어렵다면, 데드락은 “정상적으로 발생 가능한 이벤트”로 보고 재시도를 넣는 것이 일반적입니다.

예: 의사코드

for attempt in 1..3:
  try:
    begin
    ...
    commit
    return success
  catch DeadlockError:
    rollback
    sleep(backoff(attempt))
throw

재시도는 무한이 아니라 제한(예: 2~3회) + 지수 백오프를 권장합니다.

7) 운영에서의 수집/분석 루틴(덮어쓰기 방지)

  1. innodb_print_all_deadlocks=1로 에러 로그에 남김
  2. 로그 수집 파이프라인에서 LATEST DETECTED DEADLOCK 또는 Deadlock found 패턴을 별도 인덱싱
  3. 로그에서 다음 필드를 추출해 대시보드화
    • 발생 시각
    • DB/테이블
    • (1)/(2) SQL
    • index 이름
    • 희생자 트랜잭션 번호
  4. 상위 N개 패턴(동일 SQL 조합)을 기준으로 개선 우선순위 결정

로그를 “원인 규명”이 아니라 “개선 사이클”로 연결하는 것이 핵심입니다. 장애 대응에서 원인을 좁혀나가는 방식은 네트워크/권한 문제를 로그로 역추적하는 접근과도 유사합니다. 예를 들어 403 원인을 체계적으로 분류하는 글인 GitHub Actions OIDC 403·권한거부 원인 7가지처럼, 데드락도 패턴화하면 대응 속도가 크게 빨라집니다.

8) 빠른 요약: 데드락 로그로 원인 쿼리 찾는 순서

  • SHOW ENGINE INNODB STATUS\G 또는 innodb_print_all_deadlocks로 로그 확보
  • LATEST DETECTED DEADLOCK에서 (1)/(2) 트랜잭션의 SQL을 먼저 확인
  • WAITING FOR THIS LOCK / HOLDS THE LOCK를 비교해 교차 대기 지점 찾기
  • index '...'를 보고 인덱스가 의도대로인지 확인(EXPLAIN)
  • 해결은 보통: 락 순서 통일 + 트랜잭션 짧게 + 인덱스로 범위 축소 + 제한적 재시도

데드락은 “DB가 나쁜 것”이 아니라, 동시성 하에서 락을 사용하는 시스템이라면 자연스럽게 만나는 현상입니다. 중요한 것은 로그를 읽어 정확히 어떤 쿼리/인덱스/락 패턴이 충돌하는지를 찾아내고, 그 지점을 작게 만드는 것입니다.

Discuss on Twitter • View on GitHub