- Published on
MySQL InnoDB 데드락 폭증 - 원인·튜닝 9단계
- Authors
- Name
- 스타차일드
- https://x.com/ETFBITX
운영 중인 MySQL에서 데드락이 갑자기 폭증하면, 단순히 Deadlock found when trying to get lock 에러를 재시도하는 수준으로는 해결이 안 됩니다. 데드락 자체는 InnoDB가 “교착 상태를 감지해 한 트랜잭션을 희생”시키면서 시스템 전체를 살리는 정상 동작이지만, 폭증은 보통 다음 중 하나를 의미합니다.
- 트래픽/동시성이 변했다
- 쿼리 플랜이 바뀌었다(인덱스 변경, 통계 변화)
- 트랜잭션 경계가 늘어졌다(배치, 외부 호출 포함)
- 잠금 범위가 커졌다(인덱스 미스, 갭락)
아래 9단계는 “원인 규명 → 재현/관측 → 쿼리·스키마·트랜잭션 튜닝 → 운영 안전장치” 순으로 설계했습니다. 기존 7단계 정리보다 더 실무적으로 확장한 버전이며, 필요하면 이전 글도 함께 보세요.
- 관련 글: MySQL InnoDB 데드락 폭증 원인·튜닝 7단계
- 트랜잭션 멱등/중복 처리 관점: MSA Saga 보상 트랜잭션 멱등성·중복처리 패턴
- 애플리케이션 쿼리 폭증 관점(JPA): Spring Boot 3 JPA N+1 폭발 대응 - fetch join·EntityGraph·Batch
1단계: “데드락”과 “락 대기”를 분리해서 본다
데드락과 락 대기는 증상이 비슷하지만 대응이 다릅니다.
- 데드락: 서로가 서로의 락을 기다리는 사이클. InnoDB가 감지 후 한쪽을 롤백.
- 락 대기(타임아웃): 누군가 오래 잡고 있어서 기다리다
innodb_lock_wait_timeout초과.
먼저 에러 로그/애플리케이션 예외를 분류하세요.
- 데드락:
Deadlock found when trying to get lock - 락 대기:
Lock wait timeout exceeded; try restarting transaction
둘 다 많다면, 대개 “긴 트랜잭션 + 큰 잠금 범위”가 공통 원인입니다.
2단계: 데드락 리포트를 확보한다(증거부터)
가장 먼저 해야 할 일은 데드락 리포트 수집입니다.
2-1. 즉시 확인
SHOW ENGINE INNODB STATUS\G
출력에서 LATEST DETECTED DEADLOCK 섹션을 확인합니다. 다만 이 값은 “마지막 1건”만 보여주므로 폭증 상황에서는 유실됩니다.
2-2. 로그로 남기기
운영에서는 다음 옵션을 켜서 로그에 남기는 게 좋습니다.
SET GLOBAL innodb_print_all_deadlocks = ON;
MySQL 에러 로그에 데드락이 누적 기록됩니다.
2-3. Performance Schema로 상시 관측
MySQL 8.x라면 Performance Schema 기반으로 “누가 누구를 막는지”를 더 구조적으로 볼 수 있습니다.
SELECT *
FROM performance_schema.data_lock_waits
LIMIT 50;
환경에 따라 컬럼이 다르지만, 핵심은 REQUESTING_ENGINE_LOCK_ID 와 BLOCKING_ENGINE_LOCK_ID 를 따라가며 어떤 테이블/인덱스가 병목인지 확인하는 것입니다.
3단계: 데드락 패턴을 분류한다(가장 흔한 6가지)
리포트를 보면 대개 아래 패턴 중 하나로 귀결됩니다.
- 잠금 순서 불일치: 트랜잭션 A는 테이블 X 후 Y, 트랜잭션 B는 Y 후 X
- 인덱스 미스로 인한 과도한 레코드 스캔: 필요 이상으로 많은 레코드/갭에 락
- 갭락/넥스트키 락: 범위 조건 +
REPEATABLE READ에서 삽입/갱신 충돌 - 유니크 키 경합: 동일 키로 동시
INSERT또는INSERT ... ON DUPLICATE KEY UPDATE - FK 연쇄 잠금: 부모/자식 테이블에서 의도치 않은 잠금 순환
- 긴 트랜잭션: 외부 API 호출, 대량 처리, 사용자 입력 대기 등
이 분류가 중요한 이유는 “해결책이 완전히 다르기” 때문입니다.
4단계: 잠금 순서를 표준화한다(가장 효과가 큰 1순위)
데드락의 정석적인 해결은 모든 코드 경로에서 잠금 획득 순서를 동일하게 만드는 것입니다.
예를 들어 주문 확정 시 orders 와 inventory 를 갱신한다면, 모든 트랜잭션이 항상 같은 순서로 접근하도록 통일합니다.
나쁜 예(순서가 섞임)
- 경로 A:
orders업데이트 후inventory업데이트 - 경로 B:
inventory업데이트 후orders업데이트
좋은 예(항상 동일)
- 모든 경로:
orders→inventory
코드 레벨에서 다음 원칙이 도움이 됩니다.
- 트랜잭션 내에서 테이블 접근 순서를 문서화
- 공통 서비스 메서드로 묶어 “순서 강제”
- 여러 행을 갱신한다면 정렬된 키 순서로 처리
예시(여러 행을 일정 순서로 잠그기):
SELECT id
FROM inventory
WHERE id IN (101, 5, 77)
ORDER BY id
FOR UPDATE;
ORDER BY 로 잠금 획득 순서를 안정화하면, 교차 잠금 가능성이 줄어듭니다.
5단계: 인덱스로 “잠금 범위”를 줄인다(데드락과 락 대기 동시 완화)
인덱스는 성능뿐 아니라 락 범위를 줄이는 도구입니다. 조건절이 인덱스를 못 타면 InnoDB는 더 많은 레코드를 스캔하면서 더 많은 락을 잡을 수 있습니다.
5-1. 실행 계획 확인
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE user_id = 123
AND status = 'PENDING'
FOR UPDATE;
type이ALL이거나rows가 과도하게 크거나Using where만 있고 적절한 키가 없다면 잠금 범위가 커질 가능성이 큽니다.
5-2. 복합 인덱스 설계
예를 들어 위 쿼리가 자주 잠금을 잡는다면:
CREATE INDEX idx_orders_user_status
ON orders (user_id, status);
그리고 가능하면 잠그는 쿼리(FOR UPDATE)는 PK 또는 유니크 키 기반으로 좁게 만드는 게 좋습니다.
6단계: 갭락/넥스트키 락을 이해하고, 범위 조건을 재설계한다
InnoDB 기본 격리수준인 REPEATABLE READ 에서는 팬텀 리드를 막기 위해 넥스트키 락(레코드 락 + 갭락) 을 사용합니다. 이때 범위 조건이 들어가면 “존재하지 않는 구간”까지 락이 걸려서 삽입/갱신이 서로 막히거나 데드락이 생깁니다.
6-1. 전형적인 위험 쿼리
SELECT *
FROM coupons
WHERE user_id = 123
AND expires_at >= NOW()
FOR UPDATE;
expires_at 범위 조건이 인덱스와 맞지 않거나, 범위가 넓으면 갭락이 커질 수 있습니다.
6-2. 해결 방향
- 범위 조건을 “더 좁은 키”로 바꾸기(가능하면 PK/유니크 키로 잠금)
- 필요한 경우만 잠금: 먼저 후보를 읽고, 그 다음 PK로 다시 잠그는 2단계 접근
예시(2단계 잠금):
-- 1) 잠금 없이 후보 PK만 찾기
SELECT id
FROM coupons
WHERE user_id = 123
AND expires_at >= NOW()
ORDER BY expires_at
LIMIT 1;
-- 2) PK로 좁게 잠금
SELECT *
FROM coupons
WHERE id = 555
FOR UPDATE;
6-3. 격리수준 변경은 마지막 카드
READ COMMITTED 로 바꾸면 갭락이 줄어드는 케이스가 있지만, 애플리케이션이 REPEATABLE READ 전제(같은 트랜잭션에서 동일 조회 결과)를 깔고 있다면 버그가 날 수 있습니다. 변경 전 반드시 트랜잭션 단위 테스트와 리그레션이 필요합니다.
7단계: 유니크 키 경합을 “업서트 설계”로 풀어낸다
동일 키로 동시 삽입이 들어오면 다음과 같은 패턴이 데드락을 유발할 수 있습니다.
INSERT ... ON DUPLICATE KEY UPDATEINSERT IGNORE- 먼저
SELECT로 존재 확인 후INSERT(TOCTOU)
7-1. 멱등 키를 명시하고, 충돌을 의도적으로 처리
예: 결제 승인 요청을 request_id 로 멱등 처리.
CREATE TABLE payment_requests (
request_id VARCHAR(64) PRIMARY KEY,
status VARCHAR(20) NOT NULL,
created_at DATETIME NOT NULL
) ENGINE=InnoDB;
INSERT INTO payment_requests (request_id, status, created_at)
VALUES ('req-123', 'RECEIVED', NOW())
ON DUPLICATE KEY UPDATE
status = VALUES(status);
이때도 경합이 심하면 데드락이 날 수 있으니, 애플리케이션 레벨에서 “동일 키 동시 처리”를 줄이는 전략(큐잉, 단일 플라이트, 분산락, 파티셔닝)을 고려합니다.
8단계: 트랜잭션을 짧게 만들고, 잠금 시간을 최소화한다
폭증의 가장 현실적인 원인은 “트랜잭션이 길어진 것”입니다. 배포 이후 다음 변화가 있었는지 확인하세요.
- 트랜잭션 내부에 외부 API 호출이 들어감
- 파일 업로드/다운로드를 트랜잭션 안에서 처리
- 한 트랜잭션에서 수백~수천 행을 갱신
- 로그/감사 테이블까지 함께 갱신
8-1. 트랜잭션 경계 재조정
원칙:
- DB 락이 필요한 구간만 트랜잭션
- 비즈니스적으로 원자성이 꼭 필요한 것만 묶기
- “읽기 → 외부 호출 → 쓰기” 구조라면 외부 호출을 트랜잭션 밖으로 이동
8-2. 배치 업데이트는 청크로
-- 예: 100개씩 잘라서 처리(애플리케이션에서 반복)
UPDATE orders
SET status = 'EXPIRED'
WHERE status = 'PENDING'
AND created_at < NOW() - INTERVAL 1 DAY
ORDER BY id
LIMIT 100;
한 번에 크게 잠그는 대신, 작은 단위로 커밋해 경쟁을 줄입니다.
9단계: 운영 안전장치(재시도, 관측, 한도)를 넣는다
튜닝이 끝나도 데드락은 “0”이 되지 않을 수 있습니다. 따라서 운영에서는 다음 안전장치가 필수입니다.
9-1. 애플리케이션 재시도는 “조건부”로
- 데드락은 보통 재시도에 성공하지만
- 무한 재시도는 더 큰 폭증을 부릅니다
권장:
- 지수 백오프(예: 50ms, 100ms, 200ms)
- 최대 2~3회
- 멱등성이 확보된 요청만 재시도
의사 코드:
for attempt in 1..3:
try:
begin
...
commit
return ok
catch deadlock:
rollback
sleep(backoff)
throw
본문에 -> 같은 문자를 그대로 쓰면 MDX에서 오인될 수 있으니, 화살표는 이런 식으로 텍스트로 표현하거나 인라인 코드로 처리하는 습관이 안전합니다.
9-2. 데드락 메트릭을 “비율”로 본다
절대 건수는 트래픽에 따라 흔들립니다.
- 분당 트랜잭션 수 대비 데드락 비율
- 특정 API 또는 특정 테이블에서의 집중도
- 상위 5개 데드락 쿼리 fingerprint
9-3. 문제 쿼리의 fingerprint를 뽑아 상시 감시
MySQL 8.x라면 performance_schema.events_statements_summary_by_digest 로 쿼리 다이제스트를 집계할 수 있습니다.
SELECT digest_text, count_star, sum_timer_wait
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 20;
여기서 상위 쿼리와 데드락 리포트의 SQL을 매칭해 “어떤 쿼리가 락을 오래 잡는지”를 연결합니다.
실전 체크리스트: 폭증 시 30분 내 확인 순서
SHOW ENGINE INNODB STATUS\G로 최근 데드락 패턴 확인innodb_print_all_deadlocks활성화 후 로그 수집- 데드락 SQL의 공통 테이블/인덱스/조건 추출
EXPLAIN ANALYZE로 인덱스 미스 여부 확인- 잠금 순서 불일치(코드 경로) 확인
- 범위 조건 +
FOR UPDATE조합에서 갭락 의심 - 트랜잭션 길이(외부 호출, 배치) 확인
- 재시도 정책(횟수/백오프/멱등성) 점검
- 핫스팟 키(특정
user_id, 특정 상품) 존재 시 샤딩/큐잉/단일화 검토
마무리: “데드락을 없애기”보다 “폭증을 멈추기”
InnoDB에서 데드락은 완전히 사라지지 않을 수 있습니다. 중요한 건 폭증을 유발하는 구조적 원인을 제거하고, 남은 데드락은 짧은 트랜잭션 + 좁은 잠금 + 안전한 재시도로 흡수하는 것입니다.
특히 (1) 잠금 순서 표준화, (2) 인덱스로 잠금 범위 축소, (3) 범위 잠금 재설계, (4) 트랜잭션 단축 이 네 가지는 대부분의 데드락 폭증을 눈에 띄게 줄입니다. 이후에야 격리수준 조정, 파티셔닝, 큐잉 같은 큰 수술을 검토하는 것이 안전합니다.