beautifulsoups.dev
Published on

MySQL 8.0 InnoDB 데드락 폭증 - gap lock·격리수준 튜닝

Authors

운영 중 MySQL 8.0로 업그레이드한 뒤, 혹은 트래픽 패턴이 바뀐 뒤 갑자기 InnoDB 데드락이 폭증하는 경우가 있습니다. 특히 REPEATABLE READ 기본 격리수준에서 gap lock(정확히는 next-key lock의 gap 부분)이 예상보다 넓게 걸리면서, 서로 다른 트랜잭션이 “안 겹칠 것 같은” 레코드까지 잠가 데드락이 터지는 패턴이 흔합니다.

이 글은 다음을 목표로 합니다.

  • 데드락을 “재시도하면 되지” 수준에서 끝내지 않고, 왜 잠금이 넓게 잡히는지를 구조적으로 이해
  • MySQL 8.0에서 확인해야 할 데드락 관찰 포인트와 재현 방법
  • gap lock과 격리수준, 인덱스, 쿼리 패턴을 함께 조정하는 실전 튜닝 체크리스트

참고로, 데드락을 무조건 제거하려 하기보다 “피할 수 없는 경쟁”이라면 재시도·백오프 패턴까지 포함해 설계하는 게 안전합니다. 트랜잭션 재시도 전략은 OpenAI API 429 재시도·백오프 패턴 실전 가이드의 백오프 설계 아이디어도 유사하게 적용할 수 있습니다.

데드락이 폭증하는 전형적 징후

  • 배치/정산/스케줄러가 도는 시간대에만 데드락이 급증
  • UPDATE ... WHERE status = 'READY' ORDER BY id LIMIT N 같은 “작업 큐” 쿼리에서 집중 발생
  • 범위 조건(BETWEEN, >=, LIKE 'prefix%')이 포함된 SELECT ... FOR UPDATE 혹은 UPDATE에서 발생
  • 유니크 키가 아닌 보조 인덱스를 타고 잠금을 잡는 쿼리가 많음

이때 핵심은 “레코드만 잠그는 줄 알았는데, 레코드 사이 구간(gap) 까지 잠그는 상황”입니다.

InnoDB 잠금: record lock, gap lock, next-key lock

InnoDB가 인덱스를 기준으로 잠금을 잡을 때, 크게 다음이 있습니다.

  • Record lock: 인덱스의 특정 레코드에 대한 잠금
  • Gap lock: 인덱스 레코드 사이의 “빈 구간”에 대한 잠금
  • Next-key lock: record lock + gap lock 조합(레코드와 그 앞의 gap을 함께 잠금)

REPEATABLE READ에서 팬텀 리드를 막기 위해, InnoDB는 범위 조건에서 next-key lock을 적극적으로 사용합니다. 문제는 이 next-key lock이 “내가 수정하려는 행”보다 더 넓은 범위를 잠가서, 다른 트랜잭션의 진행을 막고 데드락을 만들 수 있다는 점입니다.

왜 gap lock이 데드락으로 이어지나

두 트랜잭션 T1, T2가 서로 다른 레코드를 수정하는데도, 둘 다 같은 인덱스 범위의 next-key lock을 잡으면 다음처럼 교착이 생깁니다.

  • T1이 인덱스 범위 A의 gap을 잠금
  • T2가 인덱스 범위 B의 gap을 잠금
  • 이후 각 트랜잭션이 상대가 잡은 gap/레코드로 확장하려다 서로 대기

특히 “큐에서 N개 가져오기” 패턴, “상태 전이 업데이트” 패턴에서 자주 나타납니다.

먼저 관찰: 데드락 로그를 제대로 남겨라

MySQL은 데드락이 발생하면 “가장 최근 데드락 1건”을 SHOW ENGINE INNODB STATUS에 남깁니다. 폭증 상황에서는 이 정보가 금방 덮이므로, 로그로 남길 장치가 필요합니다.

1) 즉시 확인

SHOW ENGINE INNODB STATUS\G

출력에서 LATEST DETECTED DEADLOCK 섹션을 확인합니다. 여기서 중요한 건 다음입니다.

  • 어떤 쿼리가 실행 중이었는지
  • 어떤 인덱스 레코드(혹은 gap)를 잡고 있었는지
  • 잠금 모드(X, S, X locks rec but not gap 등) 표현

2) InnoDB deadlock 로그 활성화

SET PERSIST innodb_print_all_deadlocks = ON;

이 설정은 모든 데드락을 에러 로그로 출력합니다. 운영에서 일시적으로 켜고, 원인 분석 후 끄는 식으로 사용합니다.

3) Performance Schema로 락 대기 추적

MySQL 8.0에서는 performance_schema를 통해 락 대기와 관련 테이블을 더 체계적으로 볼 수 있습니다.

-- 어떤 스레드가 어떤 락을 기다리는지
SELECT *
FROM performance_schema.data_lock_waits;

-- 현재 잡혀있는 락(인덱스/레코드 단위)
SELECT *
FROM performance_schema.data_locks;

이 정보는 “어떤 인덱스에서 잠금이 넓게 잡히는지”를 찾는 데 유용합니다.

데드락 재현: 큐 테이블에서 가장 흔한 패턴

예를 들어 작업 큐 테이블이 있고, 여러 워커가 동시에 READY 작업을 가져가 처리한다고 합시다.

CREATE TABLE job_queue (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  status VARCHAR(16) NOT NULL,
  scheduled_at DATETIME NOT NULL,
  payload JSON,
  KEY idx_status_scheduled (status, scheduled_at, id)
) ENGINE=InnoDB;

워커가 다음처럼 처리하는 경우가 많습니다.

START TRANSACTION;

SELECT id
FROM job_queue
WHERE status = 'READY'
  AND scheduled_at <= NOW()
ORDER BY scheduled_at, id
LIMIT 10
FOR UPDATE;

UPDATE job_queue
SET status = 'RUNNING'
WHERE id IN ( ... );

COMMIT;

이때 FOR UPDATE가 범위 조건 + 정렬 + LIMIT과 결합되면, 인덱스 범위를 따라 next-key lock이 걸리면서 워커 간 충돌이 커질 수 있습니다.

튜닝 1: 격리수준을 READ COMMITTED로 낮추기

가장 강력한 레버 중 하나는 세션 또는 전역 격리수준을 READ COMMITTED로 바꾸는 것입니다. MySQL(InnoDB)은 READ COMMITTED에서 gap lock 사용이 크게 줄어드는 케이스가 많습니다(특히 단순 SELECT ... FOR UPDATE 범위에서).

세션 단위 적용(권장: 점진적)

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

애플리케이션에서는 커넥션 풀 단위로 세션 변수를 설정하거나, 특정 트랜잭션 시작 전에만 설정하는 방식으로 롤아웃할 수 있습니다.

주의점

  • READ COMMITTED는 같은 트랜잭션 내에서 동일 쿼리를 두 번 실행했을 때 결과가 달라질 수 있습니다(비반복 읽기).
  • “정확히 같은 스냅샷을 유지해야 하는” 도메인(정산, 리포트 등)은 오히려 문제가 될 수 있습니다.

즉, 격리수준 조정은 업무 일관성 요구사항과 함께 검토해야 합니다.

튜닝 2: 인덱스를 “잠금이 덜 넓게” 잡히게 설계하기

gap lock이 넓어지는 이유 중 하나는 “조건을 잘 못 타서” 불필요하게 긴 인덱스 범위를 스캔하기 때문입니다.

1) WHERE 절 선두 컬럼과 ORDER BY를 인덱스에 맞추기

위 큐 예시에서 다음은 좋습니다.

  • WHERE status = ? AND scheduled_at <= ?
  • ORDER BY scheduled_at, id

따라서 (status, scheduled_at, id) 인덱스는 합리적입니다. 하지만 실제 쿼리가 ORDER BY id로 되어 있거나, scheduled_at 조건이 빠져 있으면 인덱스 스캔 범위가 커질 수 있습니다.

EXPLAIN ANALYZE
SELECT id
FROM job_queue
WHERE status = 'READY'
ORDER BY id
LIMIT 10
FOR UPDATE;

이런 쿼리는 status만으로 범위를 넓게 잡고 id 정렬을 위해 추가 작업을 하면서 잠금 충돌이 커질 수 있습니다.

2) “상태” 컬럼만으로 잡지 말고, 선택도를 올리기

status='READY'가 전체의 30%라면, 사실상 큰 범위를 잠그게 됩니다. 다음처럼 워커 샤딩 키(예: partition_key)를 넣어 선택도를 높이는 방법이 있습니다.

ALTER TABLE job_queue
  ADD COLUMN shard INT NOT NULL DEFAULT 0,
  ADD KEY idx_ready_shard (status, shard, scheduled_at, id);

그리고 워커는 shard를 나눠 처리합니다.

SELECT id
FROM job_queue
WHERE status = 'READY'
  AND shard = 3
  AND scheduled_at <= NOW()
ORDER BY scheduled_at, id
LIMIT 10
FOR UPDATE;

잠금 경쟁이 구조적으로 줄어듭니다.

튜닝 3: SKIP LOCKED로 “기다리지 말고 건너뛰기”

MySQL 8.0에서는 SELECT ... FOR UPDATE SKIP LOCKED가 가능합니다. 큐 처리에서 매우 유용합니다.

START TRANSACTION;

SELECT id
FROM job_queue
WHERE status = 'READY'
  AND scheduled_at <= NOW()
ORDER BY scheduled_at, id
LIMIT 10
FOR UPDATE SKIP LOCKED;

-- 가져온 id만 업데이트
UPDATE job_queue
SET status = 'RUNNING'
WHERE id IN ( ... );

COMMIT;

이 방식은 “이미 누가 잡은 작업이면 기다리지 않고 다음 작업을 가져오는” 전략이라 락 대기와 데드락이 크게 줄어듭니다.

주의점

  • 공정성(fairness)이 약해질 수 있습니다. 특정 작업이 계속 잠겨 있으면 뒤로 밀릴 수 있습니다.
  • LIMIT와 결합 시 처리량은 좋아지지만, 모니터링 없이 쓰면 “처리 누락처럼 보이는 지연”이 생길 수 있습니다.

튜닝 4: 트랜잭션을 짧게, 잠금 순서를 고정하기

데드락은 결국 “서로 다른 순서로 자원을 잡을 때” 발생합니다. 완벽히 없애기 어렵지만 확률을 줄일 수 있습니다.

  • 네트워크 호출, 외부 API 호출, 파일 I/O를 트랜잭션 밖으로 이동
  • 여러 테이블을 갱신한다면 항상 같은 순서로 갱신
  • 필요한 컬럼만 갱신하고, 불필요한 SELECT ... FOR UPDATE를 제거

애플리케이션에서 동시성(스레드/가상 스레드)을 늘린 뒤 데드락과 락 대기가 같이 늘었다면, DB 커넥션과 트랜잭션 범위도 함께 재점검해야 합니다. 비슷한 맥락으로 Spring Boot 3 가상스레드로 DB 커넥션 고갈 막기도 같이 참고하면 좋습니다.

튜닝 5: “원자적 가져오기”로 쿼리 패턴 바꾸기

SELECT ... FOR UPDATE 후 UPDATE ... WHERE id IN (...)는 흔하지만, 두 단계 사이에 락/스캔이 복잡해질 수 있습니다. 가능하면 한 번의 UPDATE로 가져오고, 가져온 행을 확인하는 방식도 고려합니다.

예시(개념):

START TRANSACTION;

UPDATE job_queue
SET status = 'RUNNING'
WHERE status = 'READY'
  AND scheduled_at <= NOW()
ORDER BY scheduled_at, id
LIMIT 10;

-- 방금 잡은 작업을 조회(예: 워커 식별자를 함께 기록해두면 안전)
SELECT id, payload
FROM job_queue
WHERE status = 'RUNNING'
  AND /* worker_id = ? */ 1=1
ORDER BY scheduled_at, id;

COMMIT;

실무에서는 worker_id, locked_at 같은 컬럼을 두고, UPDATE 시점에 함께 기록한 뒤 그 키로 재조회하는 패턴이 안전합니다.

“격리수준만 바꾸면 끝”이 아닌 이유

READ COMMITTED로 바꾸면 gap lock이 줄어드는 경우가 많지만, 다음 상황에서는 여전히 데드락이 날 수 있습니다.

  • 같은 레코드를 서로 다른 순서로 업데이트
  • 유니크 키 충돌을 유발하는 동시 INSERT/UPDATE
  • 보조 인덱스 업데이트가 많은 테이블(인덱스가 많을수록 잠금/래치 경합도 증가)

그래서 결론은 보통 “격리수준 조정 + 인덱스/쿼리 개선 + 트랜잭션 단축 + 재시도 전략”의 조합입니다.

재시도는 무작정 즉시 재시도하면 오히려 폭풍 재시도로 더 큰 경합을 만들 수 있으니, 지수 백오프와 지터를 권장합니다. 백오프 설계 감각은 OpenAI API 429 재시도·백오프 패턴 실전 가이드를 응용해도 좋습니다.

실전 체크리스트

1) 데드락이 발생한 쿼리의 공통점 찾기

  • 특정 테이블/인덱스에 집중되는가
  • 범위 조건이 있는가
  • FOR UPDATE 혹은 UPDATE ... WHERE ...에서 풀스캔/넓은 스캔이 있는가

EXPLAIN ANALYZE로 실제 스캔 범위와 비용을 확인합니다.

EXPLAIN ANALYZE
UPDATE job_queue
SET status = 'RUNNING'
WHERE status = 'READY'
  AND scheduled_at <= NOW();

2) 인덱스가 “조건 + 정렬”을 제대로 커버하는가

  • WHERE 선두 컬럼이 인덱스 선두와 일치하는가
  • ORDER BY가 인덱스 순서를 타는가
  • 선택도가 낮은 컬럼만으로 범위를 잡고 있지 않은가

3) 격리수준 변경을 검토하되, 도메인 요구사항을 먼저 확인

  • 큐/작업 처리: READ COMMITTED + SKIP LOCKED가 대개 유리
  • 정합성 강한 트랜잭션: 유지 필요 시 쿼리/인덱스로 해결

4) 트랜잭션 범위를 줄이고, 잠금 순서를 고정

  • 같은 테이블/레코드를 업데이트하는 순서를 통일
  • 트랜잭션 내 불필요한 로직 제거

5) 애플리케이션 레벨 재시도는 “백오프 포함”으로

  • 즉시 재시도 금지(경합 악화)
  • 지수 백오프 + 랜덤 지터
  • 재시도 횟수 제한 및 관측 지표(성공률, 평균 재시도 횟수)

마무리

MySQL 8.0 InnoDB 데드락 폭증은 많은 경우 “버그”라기보다, next-key lock과 gap lock이 만드는 잠금 범위가 트래픽/쿼리 패턴과 충돌하면서 나타납니다. 해결은 한 가지 버튼이 아니라 다음의 조합으로 접근하는 게 가장 재현 가능하고 안전합니다.

  • 데드락 로그와 performance_schema로 원인을 구조적으로 확인
  • 인덱스/쿼리로 스캔 범위와 잠금 범위를 축소
  • 큐 처리라면 SKIP LOCKED 같은 동시성 친화 기능 사용
  • 필요 시 READ COMMITTED로 조정하되, 일관성 요구사항을 검증
  • 마지막으로 백오프 기반 재시도로 “피할 수 없는 경쟁”을 흡수

데드락이 늘어난 시점의 변경 사항(업그레이드, 인덱스 변경, 워커 수 증가, 트랜잭션 로직 변경)을 타임라인으로 정리한 뒤, 위 체크리스트를 적용하면 대부분의 폭증 케이스는 안정화할 수 있습니다.

Discuss on Twitter • View on GitHub