beautifulsoups.dev
Published on

MySQL InnoDB 데드락 폭증? 실전 튜닝 7단계

Authors

서버가 잘 돌다가 어느 날부터 Deadlock found when trying to get lock 로그가 폭증하면, 대부분은 “트래픽 증가”가 아니라 락을 잡는 방식이 바뀐 것(쿼리 플랜 변화, 인덱스 누락, 트랜잭션 범위 확대, 배치/스케줄러 겹침)입니다. InnoDB 데드락은 완전히 0으로 만들기 어렵지만, **빈도와 피해(롤백 비용)**는 구조적으로 낮출 수 있습니다.

아래 7단계는 현장에서 바로 적용 가능한 순서로 구성했습니다. 핵심은 관측을 먼저 고정하고, 그 다음 락 범위와 락 순서를 제어하는 것입니다.

1단계: 데드락 “증거”를 고정 수집하기

데드락은 순간 이벤트라서, 재현이 어려우면 해결도 어렵습니다. 우선 서버가 어떤 데드락을 보고 있는지 정확한 스냅샷을 남기세요.

필수 명령

-- 가장 최근 데드락 1건 상세
SHOW ENGINE INNODB STATUS\G

-- 현재 대기/차단 관계 (MySQL 8.0)
SELECT *
FROM performance_schema.data_lock_waits\G;

-- 현재 잡힌 락 목록
SELECT *
FROM performance_schema.data_locks
ORDER BY ENGINE_LOCK_ID\G;

설정 체크

  • SHOW ENGINE INNODB STATUS는 “마지막 데드락 1건”만 보여줍니다. 폭증 상황에서는 로그로 흘려야 합니다.
-- 데드락 정보를 에러 로그로 남김
SET PERSIST innodb_print_all_deadlocks = ON;

운영에서 장애가 연쇄로 번질 때는 DB만 보지 말고, 타임아웃/재시도 정책도 함께 점검하세요. 특히 RPC 타임아웃이 짧고 재시도가 공격적으로 걸려 있으면 데드락이 “원인”이 아니라 “증폭기”가 됩니다. 분산 환경 타임아웃 설계는 이 글도 참고할 만합니다: gRPC MSA에서 DEADLINE_EXCEEDED 연쇄 장애 차단

2단계: “어떤 쿼리 조합”이 부딪히는지 패턴화

데드락 로그를 보면 보통 두 트랜잭션이 서로 다른 순서로 같은 리소스를 잡습니다.

  • 트랜잭션 A: 테이블 X의 레코드(또는 갭)를 잡고 테이블 Y를 기다림
  • 트랜잭션 B: 테이블 Y를 잡고 테이블 X를 기다림

이때 중요한 건 SQL 텍스트 자체보다도:

  • 어떤 인덱스를 타는지
  • 범위 스캔인지(갭락/넥스트키락 가능)
  • 같은 테이블을 업데이트하더라도 “잠그는 레코드 집합”이 동일한지

재현을 위한 최소 정보

  • 테이블 스키마(인덱스 포함)
  • 두 트랜잭션의 SQL 순서
  • 각 SQL의 EXPLAIN 결과
EXPLAIN FORMAT=tree
UPDATE orders
SET status = 'PAID'
WHERE user_id = 123 AND status = 'PENDING';

EXPLAIN에서 type=range, rows가 크거나, 의도한 인덱스를 못 타고 ALL로 풀스캔이면 “락 범위가 넓어지는” 방향으로 데드락 가능성이 급상승합니다.

3단계: 인덱스로 “락 범위”를 줄여라 (가장 강력)

InnoDB는 인덱스를 통해 레코드를 찾고, 그 과정에서 락을 잡습니다. 인덱스가 없거나 선택도가 낮으면:

  • 더 많은 레코드를 스캔
  • 더 많은 레코드/갭을 잠금
  • 대기 시간이 길어져 교차 대기 확률 증가

전형적인 개선 예시

상태 전이 업데이트는 데드락의 단골입니다.

-- 나쁜 예: status만으로 찾으면 범위가 넓어질 수 있음
UPDATE orders
SET status='PAID'
WHERE status='PENDING'
LIMIT 1;

개선 방향:

  • “작업 단위”를 식별하는 조건을 인덱스로 좁히기
  • 가능하면 PK 기반으로 업데이트하기
-- 1) 먼저 PK를 좁혀 잡고
SELECT id
FROM orders
WHERE user_id = ? AND status='PENDING'
ORDER BY id
LIMIT 1
FOR UPDATE;

-- 2) PK로 업데이트
UPDATE orders
SET status='PAID'
WHERE id = ?;

추천 인덱스 예:

CREATE INDEX idx_orders_user_status_id ON orders(user_id, status, id);

이 패턴은 “락을 잡는 레코드 집합”을 작게 만들고, 동시에 “락 순서”도 id 오름차순으로 정렬되기 쉬워져 데드락이 줄어듭니다.

4단계: 트랜잭션을 짧게, 락을 늦게 잡고 빨리 풀어라

데드락은 결국 “락을 쥔 시간”과 “교차 대기”의 함수입니다. 트랜잭션이 길어지는 흔한 원인:

  • 트랜잭션 안에서 외부 API 호출
  • 트랜잭션 안에서 파일 I/O
  • 불필요한 SELECT 후 로직 처리
  • 같은 트랜잭션에서 여러 테이블을 넓게 업데이트

안티패턴

BEGIN;
SELECT * FROM users WHERE id=? FOR UPDATE;
-- 애플리케이션에서 복잡한 계산/외부 호출
UPDATE users SET ... WHERE id=?;
COMMIT;

개선

  • 락이 필요한 시점 직전에 FOR UPDATE
  • 외부 호출은 트랜잭션 밖으로
  • 한 번에 처리할 행 수를 제한(배치 쪼개기)

또한 “사용자 요청 트랜잭션”과 “배치 트랜잭션”이 같은 자원을 건드리면 폭증하기 쉽습니다. 배치가 있다면:

  • 시간 분리
  • 테넌트/샤드 단위 분리
  • 배치도 작은 커밋 단위로

5단계: 락 획득 순서를 통일하라 (교차 대기 제거)

여러 테이블을 갱신하는 업무 로직에서 데드락이 자주 납니다. 해결의 정석은 항상 동일한 순서로 락을 획득하는 것입니다.

예를 들어 다음 두 트랜잭션이 있다면:

  • A: accounts 업데이트 후 ledger 삽입
  • B: ledger 삽입 후 accounts 업데이트

이 구조는 언젠가 데드락이 납니다. 둘 다 accounts를 먼저 잠그도록 통일하세요.

실전 팁

  • 애플리케이션 레벨에서 “락 순서 규약”을 문서화
  • DAO/Repository 계층에서 테이블 접근 순서를 강제
  • 다건 처리 시 정렬을 통일: ORDER BY id ASC로 잠금 순서 고정
-- 다건 업데이트/삭제는 잠금 순서를 고정
SELECT id
FROM orders
WHERE status='PENDING'
ORDER BY id ASC
LIMIT 100
FOR UPDATE;

6단계: 격리 수준과 잠금 모드를 재점검 (갭락/넥스트키락)

특히 MySQL 5.7/8.0의 기본 격리 수준은 REPEATABLE READ이고, 이때 범위 조건이 섞이면 **넥스트키락(레코드락 + 갭락)**이 걸려 데드락 가능성이 커질 수 있습니다.

선택지

  • 트랜잭션 격리 수준을 READ COMMITTED로 낮춰 갭락 영향을 줄이기
  • 범위 조건을 PK 기반 점조회로 바꾸기
  • SELECT ... FOR UPDATE 사용 위치/범위를 최소화
-- 세션 단위로만 완화해 영향 범위를 통제
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

주의:

  • 격리 수준 변경은 “정합성 모델”을 바꾸는 결정입니다.
  • 팬텀 리드 허용 여부, 재고/중복처리 방지 로직이 격리 수준에 의존하는지 반드시 확인하세요.

7단계: 운영 파라미터와 재시도 전략으로 피해를 줄여라

데드락은 InnoDB가 “한 쪽을 희생(롤백)”해 시스템 전체가 멈추지 않게 하는 안전장치입니다. 따라서 완전 제거보다 피해 최소화가 중요합니다.

(1) lock wait과 deadlock을 구분

  • 데드락: 즉시 감지 후 한 트랜잭션 롤백
  • 락 대기 타임아웃: innodb_lock_wait_timeout 초 후 실패
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

대기가 길어질수록 스레드/커넥션이 묶여 장애가 커집니다. 서비스 특성에 맞게 너무 길지 않게 조정하고, 애플리케이션에서 재시도를 설계하세요.

(2) 애플리케이션 재시도는 “지수 백오프 + 지터”

데드락이 났을 때 즉시 재시도하면 같은 타이밍에 다시 충돌합니다.

의사코드:

for attempt in 1..5:
  try tx
  if deadlock:
    sleep(backoff_ms * 2^(attempt-1) + random(0..jitter))
    continue
  else:
    raise

(3) 대기/락 관측을 상시화

  • performance_schema의 락 대기 지표를 주기적으로 수집
  • 데드락 로그를 파싱해 “상위 쿼리 조합 Top N” 대시보드화

운영에서 장애를 “진단 가능한 형태”로 만드는 습관은 다른 장애에도 그대로 통합니다. 예를 들어 503이 갑자기 늘 때도 원인별로 분해해야 해결 속도가 빨라집니다: Spring Boot 3+ Tomcat 503 원인별 진단·해결

자주 터지는 데드락 시나리오 3가지와 처방

1) 상태 전이 경쟁 (PENDING에서 DONE)

  • 증상: 같은 조건으로 여러 워커가 UPDATE ... WHERE status='PENDING' 수행
  • 처방: PK 선점(SELECT id ... FOR UPDATE) 후 PK 업데이트, 워커별 파티셔닝

2) 부모-자식 테이블 교차 갱신

  • 증상: 트랜잭션마다 갱신 순서가 다름
  • 처방: 락 순서 규약 통일(항상 부모 먼저), FK 인덱스 확인

3) 범위 삭제/정리 배치

  • 증상: DELETE ... WHERE created_at 같은 범위 조건이 넓고, 온라인 트래픽과 충돌
  • 처방: 작은 배치로 쪼개기, 인덱스 최적화, 트래픽 낮은 시간대로 이동

체크리스트: 30분 안에 할 수 있는 우선순위

  1. SET PERSIST innodb_print_all_deadlocks = ON; 적용
  2. 데드락 로그에서 “두 트랜잭션 SQL”을 추출해 Top 3 조합 선정
  3. 해당 SQL의 EXPLAIN으로 인덱스 미스/범위 스캔 여부 확인
  4. 인덱스 추가 또는 PK 기반 갱신으로 락 범위 축소
  5. 트랜잭션 범위 축소(외부 호출 제거, 배치 커밋 단위 축소)
  6. 락 획득 순서 통일(테이블/레코드 정렬)
  7. 재시도 정책을 지수 백오프+지터로 변경, 대기 타임아웃 재점검

마무리

InnoDB 데드락은 “DB가 이상해서”가 아니라, 대개 **업데이트 대상의 선택 방식(인덱스/범위)**과 락 순서가 만들어낸 결과입니다. 관측을 고정하고(1단계), 락 범위를 줄이고(3단계), 트랜잭션을 짧게 만들고(4단계), 락 순서를 통일(5단계)하면 데드락 폭증은 대부분 안정화됩니다.

다음 단계로는 데드락 로그 샘플을 기반으로 “어떤 인덱스를 어떤 순서로” 설계해야 하는지까지 구체적으로 들어가면 효과가 더 큽니다. 로그의 트랜잭션 두 개와 관련 테이블 DDL을 확보해 두세요.

Discuss on Twitter • View on GitHub