beautifulsoups.dev
Published on

MySQL InnoDB 데드락 폭증 대응 - 인덱스·격리수준 튜닝

Authors

서비스가 성장하면서 트래픽이 늘면, 어느 날부터 Deadlock found when trying to get lock; try restarting transaction 로그가 폭증하는 순간이 옵니다. 데드락은 단순히 “동시성 문제”가 아니라, 인덱스 설계, 잠금 범위(레코드락·갭락·넥스트키락), 트랜잭션 격리수준, 쿼리 패턴(일괄 갱신, 정렬, 범위 조건) 이 합쳐져 만들어지는 결과입니다.

이 글은 InnoDB 데드락을 “재시도 로직으로 덮기” 전에, 왜 잠금이 커졌는지를 관찰하고 인덱스와 격리수준을 조정해 데드락 빈도를 구조적으로 낮추는 흐름으로 설명합니다.

(분산 환경에서 재시도 폭주가 장애로 번지는 패턴은 DB에서도 그대로 재현됩니다. 재시도 설계 관점은 gRPC MSA에서 데드라인·리트라이 폭주 막는 법도 함께 참고하면 좋습니다.)

1) 데드락 폭증의 전형적인 징후

다음 중 하나라도 보이면 “인덱스와 잠금 범위가 커졌을 가능성”이 큽니다.

  • 특정 API 배포 이후 데드락 급증 (쿼리 변경, 조건 변경, 정렬 추가)
  • 배치 작업 시간대에만 데드락 폭증 (대량 갱신·삭제)
  • UPDATE ... WHERE non_indexed_column = ... 형태가 섞임
  • SELECT ... FOR UPDATE 가 범위 조건을 사용
  • 격리수준이 REPEATABLE READ 이고, 범위 검색이 많음

데드락은 “서로가 서로의 락을 기다리는 순환”이므로, 잠금 획득 순서가 엇갈릴 때 특히 잘 발생합니다. 잠금 획득 순서는 보통 다음에 의해 결정됩니다.

  • 인덱스 접근 경로(어떤 인덱스를 타는지)
  • 범위 스캔 순서(오름차순·내림차순)
  • 조인 순서(옵티마이저가 선택)
  • 동일 트랜잭션 내에서 테이블을 갱신하는 순서

2) 먼저 “증거”를 모으는 방법

2.1 SHOW ENGINE INNODB STATUS 로 데드락 원문 확인

데드락이 발생한 직후 아래를 실행하면, InnoDB가 기록한 최신 데드락 1건의 상세를 볼 수 있습니다.

SHOW ENGINE INNODB STATUS\G

LATEST DETECTED DEADLOCK 섹션에서 확인할 핵심은 다음입니다.

  • 각 트랜잭션이 실행한 SQL
  • 어떤 인덱스 레코드(또는 갭)를 잠그려 했는지
  • 락 타입이 X 인지 S 인지
  • “기다리는 락”과 “이미 보유한 락”의 조합

여기서 SQL이 애플리케이션 로그의 쿼리와 매칭되면, 어떤 API 조합이 충돌하는지가 드러납니다.

2.2 Performance Schema로 “데드락 이력”을 남기기

SHOW ENGINE INNODB STATUS 는 최신 1건만 보여주므로, 폭증 상황에서는 부족합니다. MySQL 8.x라면 Performance Schema를 통해 잠금 대기를 관찰할 수 있습니다.

-- 어떤 락 대기가 많은지
SELECT *
FROM performance_schema.data_lock_waits
LIMIT 50;

-- 현재 잡혀 있는 락
SELECT *
FROM performance_schema.data_locks
WHERE object_schema = DATABASE()
LIMIT 50;

운영에서는 위 결과를 그대로 수집하기보다, 주기적으로 샘플링하거나 특정 테이블에 대해서만 필터링하는 식으로 부하를 관리하는 편이 안전합니다.

2.3 애플리케이션에서 “트랜잭션 경계”를 로그로 남기기

데드락 분석이 어려운 이유는 SQL 한 줄이 아니라 “트랜잭션 묶음”이 문제인 경우가 많기 때문입니다. 다음을 로그에 남기면 원인 추적이 빨라집니다.

  • 트랜잭션 시작 시점
  • 트랜잭션 내 쿼리 목록(요약)
  • 커밋/롤백까지 걸린 시간

3) InnoDB 잠금의 핵심: 레코드락 + 갭락 + 넥스트키락

InnoDB에서 REPEATABLE READ 는 팬텀을 막기 위해 넥스트키락(next-key lock) 을 적극적으로 사용합니다. 넥스트키락은 “레코드락 + 갭락” 조합이라, 범위 검색에서 잠금 범위가 커지고 충돌이 늘어납니다.

특히 다음 패턴이 위험합니다.

  • 범위 조건: WHERE created_at BETWEEN ... / WHERE id > ...
  • 정렬 + 잠금: ORDER BY ... FOR UPDATE
  • 인덱스가 애매해서 넓게 스캔하는 경우

즉, 데드락을 줄이려면 스캔 범위를 줄이는 인덱스 와 락을 잡는 쿼리의 형태 를 먼저 다듬는 게 정석입니다.

4) 인덱스로 데드락을 줄이는 실전 포인트

4.1 “조건 컬럼 인덱스”는 기본, “복합 인덱스”가 승부처

다음 쿼리를 예로 듭니다.

UPDATE orders
SET status = 'PAID'
WHERE user_id = 123
  AND status = 'PENDING'
  AND created_at >= '2026-02-01';

user_id 만 인덱스가 있으면, 해당 사용자의 주문을 넓게 훑으며 status 와 created_at 를 필터링합니다. 이 과정에서 잠금 후보가 많아지고, 다른 트랜잭션과 충돌 확률이 커집니다.

해결은 쿼리의 필터링 순서에 맞춘 복합 인덱스입니다.

CREATE INDEX idx_orders_user_status_created
ON orders (user_id, status, created_at);

이렇게 하면 InnoDB가 훨씬 좁은 범위만 스캔하고, 결과적으로 락 범위도 줄어듭니다.

4.2 SELECT ... FOR UPDATE 는 “인덱스를 타는지”가 전부

SELECT *
FROM coupons
WHERE code = 'ABC123'
FOR UPDATE;

여기서 code 가 유니크 인덱스라면 잠금은 매우 국소적입니다. 하지만 code 가 인덱스가 아니라면 테이블 스캔 중 다수 레코드에 락이 걸릴 수 있고, 데드락의 도화선이 됩니다.

ALTER TABLE coupons
ADD UNIQUE KEY uk_coupons_code (code);

4.3 “같은 테이블을 여러 번 갱신”할 때는 잠금 순서를 강제

두 트랜잭션이 같은 테이블의 여러 행을 갱신할 때, 서로 다른 순서로 락을 잡으면 데드락이 쉽게 납니다.

  • 트랜잭션 A: id=10 갱신 후 id=20 갱신
  • 트랜잭션 B: id=20 갱신 후 id=10 갱신

해결은 애플리케이션에서 항상 동일한 정렬 기준으로 처리하는 것입니다.

-- 항상 id 오름차순으로 잠금 획득
SELECT id
FROM orders
WHERE id IN (20, 10)
ORDER BY id
FOR UPDATE;

UPDATE orders SET status = 'X' WHERE id = 10;
UPDATE orders SET status = 'X' WHERE id = 20;

이 패턴은 “락을 먼저 잡고 업데이트”라서 안전하지만, 트랜잭션 시간이 길어지지 않게 주의해야 합니다.

4.4 세컨더리 인덱스 업데이트가 “의외로” 데드락을 만든다

InnoDB는 보조 인덱스도 함께 갱신합니다. 즉, 업데이트가 다음을 유발할 수 있습니다.

  • 클러스터드 인덱스(프라이머리 키) 레코드 락
  • 변경되는 보조 인덱스 엔트리 락

특정 컬럼(예: status)에 인덱스가 있고, 그 컬럼을 대량 업데이트하면 보조 인덱스 엔트리 충돌이 늘어 데드락이 증가할 수 있습니다. 이때는 다음을 검토합니다.

  • 해당 인덱스가 정말 필요한지
  • 인덱스를 유지하되 업데이트를 작은 배치로 쪼갤지
  • “상태 전이”를 별도 테이블로 분리할지

5) 격리수준 튜닝: REPEATABLE READ 에서 READ COMMITTED 로

5.1 왜 격리수준이 데드락에 영향을 주나

MySQL InnoDB 기본 격리수준은 REPEATABLE READ 입니다. 이 모드에서 범위 조건 + 잠금 쿼리는 넥스트키락을 통해 팬텀을 막으려 하므로, 갭락이 늘어나고 충돌이 증가합니다.

반면 READ COMMITTED 는 일반적으로 갭락 사용이 줄어(특히 단순 조회와 조합에서), 데드락과 락 대기를 완화하는 경우가 많습니다.

5.2 전역 변경 vs 세션 변경

운영에서 “전역으로 격리수준 변경”은 영향 범위가 큽니다. 우선은 특정 워크로드(배치, 특정 API)에서 세션 단위로 실험하는 접근이 안전합니다.

-- 세션 단위
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- ...
COMMIT;

전역 변경은 충분한 검증 후에 고려합니다.

-- 전역(다음 접속부터)
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

5.3 격리수준을 낮추기 전에 체크할 것

READ COMMITTED 로 바꾸면 데드락이 줄 수 있지만, 다음과 같은 애플리케이션 가정이 깨질 수 있습니다.

  • 같은 트랜잭션 내 반복 조회 결과가 달라질 수 있음
  • 특정 로직이 “반복 조회 불변”에 의존하는지 확인 필요

실무에서는 보통 다음 우선순위를 권합니다.

  1. 인덱스와 쿼리로 스캔/락 범위를 먼저 줄인다
  2. 그래도 데드락이 높으면, 충돌이 심한 워크로드에 한해 READ COMMITTED 를 검토한다

6) 쿼리/트랜잭션 구조로 푸는 방법

6.1 트랜잭션을 짧게: “검증 로직”을 트랜잭션 밖으로

데드락은 락을 오래 들고 있을수록 확률이 올라갑니다. 흔한 실수는 트랜잭션 안에서 다음을 하는 것입니다.

  • 외부 API 호출
  • 파일/네트워크 작업
  • 복잡한 비즈니스 검증

원칙은 간단합니다.

  • 트랜잭션은 “DB 읽기/쓰기”만 포함
  • 외부 의존성은 트랜잭션 밖에서 처리

6.2 대량 업데이트는 쪼개서 커밋

-- 나쁜 예: 한 번에 수십만 행 업데이트
UPDATE events
SET processed = 1
WHERE processed = 0
  AND created_at < '2026-02-01';

위 쿼리는 잠금 범위를 크게 만들고, undo/redo 부담도 키웁니다. 다음처럼 “PK 범위로 쪼개기”가 일반적으로 안정적입니다.

-- 예시: PK 범위로 배치 처리
START TRANSACTION;
UPDATE events
SET processed = 1
WHERE processed = 0
  AND id BETWEEN 1 AND 10000;
COMMIT;

실제 구현에서는 마지막 처리 id 를 저장하고 반복합니다.

6.3 INSERT ... ON DUPLICATE KEY UPDATE 충돌 완화

업서트는 편하지만, 동시성이 높을수록 같은 유니크 키에서 경합이 발생합니다. 가능하면 “선점 잠금” 또는 “키 공간 분산”을 고려합니다.

  • 키를 더 세분화(샤딩 키 포함)
  • 테이블을 워크로드별로 분리
  • 충돌이 잦은 키는 큐로 직렬화

재시도는 필요하지만, 무제한 재시도는 DB를 더 압박합니다. 이 관점은 OpenAI 429/RateLimitError 재시도·백오프·큐 설계에서 설명한 “백오프와 큐잉” 원칙을 DB 재시도에도 그대로 적용할 수 있습니다.

7) 운영 파라미터: 데드락을 “줄이진” 못하지만 피해를 줄인다

튜닝이 구조적 원인 제거라면, 파라미터는 피해 통제입니다.

7.1 innodb_lock_wait_timeout

데드락은 InnoDB가 감지해 한 트랜잭션을 희생시키지만, 데드락이 아닌 단순 락 대기는 타임아웃까지 기다릴 수 있습니다.

  • 너무 크면: 대기 스레드가 쌓여 장애 전파
  • 너무 작으면: 정상 트랜잭션도 타임아웃 증가

워크로드에 맞는 값을 찾되, 애플리케이션의 재시도 정책과 함께 설계해야 합니다.

7.2 데드락 로깅

MySQL 8.0에서는 log_error_verbosity 와 에러 로그 설정을 통해 데드락 단서를 확보할 수 있습니다. 또한 주기적으로 SHOW ENGINE INNODB STATUS 를 수집해 아카이빙하는 방식도 많이 씁니다.

8) 재현 가능한 “데드락 최소 예제”로 팀 합의 만들기

데드락은 팀 내에서 “운이 나빴다”로 취급되기 쉽습니다. 아래는 재현 가능한 간단한 패턴입니다.

테이블:

CREATE TABLE wallet (
  user_id BIGINT PRIMARY KEY,
  balance BIGINT NOT NULL
) ENGINE=InnoDB;

INSERT INTO wallet(user_id, balance) VALUES (1, 1000), (2, 1000);

세션 A:

START TRANSACTION;
UPDATE wallet SET balance = balance - 100 WHERE user_id = 1;
-- 여기서 잠시 대기
UPDATE wallet SET balance = balance + 100 WHERE user_id = 2;
COMMIT;

세션 B:

START TRANSACTION;
UPDATE wallet SET balance = balance - 100 WHERE user_id = 2;
-- 여기서 잠시 대기
UPDATE wallet SET balance = balance + 100 WHERE user_id = 1;
COMMIT;

두 세션이 서로 반대 순서로 락을 잡으면 데드락이 납니다. 해결은 “항상 작은 user_id 먼저” 같은 일관된 잠금 순서입니다.

9) 점검 체크리스트: 폭증 상황에서의 우선순위

  1. SHOW ENGINE INNODB STATUS\G 로 실제 충돌 SQL 확보
  2. 충돌 SQL의 EXPLAIN 확인: 인덱스를 타는지, 스캔이 넓은지
  3. 조건에 맞는 복합 인덱스 추가 또는 쿼리 재작성
  4. 동일 테이블 다건 갱신은 잠금 순서 고정(ORDER BY pk + FOR UPDATE)
  5. 대량 업데이트/배치는 PK 범위로 쪼개기
  6. 특정 워크로드에서만 READ COMMITTED 세션 적용 실험
  7. 애플리케이션 재시도는 지수 백오프 + 지터 + 최대 횟수 제한

마무리

InnoDB 데드락은 “없애는 것”보다 “확률을 낮추는 것”에 가깝습니다. 하지만 폭증하는 데드락은 대부분 우연이 아니라 스캔 범위가 커졌거나, 잠금 순서가 뒤섞였거나, 격리수준과 범위 잠금이 맞물린 결과입니다.

가장 효과가 큰 순서는 대체로 이렇습니다.

  • 인덱스로 스캔 범위 축소
  • 트랜잭션을 짧게, 잠금 순서를 일관되게
  • 필요 시 READ COMMITTED 로 갭락 영향 완화

이 3가지를 체계적으로 적용하면, “재시도에 의존하는 운영”에서 “예측 가능한 동시성”으로 한 단계 올라갈 수 있습니다.

Discuss on Twitter • View on GitHub