beautifulsoups.dev
Published on

MySQL InnoDB 데드락 원인 추적 - SHOW ENGINE부터 인덱스까지

Authors

서버가 느려지고, 애플리케이션 로그에 Deadlock found when trying to get lock; try restarting transaction가 찍히면 대부분은 “운이 나빴다”로 넘기기 쉽습니다. 하지만 InnoDB 데드락은 대개 재현 가능한 패턴(락 순서, 인덱스 부재, 범위 잠금, FK/유니크 제약, 갭 락)에서 발생합니다. 이 글에서는 SHOW ENGINE INNODB STATUS로 “마지막 데드락”을 해석해 원인을 특정하고, 인덱스/쿼리/트랜잭션 설계로 재발을 줄이는 흐름을 단계별로 정리합니다.

> 참고: 장애 상황에서 원인 추적은 ‘관측 가능성’이 핵심입니다. 프록시/타임아웃/버퍼링 문제를 체크리스트로 좁혀가는 방식은 FastAPI Uvicorn에서 SSE 웹소켓 LLM 스트리밍이 프록시 뒤에서 끊길 때… 체크리스트처럼 증상→관측→가설 검증 구조로 접근하면 효율이 좋습니다.

1) 데드락의 본질: “서로가 서로의 락을 기다리는 순환”

InnoDB 데드락은 단순히 두 쿼리가 충돌했다는 뜻이 아니라, **트랜잭션 A가 가진 락을 B가 기다리고, B가 가진 락을 A가 기다리는 순환(cycle)**이 생겼다는 의미입니다. InnoDB는 이를 감지하면 한 트랜잭션을 희생(victim)으로 롤백해 순환을 깨고, 나머지 트랜잭션을 진행시킵니다.

핵심은 다음 두 가지입니다.

  • 데드락은 “동시성 버그”가 아니라 락 설계/쿼리/인덱스가 만든 구조적 문제인 경우가 많습니다.
  • InnoDB는 데드락을 로그로 남기며, 그 로그는 원인 추적에 필요한 거의 모든 단서(SQL, 락 종류, 인덱스, 레코드 키)를 포함합니다.

2) 1차 진단: SHOW ENGINE INNODB STATUS로 ‘마지막 데드락’ 확인

가장 먼저 볼 것은 다음입니다.

SHOW ENGINE INNODB STATUS\G

출력에서 중요한 섹션은 보통 아래입니다.

  • LATEST DETECTED DEADLOCK
  • TRANSACTIONS
  • ROW OPERATIONS

2.1 LATEST DETECTED DEADLOCK 읽는 법(핵심 필드)

데드락 섹션에는 보통 트랜잭션 2개(또는 그 이상)가 등장하며, 각각에 대해:

  • 어떤 SQL을 실행 중이었는지
  • 어떤 락을 보유(holds) 중인지
  • 어떤 락을 대기(waiting) 중인지
  • 그 락이 걸린 테이블/인덱스/레코드(키 값)

이 정보가 나옵니다.

자주 보이는 문구 예:

  • RECORD LOCKS space id ... index ... lock_mode X locks rec but not gap
  • lock_mode X locks gap before rec (갭 락/넥스트키 락)
  • waiting for this lock to be granted

여기서 중요한 포인트:

  • index 이름이 나온다 → 어떤 인덱스 경로로 락을 잡았는지 추적 가능
  • rec but not gap / locks gap → 범위 잠금(갭/넥스트키)이 개입했는지 판단 가능
  • PRIMARY vs 보조 인덱스 → 어떤 접근 방식이 문제인지(테이블 스캔/광범위 락) 가늠 가능

2.2 “마지막 데드락만” 보인다는 한계

SHOW ENGINE INNODB STATUS는 기본적으로 가장 최근 1건(또는 제한된 수)의 데드락만 보여줍니다. 데드락이 빈번한 환경에서는 “방금 발생한” 사건만 남아, 원인 패턴을 놓칠 수 있습니다.

보완책:

  • 애플리케이션에서 데드락 예외 발생 시 SQL, 바인딩 파라미터, 트랜잭션 경로를 로그로 남기기
  • MySQL에서 데드락 관련 로그를 추가 활성화
-- MySQL 8.0+ (성능/로그 정책에 따라 신중히)
SET GLOBAL innodb_print_all_deadlocks = ON;

> 운영에서 무작정 ON은 로그 폭증을 부를 수 있으니, 기간을 정해 켜거나 샘플링/필터링 전략을 같이 가져가세요.

3) 재현 가능한 전형 패턴 5가지

InnoDB 데드락은 “DB가 이상해서”라기보다, 아래 패턴 중 하나일 때 급증합니다.

3.1 서로 다른 순서로 같은 자원을 업데이트(락 순서 불일치)

가장 흔한 형태입니다.

  • 트랜잭션 A: users → orders 순서로 업데이트
  • 트랜잭션 B: orders → users 순서로 업데이트

둘 다 같은 user/order를 건드리면 순환이 생길 수 있습니다.

해결 방향: 트랜잭션 내 테이블/레코드 접근 순서를 전역적으로 통일합니다.

3.2 인덱스 부재로 인해 “필요 이상”의 레코드를 잠금

조건절이 인덱스를 타지 못하면 InnoDB는 더 많은 레코드를 스캔/잠금할 수 있습니다. 특히 UPDATE ... WHERE non_indexed_col = ? 같은 형태는 데드락/락 대기 폭증의 단골입니다.

해결 방향: WHERE/JOIN 조건에 맞는 인덱스를 설계하고, 실행 계획을 확인합니다.

EXPLAIN UPDATE orders
SET status = 'PAID'
WHERE user_id = 123 AND status = 'PENDING';
  • type이 ALL이면 위험 신호(풀스캔)
  • key가 NULL이면 인덱스 미사용

3.3 범위 조건 + REPEATABLE READ로 인한 넥스트키 락(갭 락)

MySQL 기본 격리 수준인 REPEATABLE READ에서는 팬텀 방지를 위해 **넥스트키 락(레코드+갭)**이 걸릴 수 있습니다. 범위 조건이 들어간 업데이트/삭제가 대표적입니다.

-- 범위 업데이트는 넥스트키 락을 유발하기 쉽습니다.
UPDATE coupons
SET used = 1
WHERE user_id = 123
  AND expires_at > NOW()
  AND used = 0
LIMIT 1;

여기서 인덱스가 부정확하거나 범위가 넓으면, 의도보다 넓은 구간이 잠겨 데드락 확률이 올라갑니다.

해결 방향:

  • 범위 조건을 더 선택적으로 만들기
  • 적절한 복합 인덱스 설계
  • 정말 필요하다면 격리 수준/락 전략 재검토(아래 6장에서 다룸)

3.4 유니크 제약/세컨더리 인덱스 업데이트로 인한 잠금 경합

유니크 키가 있는 컬럼을 업데이트하거나, 세컨더리 인덱스가 많은 테이블에 잦은 업데이트가 있으면 인덱스 엔트리 변경 과정에서 락이 늘어납니다.

예: UPDATE users SET email = ? 처럼 유니크 컬럼 변경이 잦을 때.

해결 방향:

  • 유니크 변경 트랜잭션을 짧게
  • 변경 경로를 분리(비동기 처리, 큐)
  • 불필요한 인덱스 제거(쓰기 비용 감소)

3.5 외래키(FK)로 인한 의외의 잠금

부모/자식 테이블에 FK가 있으면, 자식 삽입/삭제 시 부모 레코드를 확인하는 과정에서 락이 걸릴 수 있습니다. 데드락 로그에 “내가 건드린 적 없는 테이블”이 보이면 FK를 의심하세요.

해결 방향:

  • FK 컬럼에 인덱스가 있는지 확인(필수)
  • FK 관계에서 업데이트/삭제 순서 통일

4) SHOW ENGINE 로그에서 “문제 인덱스”를 찾아내는 절차

데드락 로그는 보통 다음 힌트를 줍니다.

  • table 이름
  • index 이름
  • lock_mode (S/X, gap 여부)
  • 레코드 식별 값(HEX/필드 값)

실전 절차는 아래 순서가 효율적입니다.

4.1 데드락에 등장한 SQL을 그대로 EXPLAIN

로그에 찍힌 SQL을 복사해 EXPLAIN/EXPLAIN ANALYZE로 확인합니다.

EXPLAIN ANALYZE
SELECT *
FROM inventory
WHERE product_id = 10
  AND warehouse_id = 3
FOR UPDATE;
  • rows 추정치가 큰지
  • 실제 실행에서 스캔이 과도한지
  • 어떤 인덱스를 탔는지

4.2 “락을 잡는 경로”가 인덱스와 일치하는지 확인

FOR UPDATE/UPDATE/DELETE는 검색 조건으로 찾은 레코드에 락을 잡습니다.

  • 인덱스를 타면 “필요한 레코드만” 잠금
  • 인덱스를 못 타면 스캔 과정에서 “많은 레코드”를 잠금

즉, 데드락은 종종 인덱스 설계 문제의 결과입니다.

4.3 복합 인덱스 설계: WHERE 절의 동등 조건부터

일반적으로 동등 조건(=)이 앞에 오고, 범위 조건(>, <, BETWEEN)이 뒤에 오도록 복합 인덱스를 설계합니다.

예:

-- 쿼리
SELECT *
FROM coupons
WHERE user_id = ?
  AND used = 0
  AND expires_at > NOW()
FOR UPDATE;

-- 후보 인덱스
CREATE INDEX idx_coupons_user_used_expires
ON coupons(user_id, used, expires_at);

이렇게 하면 user_id, used로 먼저 좁히고, expires_at 범위를 최소화해 갭/넥스트키 락 범위를 줄이는 데 도움이 됩니다.

5) 애플리케이션 관점 대응: “재시도”는 필요하지만 만능은 아님

InnoDB는 데드락 발생 시 한 트랜잭션을 롤백합니다. 따라서 애플리케이션은 데드락을 정상적인 동시성 이벤트로 보고 재시도할 수 있어야 합니다.

다만 재시도만으로는 근본 원인이 남아 재시도 폭풍이 생길 수 있습니다. (특히 트래픽 피크에서)

5.1 재시도 구현 예시(백오프 포함)

아래는 의사 코드(언어 불문)입니다.

maxRetries = 5
for attempt in 1..maxRetries:
  begin transaction
  try:
    do business queries
    commit
    return success
  catch DeadlockError:
    rollback
    sleep( random(0, 50ms) * attempt )
    continue
  catch:
    rollback
    throw
throw DeadlockRetryExceeded
  • 랜덤 지터 + 선형/지수 백오프가 효과적
  • 재시도 횟수 초과 시 사용자 경험/중복 처리 방지 정책 필요

> 재시도/큐잉 패턴은 DB 데드락뿐 아니라 외부 API 과부하에도 동일하게 적용됩니다. 과부하 시 재시도 전략을 정리한 글로 Claude API 529 Overloaded 재시도·큐잉 패턴 정리도 함께 참고하면 설계 관점이 정리됩니다.

6) 쿼리/트랜잭션 설계로 데드락 확률을 낮추는 체크리스트

데드락을 “0”으로 만들기는 어렵지만, 확률을 유의미하게 낮출 수는 있습니다.

6.1 트랜잭션을 짧게: 락 보유 시간을 줄인다

  • 트랜잭션 안에서 네트워크 호출/파일 IO/긴 계산을 하지 않기
  • 사용자 입력 대기 같은 작업을 트랜잭션 밖으로 빼기
  • 필요한 SQL만 묶기

6.2 같은 자원은 같은 순서로 잠근다(가장 효과적)

예를 들어 주문 처리에서:

  1. users를 먼저 잠그고
  2. 그 다음 orders
  3. 그 다음 inventory

처럼 모든 코드 경로에서 동일한 순서를 강제하면 순환이 크게 줄어듭니다.

6.3 SELECT ... FOR UPDATE 사용 시 “정확히 필요한 행만” 잠그기

FOR UPDATE는 강력하지만, 조건이 부정확하면 재앙이 됩니다.

  • 반드시 적절한 인덱스가 있는 조건으로 사용
  • 범위가 넓은 FOR UPDATE는 피하기

6.4 격리 수준/락 모드 재검토(신중히)

  • REPEATABLE READ에서 갭/넥스트키 락이 문제라면, 워크로드에 따라 READ COMMITTED가 데드락/락 대기를 줄이기도 합니다.
  • 하지만 일관성/비즈니스 요구사항(팬텀 허용 여부)을 먼저 확인해야 합니다.
-- 세션 단위로 테스트
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

6.5 핫스팟(단일 행/단일 키)에 쓰기 집중 완화

  • “카운터 테이블 1행”에 업데이트가 몰리면 데드락/락 대기가 폭증
  • 샤딩 키(예: 날짜/사용자 범위)로 분산하거나, 이벤트 로그 적재 후 집계로 전환

7) 인덱스 관점 결론: 데드락은 종종 ‘인덱스가 만든 락 범위’ 문제다

정리하면, InnoDB 데드락 원인 추적은 다음 순서가 가장 빠릅니다.

  1. SHOW ENGINE INNODB STATUS\G에서 LATEST DETECTED DEADLOCK 확보
  2. 로그에 나온 SQL을 EXPLAIN (ANALYZE)로 실행 계획 확인
  3. 로그에 나온 index/lock_mode로 “왜 범위가 잠겼는지” 역추적
  4. 해결책을 (a) 락 순서 통일 (b) 인덱스 최적화 (c) 트랜잭션 단축 (d) 격리 수준/쿼리 패턴 조정 순으로 적용
  5. 재시도는 안전장치로 두되, 재시도율이 높다면 구조를 바꾼다

8) 실전 예시: 재고 차감 데드락을 인덱스로 줄이는 흐름

상황: 재고 테이블 inventory(product_id, warehouse_id, qty, updated_at)에서 동시 주문이 들어올 때 데드락이 발생.

문제 쿼리:

-- (예) 인덱스가 product_id만 있을 때
UPDATE inventory
SET qty = qty - 1
WHERE product_id = ?
  AND warehouse_id = ?
  AND qty > 0;

개선:

  1. 조건에 맞는 복합 인덱스 추가
CREATE INDEX idx_inventory_product_wh_qty
ON inventory(product_id, warehouse_id, qty);
  1. 실행 계획 확인
EXPLAIN UPDATE inventory
SET qty = qty - 1
WHERE product_id = 10
  AND warehouse_id = 3
  AND qty > 0;
  1. 트랜잭션에서 다른 테이블 업데이트 순서 통일(예: inventory → orders)

이런 조합으로 “잠그는 행 수”와 “락 보유 시간”을 동시에 줄이면 데드락 빈도가 체감될 정도로 떨어집니다.

데드락은 피할 수 없는 동시성의 부산물이지만, InnoDB는 원인 추적에 필요한 정보를 이미 제공합니다. SHOW ENGINE INNODB STATUS를 읽는 능력과, 그 결과를 인덱스/쿼리/트랜잭션 설계로 연결하는 습관이 쌓이면 “재시도만 하는 운영”에서 “재발을 줄이는 운영”으로 넘어갈 수 있습니다.

Discuss on Twitter • View on GitHub