beautifulsoups.dev
Published on

MySQL 8.0 InnoDB 데드락 폭증 진단과 잠금·인덱스 튜닝

Authors

서버가 멀쩡히 돌아가던 중 어느 날부터 Deadlock found when trying to get lock 이 폭증하면, 대부분은 “트래픽 증가”가 아니라 잠금 경합을 유발하는 쿼리/인덱스/트랜잭션 패턴이 바뀐 것입니다. MySQL 8.0 InnoDB는 내부적으로 다양한 잠금(레코드/갭/넥스트키)을 사용하고, 실행 계획이 조금만 달라져도 잠금 범위가 커지면서 데드락 빈도가 급증할 수 있습니다.

이 글은 MySQL 8.0 기준으로 데드락을 증상 수집 → 원인 분류 → 인덱스/쿼리/트랜잭션 튜닝 순서로 정리합니다. (참고로, DB 유지보수 이슈를 다른 엔진 관점에서 비교해보면 PostgreSQL VACUUM 안 될 때 bloat 진단·해결도 함께 보면 좋습니다.)

1) 데드락이 “폭증”하는 대표 트리거

실행 계획 변화로 잠금 범위가 커짐

  • 인덱스가 있는데도 통계/카디널리티 변화로 range scan 이 full scan 비슷하게 변함
  • 조인 순서가 바뀌어, 잠금 획득 순서가 뒤틀림
  • ORDER BY/LIMIT 때문에 예상과 다른 인덱스를 선택

격리 수준과 갭/넥스트키 잠금

  • 기본 격리 수준 REPEATABLE READ 에서는 팬텀 방지를 위해 갭/넥스트키 잠금이 발생
  • “한 줄만 업데이트”라고 생각했는데, 실제로는 범위 조건 때문에 구간 전체를 잠금

핫스팟(Hot spot) 행/인덱스

  • 카운터 테이블, 포인트/재고, 마지막 상태 1행, status='READY' 같은 낮은 카디널리티 조건
  • 세컨더리 인덱스의 동일 키 값에 업데이트가 몰리면, 잠금 경합이 급증

트랜잭션이 길어짐

  • 애플리케이션에서 외부 API 호출을 트랜잭션 안에서 수행
  • 배치가 큰 단위로 UPDATE/DELETE 를 오래 잡고 있음

2) 우선 수집: “지금 어떤 데드락이 나는지” 고정밀로 보기

2-1) InnoDB 데드락 로그 확보

가장 먼저 이걸 봐야 합니다.

SHOW ENGINE INNODB STATUS\G

출력에서 LATEST DETECTED DEADLOCK 섹션을 확인합니다.

  • 어떤 트랜잭션이 어떤 인덱스(레코드)에 어떤 잠금을 잡고 있었는지
  • 서로 어떤 순서로 잠금을 잡으려다 교착이 됐는지

운영에서는 로그로 남겨야 추세를 봅니다.

SET PERSIST innodb_print_all_deadlocks = ON;

2-2) Performance Schema로 잠금/대기 관찰

MySQL 8.0에서는 performance_schema 가 실전에서 매우 유용합니다.

-- 현재 잠금 대기(누가 누구를 막는지)
SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id
JOIN information_schema.innodb_trx b ON w.blocking_trx_id  = b.trx_id;

또한 실행 계획이 바뀌었는지 확인하려면:

EXPLAIN ANALYZE
SELECT ...;

EXPLAIN ANALYZE 는 “예상”이 아니라 “실제 실행”에 가까운 정보를 줘서, 잠금 범위를 키우는 원인을 찾는 데 도움이 됩니다.

3) 데드락 패턴 1: 서로 다른 순서로 행을 업데이트

가장 흔한 교착 시나리오입니다.

  • 트랜잭션 A: users 먼저 잠그고 orders 잠금
  • 트랜잭션 B: orders 먼저 잠그고 users 잠금

해결 원칙: 잠금 획득 순서를 통일

애플리케이션 레벨에서 항상 같은 테이블/같은 인덱스 순서로 업데이트하게 만드세요.

예시(의도적으로 순서를 통일):

START TRANSACTION;

-- 1) users 먼저
SELECT id FROM users WHERE id = ? FOR UPDATE;

-- 2) orders 다음
UPDATE orders SET status = 'PAID' WHERE user_id = ? AND id = ?;

COMMIT;

FOR UPDATE 는 “잠금 예약” 역할을 해서, 뒤늦게 업데이트하면서 예상치 못한 순서로 잠금을 잡는 것을 줄입니다.

4) 데드락 패턴 2: 범위 조건 업데이트로 넥스트키 잠금 확대

문제 예시

아래는 얼핏 보면 “조건에 맞는 행만 업데이트”지만, 인덱스가 부적절하면 범위 스캔 과정에서 넥스트키 잠금이 크게 걸립니다.

UPDATE jobs
SET status = 'RUNNING'
WHERE status = 'READY'
ORDER BY created_at
LIMIT 1;

이 패턴은 큐(Queue) 구현에서 자주 등장하고, 동시성에서 데드락/락 경합을 유발합니다.

개선 1) 명확한 키로 2단계 처리

먼저 후보를 “정확히 한 건” 고르고, 그 키로 업데이트합니다.

START TRANSACTION;

SELECT id
FROM jobs
WHERE status = 'READY'
ORDER BY created_at
LIMIT 1
FOR UPDATE;

UPDATE jobs
SET status = 'RUNNING'
WHERE id = ?;

COMMIT;

개선 2) 적절한 복합 인덱스

status 와 created_at 을 같이 타도록 인덱스를 설계합니다.

CREATE INDEX idx_jobs_status_created_at ON jobs(status, created_at, id);

핵심은 “정렬/필터 조건을 인덱스로 밀어 넣어” 스캔 범위를 최소화하는 것입니다.

5) 데드락 패턴 3: 유니크 인덱스 충돌과 동시 INSERT

동일 키로 동시에 INSERT 하거나, INSERT ... ON DUPLICATE KEY UPDATE 를 경쟁적으로 수행하면 데드락이 생길 수 있습니다.

완화 전략

  • 충돌이 잦은 키를 피할 수 있으면 키 설계를 바꿈(예: 전역 시퀀스 대신 샤딩 키)
  • 꼭 필요하면 재시도 로직을 표준화(데드락은 “정상적으로” 발생 가능한 이벤트)

애플리케이션에서 데드락은 재시도가 정답인 경우가 많습니다. 재시도/백오프 패턴은 DB뿐 아니라 전반적인 안정성에 유효하므로, 구현 패턴은 Python 데코레이터로 재시도·백오프 구현 5패턴도 참고할 수 있습니다.

6) 인덱스 튜닝 체크리스트: “잠금 범위를 줄이는 인덱스”

데드락 튜닝에서 인덱스는 단순히 “빠르게 만들기”가 아니라, 잠금 대상(스캔 범위)을 줄이는 장치입니다.

6-1) WHERE 조건을 커버하는 인덱스가 있는가

  • 조건 컬럼이 인덱스 선두에 있어야 합니다.
  • WHERE a = ? AND b = ? 인데 인덱스가 (b, a) 면 기대대로 안 타거나 범위가 커질 수 있습니다.

6-2) 낮은 카디널리티 컬럼 단독 인덱스는 위험

예: status 가 READY/RUNNING/DONE 3개뿐이면 status 단독 인덱스는 “거의 전체 범위”를 잠글 가능성이 큽니다.

  • (status, created_at, id) 처럼 선택도를 올리는 컬럼을 뒤에 결합

6-3) 불필요한 갭 잠금 유발 쿼리 점검

  • 범위 조건 BETWEEN, >=, LIKE 'prefix%' 는 넥스트키 잠금 범위를 키울 수 있습니다.
  • 특히 동시 업데이트가 많다면 “범위 업데이트”를 “PK 기반 단건 업데이트”로 바꾸는 것이 강력합니다.

7) 트랜잭션/쿼리 레벨 튜닝: 데드락을 줄이는 운영 규칙

7-1) 트랜잭션을 짧게

  • 트랜잭션 안에서 파일 IO/외부 API 호출 금지
  • 대량 갱신은 배치 크기(청크)를 줄여 커밋을 자주
-- 예: 1만 건씩 끊어서 처리(애플리케이션 루프)
UPDATE events
SET processed = 1
WHERE processed = 0
ORDER BY id
LIMIT 10000;

7-2) 필요하면 격리 수준을 구간별로 조정

기본 REPEATABLE READ 가 항상 최선은 아닙니다.

  • 팬텀 보호가 필요 없는 단순 조회/큐 폴링은 READ COMMITTED 가 락 경합을 줄일 수 있음
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

주의: 격리 수준 변경은 정합성 요구사항(팬텀 허용 여부)과 함께 검토해야 합니다.

7-3) SELECT ... FOR UPDATE 를 “정확히” 사용

  • 잠그려는 행을 PK 또는 유니크 키로 좁힐수록 안전
  • 인덱스를 못 타는 FOR UPDATE 는 대형 잠금을 만들 수 있어 더 위험

8) 데드락을 “없애기”보다 “통제”하기

InnoDB에서 데드락은 완전히 0으로 만들기 어렵습니다. 중요한 건:

  1. 발생 빈도와 영향도를 낮추고
  2. 재시도로 사용자 영향을 제거하며
  3. 원인 쿼리를 지속적으로 줄여나가는 것입니다.

운영 체크리스트를 권장합니다.

  • innodb_print_all_deadlocks 로 로그 수집
  • 상위 데드락 쿼리 Top N 정리
  • 각 쿼리에 대해 EXPLAIN ANALYZE + 인덱스/쿼리 형태 변경
  • 트랜잭션 길이(평균/최대) 모니터링
  • 애플리케이션에서 에러 코드 ER_LOCK_DEADLOCK 재시도(지수 백오프)

9) 실전 예시: “상태 전이” 테이블에서 데드락 줄이기

상태 전이 워크플로우에서 흔한 패턴:

UPDATE task
SET status = 'DONE'
WHERE user_id = ? AND status = 'RUNNING';

여기서 user_id 가 같고 status 가 낮은 카디널리티면, 동시에 여러 트랜잭션이 같은 범위를 건드리며 교착이 발생합니다.

개선안

  • 단건을 특정하는 키를 먼저 확보
  • 인덱스는 (user_id, status, id) 처럼 잠금 범위를 줄이는 방향
CREATE INDEX idx_task_user_status_id ON task(user_id, status, id);

START TRANSACTION;

SELECT id
FROM task
WHERE user_id = ? AND status = 'RUNNING'
ORDER BY id
LIMIT 1
FOR UPDATE;

UPDATE task
SET status = 'DONE'
WHERE id = ?;

COMMIT;

10) 마무리: 데드락 폭증 시 가장 빠른 5단계

  1. SHOW ENGINE INNODB STATUS\G 로 최신 데드락을 확보
  2. 데드락에 등장하는 테이블/인덱스/쿼리를 “한 덩어리”로 묶어 Top 원인 선정
  3. EXPLAIN ANALYZE 로 실행 계획과 스캔 범위 확인
  4. 인덱스/쿼리 변경으로 “범위 잠금”을 “단건 잠금”으로 축소
  5. 애플리케이션에서 ER_LOCK_DEADLOCK 재시도 + 트랜잭션 짧게

이 흐름대로 가면, 데드락을 단순히 운에 맡기는 게 아니라 재현 가능한 성능/동시성 문제로 바꿔서 다룰 수 있습니다.

Discuss on Twitter • View on GitHub