- Published on
MySQL InnoDB 데드락 폭증? 인덱스·격리수준 튜닝
- Authors
- Name
- 스타차일드
- https://x.com/ETFBITX
서버가 멀쩡하던 어느 날부터 Deadlock found when trying to get lock 이 폭증하면, 대부분은 “락이 많아졌다”가 아니라 락을 잡는 순서와 범위가 바뀌었거나, 불필요하게 넓은 범위를 잠그게 된 것이 원인입니다. 특히 InnoDB는 레코드 락뿐 아니라 넥스트키 락(next-key lock) 과 갭 락(gap lock) 이 결합되면서, 인덱스 설계와 격리수준에 따라 데드락 민감도가 크게 달라집니다.
이 글은 데드락을 “없애는” 요령이 아니라, 폭증을 안정적으로 낮추는 방향으로 인덱스·쿼리·격리수준을 튜닝하는 실전 체크리스트를 제공합니다.
관련해서 분산 환경에서 타임아웃과 데드락이 서로 증폭하는 케이스도 많습니다. 서비스 단에서는 데드라인 전파도 같이 점검해두면 좋습니다: gRPC 데드라인 전파 실패, 원인과 진단법
1) InnoDB 데드락의 본질: “순서”와 “범위”
데드락은 대개 아래 두 축에서 만들어집니다.
락 획득 순서가 트랜잭션마다 다름
- 트랜잭션 A: 테이블 X의 레코드 1 잠금 후 레코드 2 잠금
- 트랜잭션 B: 같은 두 레코드를 역순으로 잠금
락 범위가 필요 이상으로 넓어짐
- 인덱스를 못 타서 풀 스캔하면서 넓은 범위에 넥스트키 락이 걸림
SELECT ... FOR UPDATE가 범위 조건에서 갭 락까지 포함해 잠금
폭증의 트리거는 보통 다음 중 하나입니다.
- 신규 기능 배포로 쿼리 패턴 변경(특히
ORDER BY,LIMIT, 범위 조건 추가) - 인덱스 변경/삭제로 실행 계획이 바뀜
- 격리수준 변경 혹은
innodb_locks_unsafe_for_binlog같은 설정 영향(구버전) - 트래픽 증가로 충돌 확률이 단순히 임계치를 넘음
2) 데드락 로그부터 제대로 보기
2.1 SHOW ENGINE INNODB STATUS 로 즉시 확인
운영에서 가장 빠른 1차 확인 방법입니다.
SHOW ENGINE INNODB STATUS\G
출력에서 LATEST DETECTED DEADLOCK 섹션을 봅니다. 여기서 중요한 포인트는 아래입니다.
- 어떤 SQL이 락을 잡고 있었는지
- 어떤 인덱스(키)에서 충돌했는지
lock_mode X인지,S인지record lock인지,gap이 포함되는지
주의: 이 출력은 “마지막 데드락 1건”만 보여주므로, 폭증 상황에서는 샘플링에 불과합니다.
2.2 performance_schema 로 누적 관측(가능하면 필수)
MySQL 8 계열에서는 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 r.trx_id = w.requesting_trx_id
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id;
환경에 따라 information_schema 뷰가 제한되거나 MySQL 버전에 따라 다를 수 있으니, 운영 표준에 맞춰 sys 스키마 뷰(sys.innodb_lock_waits)도 함께 확인하세요.
3) 가장 흔한 원인 1: 인덱스 미스/비효율로 넥스트키 락이 커짐
InnoDB에서 REPEATABLE READ 는 팬텀 리드를 막기 위해 범위 조건에서 넥스트키 락 을 적극적으로 사용합니다. 이때 쿼리가 인덱스를 제대로 못 타면, 잠금 범위가 급격히 커져 데드락이 쉬워집니다.
3.1 재현 예시: 범위 조건 + 인덱스 부재
예를 들어 주문 테이블에서 특정 사용자 주문을 “처리중”으로 바꾸는 워커가 있다고 합시다.
-- 나쁜 예: user_id, status에 적절한 인덱스가 없거나
-- 조건이 인덱스를 못 타면 광범위 잠금이 발생
START TRANSACTION;
SELECT id
FROM orders
WHERE user_id = 123 AND status = 'READY'
ORDER BY created_at
LIMIT 1
FOR UPDATE;
UPDATE orders
SET status = 'PROCESSING'
WHERE id = ?;
COMMIT;
여기서 핵심은 FOR UPDATE 가 정확히 필요한 레코드만 잠그는지 입니다.
WHERE user_id = 123 AND status = 'READY'를 만족하는 후보가 많고ORDER BY created_at까지 포함되는데(user_id, status, created_at)같은 복합 인덱스가 없다면
MySQL은 정렬/필터를 위해 더 많은 레코드를 스캔하고, 그 과정에서 더 넓은 범위에 락을 걸 수 있습니다.
3.2 해결: “잠금 쿼리” 기준으로 복합 인덱스 설계
잠금이 걸리는 쿼리(FOR UPDATE, UPDATE, DELETE)는 조회 쿼리보다 훨씬 엄격하게 인덱스를 설계해야 합니다.
위 예시라면 다음이 후보입니다.
CREATE INDEX idx_orders_user_status_created
ON orders (user_id, status, created_at, id);
- 앞쪽 컬럼은
WHERE동등 조건을 우선 배치 - 그 다음
ORDER BY컬럼 - 마지막에
id를 붙여 커버링/정렬 안정성에 도움
이렇게 하면 FOR UPDATE 가 “최소 범위”만 잠그도록 유도할 수 있습니다.
3.3 EXPLAIN 에서 꼭 볼 것
EXPLAIN
SELECT id
FROM orders
WHERE user_id = 123 AND status = 'READY'
ORDER BY created_at
LIMIT 1
FOR UPDATE;
체크 포인트:
type이range나ref인지, 아니면ALL인지key가 의도한 인덱스인지rows추정치가 과도하게 큰지Extra에Using filesort가 뜨는지
Using filesort 자체가 항상 나쁜 건 아니지만, 잠금 쿼리에서 filesort가 개입하면 스캔 범위가 커질 가능성이 큽니다.
4) 가장 흔한 원인 2: 잠금 순서 불일치(특히 다중 행 업데이트)
데드락의 정석 원인은 “서로 다른 순서로 같은 자원을 잠금”입니다. 애플리케이션이 의도치 않게 잠금 순서를 뒤섞는 대표 케이스가 아래입니다.
- 배치/워커가 여러 행을
IN (...)으로 업데이트 - 트랜잭션 내에서 서로 다른 테이블을 순서가 다르게 접근
- 비즈니스 로직 분기 때문에 A 경로는
users후orders, B 경로는 반대
4.1 해결: 항상 같은 순서로 잠그기
예: 여러 주문을 처리해야 한다면, 애플리케이션에서 정렬된 순서로 잠금 획득을 강제합니다.
-- 예시: id 오름차순으로 잠금 획득
START TRANSACTION;
SELECT id
FROM orders
WHERE id IN (101, 55, 78)
ORDER BY id
FOR UPDATE;
UPDATE orders
SET status = 'PROCESSING'
WHERE id IN (55, 78, 101);
COMMIT;
핵심은 ORDER BY id FOR UPDATE 로 락 획득 순서를 결정하는 것입니다.
4.2 UPDATE ... ORDER BY 가 도움이 되는 경우
MySQL은 일부 케이스에서 다음처럼 업데이트 순서를 고정할 수 있습니다.
UPDATE orders
SET status = 'ARCHIVED'
WHERE user_id = 123 AND status = 'DONE'
ORDER BY id
LIMIT 1000;
대량 처리 배치에서 “락 순서 고정”과 “청크 처리”를 함께 하면 데드락과 락 대기를 동시에 낮출 수 있습니다.
5) 격리수준 튜닝: REPEATABLE READ vs READ COMMITTED
5.1 왜 격리수준이 데드락에 영향을 주나
InnoDB에서 기본 격리수준은 보통 REPEATABLE READ 입니다. 이 모드에서는 팬텀 리드를 방지하기 위해 범위 쿼리에서 갭/넥스트키 락이 더 적극적으로 사용될 수 있습니다.
반면 READ COMMITTED 는 일반적으로 갭 락 사용이 줄어들어(특히 단순 조회) 동시성에 유리한 경우가 많습니다. 데드락 폭증이 “범위 잠금 과다”에서 왔다면, READ COMMITTED 로 내려서 개선되는 케이스가 있습니다.
5.2 격리수준 변경 전 체크리스트
READ COMMITTED 로 바꾸기 전에 아래를 반드시 점검하세요.
- 동일 트랜잭션 내 반복 조회 결과가 달라져도 되는가
- 비즈니스 로직이 “한 트랜잭션에서 본 값이 고정”이라는 가정에 기대고 있지 않은가
SELECT후UPDATE패턴에서 경쟁 조건이 생기지 않는가
특히 “재고 차감”, “포인트 차감” 같은 로직은 격리수준 변경보다 원자적 업데이트로 바꾸는 게 안전합니다.
5.3 세션/트랜잭션 단위로 점진 적용
운영에서는 전역 변경보다, 문제 워커/배치의 커넥션에만 먼저 적용하는 방식이 안전합니다.
-- 세션 단위
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- ...
COMMIT;
또는 애플리케이션에서 해당 작업 전용 커넥션 풀을 분리하는 방법도 실전에서 자주 씁니다.
6) 쿼리 패턴 튜닝: “읽고 나서 쓰기”를 “바로 쓰기”로
데드락은 SELECT ... FOR UPDATE 를 남발할수록 증가합니다. 많은 경우 “잠금 조회 후 업데이트”를 아래처럼 바꿔서 락 범위를 줄일 수 있습니다.
6.1 원자적 조건 업데이트로 경쟁 제거
-- 재고 차감 예시: 먼저 읽고 판단하지 말고
-- 조건을 UPDATE에 포함해 원자적으로 처리
UPDATE inventory
SET qty = qty - 1
WHERE sku = 'A-123' AND qty >= 1;
이후 애플리케이션에서는 affected_rows 가 1인지로 성공/실패를 판단합니다. 이렇게 하면 불필요한 FOR UPDATE 와 긴 트랜잭션을 줄여 데드락 가능성을 낮춥니다.
6.2 유니크 키로 “선점”하기(작업 큐 패턴)
작업을 여러 워커가 가져가는 구조라면, “선점 테이블”을 두고 유니크 키로 충돌을 흡수하는 방식이 강력합니다.
-- (job_id)는 PK
INSERT INTO job_locks (job_id, locked_at)
VALUES (123, NOW());
이미 누가 선점했다면 PK 충돌로 실패하고, 워커는 다른 작업을 가져가면 됩니다. 락 대기 대신 “즉시 실패”로 바꾸는 전략입니다.
7) 트랜잭션을 짧게: 데드락은 확률 게임이다
같은 쿼리라도 트랜잭션이 길어지면 데드락 확률이 급증합니다.
- 외부 API 호출을 트랜잭션 안에 넣지 않기
- 대량 처리 시 1000건 단위 청크 커밋
- 불필요한
SELECT를 트랜잭션 밖으로 이동
특히 MSA 환경에서는 DB 트랜잭션과 분산 작업이 섞이면서 락 유지 시간이 길어지는 실수가 흔합니다. 보상 트랜잭션 기반으로 경계를 정리하는 접근도 참고할 만합니다: MSA 사가 패턴 - Temporal로 보상 트랜잭션 구현
8) 데드락은 “정상”이다: 재시도 전략을 설계에 포함
InnoDB는 데드락을 감지하면 한 트랜잭션을 롤백시켜 문제를 풀어줍니다. 즉, 데드락 자체는 완전히 0으로 만들기 어렵고, 낮은 빈도로는 정상입니다.
중요한 건 아래 2가지입니다.
- 데드락이 발생해도 사용자 경험이 깨지지 않게 재시도
- 무한 재시도 대신 지수 백오프 와 최대 횟수 제한
애플리케이션 예시(의사 코드):
for attempt in 1..3:
try:
begin
do tx
commit
return success
catch deadlock:
rollback
sleep(backoff_ms)
return failure
Java/Spring, Go, Node.js 모두 같은 원칙을 적용할 수 있습니다.
9) 운영 체크리스트: “인덱스”와 “격리수준”을 어떻게 결정할까
9.1 인덱스 튜닝 체크리스트
- 잠금 쿼리(
UPDATE,DELETE,SELECT ... FOR UPDATE) 기준으로 인덱스 설계 - 범위 조건이 있다면 선두 컬럼 구성 재점검
ORDER BY와LIMIT이 있는 잠금 쿼리는 복합 인덱스로 정렬 비용 제거- 실행 계획이 바뀌지 않았는지(배포 전후
EXPLAIN비교)
9.2 격리수준 튜닝 체크리스트
- 팬텀 리드/반복 조회 일관성이 정말 필요한 트랜잭션인지 분류
- 필요한 곳만
REPEATABLE READ유지, 나머지는READ COMMITTED고려 - 변경은 세션 단위로 점진 적용하고, 데드락/락 대기/응답시간을 같이 관측
9.3 관측 지표
- 데드락 건수(로그 기반 집계)
- 평균/상위 퍼센타일 락 대기 시간
innodb_row_lock_time,innodb_row_lock_waits(상태 변수)- 특정 쿼리의 수행 빈도와 평균 시간
10) 결론: 폭증을 잡는 우선순위
데드락 폭증을 “인덱스 vs 격리수준” 중 하나로만 보면 헛발질하기 쉽습니다. 실전 우선순위는 보통 아래가 가장 효율적입니다.
- 데드락 로그에서 충돌 쿼리와 인덱스를 특정
- 잠금 쿼리가 최소 범위를 잠그도록 복합 인덱스/쿼리 형태 수정
- 트랜잭션을 짧게 만들고, 다중 행 작업은 락 순서 고정
- 그래도 범위 잠금이 구조적으로 크면
READ COMMITTED를 선별 적용 - 마지막으로 재시도/백오프를 넣어 사용자 영향 최소화
이 순서대로 접근하면, “어느 날 갑자기 데드락이 폭증”하는 사건을 대부분 안정적으로 수습할 수 있고, 이후에도 같은 유형의 회귀를 예방할 수 있습니다.