- Published on
MySQL InnoDB 데드락 원인 추적과 인덱스 튜닝
- Authors
- Name
- 스타차일드
- https://x.com/ETFBITX
서버가 바쁘면 데드락은 ‘가끔’이 아니라 ‘언젠가 반드시’ 발생합니다. 중요한 건 데드락 자체를 0으로 만드는 것이 아니라, (1) 왜 그 잠금이 잡혔는지 추적하고 (2) 잠금 범위를 줄이도록 인덱스/쿼리를 튜닝하며 (3) 애플리케이션에서 재시도 가능한 형태로 설계하는 것입니다.
InnoDB의 데드락은 보통 다음 조합에서 자주 터집니다.
- 인덱스가 없어 풀스캔/광범위한 레코드 잠금이 발생
UPDATE ... WHERE조건이 비선택적이라 갭/넥스트키 잠금(next-key lock) 범위가 커짐- 서로 다른 순서로 같은 리소스를 갱신(예: A→B vs B→A)
- 긴 트랜잭션(외부 API 호출 포함)로 락 보유 시간이 길어짐
이 글은 “로그를 보고 원인을 특정 → 인덱스로 잠금 범위를 줄이기 → 트랜잭션/쿼리 패턴 개선” 순서로 진행합니다. 트랜잭션 설계 함정은 Spring Boot 3 @Transactional 전파·격리 함정 7가지도 함께 참고하면 좋습니다.
1) InnoDB 데드락이 발생하는 메커니즘 요약
InnoDB는 기본 격리수준이 REPEATABLE READ인 경우가 많고(환경에 따라 다름), 이때 팬텀 방지를 위해 SELECT ... FOR UPDATE, UPDATE, DELETE 같은 잠금 연산에서 레코드 락 + 갭 락을 결합한 넥스트키 잠금을 사용합니다.
- Record lock: 특정 인덱스 레코드
- Gap lock: 인덱스 레코드 사이 “구간”
- Next-key lock: 레코드 + 앞 구간(또는 뒤 구간) 결합
문제는 인덱스를 잘 못 타면 “필요한 몇 행”이 아니라 “범위 전체”를 잠그는 형태가 되어, 다른 트랜잭션과 교착 상태가 쉽게 만들어진다는 점입니다.
2) 데드락을 ‘관측 가능’하게 만들기 (필수 설정)
2.1 데드락 로그 활성화
운영에서 가장 먼저 할 일은 데드락 정보를 남기는 것입니다.
-- MySQL 8 기준
SET GLOBAL innodb_print_all_deadlocks = ON;
-- 현재 값 확인
SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';
그리고 에러 로그(MySQL error log) 수집/검색이 가능해야 합니다(예: CloudWatch, ELK 등).
2.2 즉시 확인: SHOW ENGINE INNODB STATUS
“방금” 발생한 데드락은 아래에서 확인할 수 있습니다.
SHOW ENGINE INNODB STATUS\G
출력에서 LATEST DETECTED DEADLOCK 섹션이 핵심입니다. 다만 마지막 데드락 1건만 보여주므로, 운영에서는 앞의 innodb_print_all_deadlocks=ON을 권장합니다.
2.3 Performance Schema로 락 대기 관찰(가능하면)
MySQL 8에서 performance_schema가 켜져 있다면, 락/대기/트랜잭션 정보를 더 구조적으로 볼 수 있습니다(환경에 따라 테이블/뷰는 다를 수 있음).
-- 어떤 세션이 어떤 락을 기다리는지(환경별로 뷰 이름 상이)
SELECT *
FROM performance_schema.data_lock_waits;
SELECT *
FROM performance_schema.data_locks;
이 데이터는 “지금 막힌 상황”을 보는 데 좋고, 데드락은 이미 종료된 뒤라면 로그 기반 분석이 더 유리합니다.
3) 데드락 로그 읽는 법: ‘어떤 인덱스를 어떤 모드로 잠갔나’
LATEST DETECTED DEADLOCK에는 대략 이런 정보가 나옵니다.
- (1) 트랜잭션 A/B의 SQL
- (2) 각 트랜잭션이 보유한 락(holds)
- (3) 각 트랜잭션이 기다리는 락(waiting for)
- (4) 충돌한 인덱스/레코드(어떤 index의 어떤 record)
여기서 핵심은 **테이블 이름이 아니라 “인덱스 이름”**입니다.
- 충돌 인덱스가 PK인지, 보조 인덱스인지
lock_mode X(배타),S(공유),insert intention등gap/next-key언급 여부
즉 “왜 데드락이 났지?”의 답은 보통 “인덱스가 이렇게 잠겼기 때문”으로 귀결됩니다.
4) 재현 가능한 대표 패턴 3가지와 해결(인덱스 중심)
패턴 A: 인덱스 미스 → 범위 잠금 확대 → 데드락
예: 주문 테이블에서 상태 전이를 처리하는 워커가 여러 개 돌 때.
-- 테이블 예시
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
status VARCHAR(20) NOT NULL,
updated_at DATETIME NOT NULL,
KEY idx_status_updated (status, updated_at)
) ENGINE=InnoDB;
다음 쿼리가 있다고 합시다.
-- (문제) user_id로 필터하지만 인덱스가 없으면 많은 레코드를 훑으며 잠금
UPDATE orders
SET status = 'PROCESSING', updated_at = NOW()
WHERE user_id = 123
AND status = 'READY'
ORDER BY updated_at
LIMIT 50;
user_id에 인덱스가 없으면, 조건을 만족하는 행을 찾기 위해 status 인덱스만 타거나(혹은 더 나쁘면 풀스캔) 많은 레코드를 읽고 잠금 후보를 넓힙니다. 동시에 다른 워커가 다른 user_id를 처리해도, 스캔 경로에서 락 경합이 생길 수 있습니다.
해결은 보통 “조건을 가장 잘 표현하는 복합 인덱스”입니다.
-- 해결: 워커가 찾는 조건을 그대로 인덱스로
CREATE INDEX idx_user_status_updated
ON orders (user_id, status, updated_at);
그리고 실행 계획을 확인합니다.
EXPLAIN UPDATE orders
SET status='PROCESSING', updated_at=NOW()
WHERE user_id=123 AND status='READY'
ORDER BY updated_at
LIMIT 50;
튜닝 포인트
type이range/ref로 좁혀지는지rows추정치가 과도하게 크지 않은지Using filesort가 뜨면ORDER BY가 인덱스로 해결되는지 재검토
인덱스가 잘 맞으면, 스캔/잠금 범위가 줄어들어 데드락 확률이 급감합니다.
패턴 B: 서로 다른 순서로 다중 행 업데이트(A→B vs B→A)
가장 고전적인 데드락입니다. 예를 들어 “두 계좌 간 이체”에서 두 행을 업데이트할 때:
- 트랜잭션 1: account 1 잠금 → account 2 잠금
- 트랜잭션 2: account 2 잠금 → account 1 잠금
해결은 “항상 같은 순서로 잠그기”입니다.
-- 계좌 테이블
CREATE TABLE accounts (
id BIGINT PRIMARY KEY,
balance BIGINT NOT NULL
) ENGINE=InnoDB;
애플리케이션에서 두 id를 정렬해 순서를 고정합니다.
-- 항상 작은 id부터 잠그기
SELECT id, balance
FROM accounts
WHERE id IN (?, ?)
ORDER BY id
FOR UPDATE;
-- 이후 UPDATE 2번 또는 CASE로 1번에 처리
UPDATE accounts
SET balance = CASE
WHEN id = ? THEN balance - ?
WHEN id = ? THEN balance + ?
ELSE balance
END
WHERE id IN (?, ?);
이 패턴은 인덱스 튜닝보다 락 획득 순서 통일이 1순위지만, WHERE id IN (...)이 PK를 타는지(즉, PK 기반으로 정확히 잠그는지)는 여전히 중요합니다.
패턴 C: 유니크 인덱스 + 동시 INSERT/UPSERT 충돌
예: user_id + date로 하루 1건만 존재해야 하는 테이블.
CREATE TABLE user_daily (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT NOT NULL,
ymd DATE NOT NULL,
cnt INT NOT NULL,
UNIQUE KEY uk_user_ymd (user_id, ymd)
) ENGINE=InnoDB;
동시에 들어온 요청이 INSERT ... ON DUPLICATE KEY UPDATE로 경합하면 데드락이 날 수 있습니다.
INSERT INTO user_daily(user_id, ymd, cnt)
VALUES(?, CURDATE(), 1)
ON DUPLICATE KEY UPDATE cnt = cnt + 1;
완화책:
- 트랜잭션을 짧게 유지
- 같은 키로의 동시 요청을 애플리케이션에서 샤딩/락(예: 분산락) 하거나
- 충돌이 잦으면 재시도 로직을 두고(아래 참고) 모니터링
5) 인덱스 튜닝 체크리스트: 데드락을 줄이는 “락 범위 최소화”
5.1 WHERE 조건을 “왼쪽부터” 만족하는 복합 인덱스
InnoDB에서 잠금은 “접근한 인덱스 레코드”에 걸립니다. 즉, 어떤 인덱스로 접근했는지가 락 범위를 결정합니다.
- 조건:
tenant_id = ? AND status = ? AND created_at < ? - 인덱스:
(tenant_id, status, created_at)
처럼 “동등 조건 → 범위 조건” 순으로 설계하면 스캔 범위가 크게 줄어듭니다.
5.2 ORDER BY + LIMIT 업데이트는 특히 인덱스가 중요
작업 큐를 테이블로 구현할 때 흔한 형태:
-- READY 중 오래된 것부터 N개 집어오기
UPDATE jobs
SET status='RUNNING'
WHERE status='READY'
ORDER BY created_at
LIMIT 100;
이때 필요한 인덱스는 보통:
CREATE INDEX idx_jobs_status_created
ON jobs(status, created_at);
인덱스가 없으면 정렬/스캔 과정에서 더 넓은 잠금이 발생하고, 여러 워커가 동시에 돌면 데드락이 빈번해집니다.
5.3 커버링 인덱스는 “락 자체”를 없애진 않지만 시간을 줄인다
잠금 범위를 줄이는 게 1순위지만, 락 보유 시간을 줄이는 것도 중요합니다.
- 필요한 컬럼을 인덱스에 포함해 테이블(클러스터드 PK)로의 추가 랜덤 I/O를 줄이면
- 같은 트랜잭션이 더 빨리 끝나고
- 결과적으로 데드락/락 대기 확률이 내려갑니다.
(MySQL 8에선 EXPLAIN ANALYZE로 실제 수행 시간을 확인하는 습관이 유효합니다.)
5.4 격리수준/락 모드 재검토(특히 갭락)
REPEATABLE READ에서 갭락이 문제라면, 워크로드에 따라 READ COMMITTED로 낮추는 것이 도움이 될 수 있습니다. 다만 이는 일관성 모델 변화를 동반하므로, 변경 전후로 애플리케이션 요구사항을 점검해야 합니다.
트랜잭션/격리수준 관련 함정은 Spring Boot 3 @Transactional 전파·격리 함정 7가지를 같이 보면 “왜 특정 쿼리가 오래 락을 잡고 있었나”까지 연결해서 볼 수 있습니다.
6) 애플리케이션 대응: 데드락은 ‘재시도 가능한 오류’로 다뤄라
MySQL에서 데드락은 보통 다음 에러로 옵니다.
ER_LOCK_DEADLOCK (1213): Deadlock found when trying to get lock; try restarting transaction
즉 DB가 “너 한 번 다시 해”라고 말합니다. 따라서 아래 원칙이 좋습니다.
- 짧은 트랜잭션(외부 API 호출/파일 IO를 트랜잭션 안에 넣지 않기)
- 멱등성 확보(재시도해도 중복 처리되지 않게)
- 지수 백오프 + 지터로 재시도
- 재시도 횟수 초과 시 경고/메트릭
예: Node.js(의사 코드)
async function withDeadlockRetry(fn, maxRetry = 3) {
for (let i = 0; i <= maxRetry; i++) {
try {
return await fn();
} catch (e) {
const isDeadlock = e && (e.errno === 1213 || /Deadlock/.test(e.message));
if (!isDeadlock || i === maxRetry) throw e;
const base = 50 * Math.pow(2, i);
const jitter = Math.floor(Math.random() * 30);
await new Promise(r => setTimeout(r, base + jitter));
}
}
}
재시도는 만능이 아닙니다. 재시도가 빈번해지면 **근본 원인(인덱스/쿼리/락 순서)**을 반드시 찾아야 합니다. 멱등 처리 전략은 메시징/이벤트 기반에서도 중요하며, Kafka Exactly-Once 깨질 때 멱등키·Outbox 패턴에서 다루는 멱등키/Outbox 접근이 DB 재시도 설계에도 그대로 응용됩니다.
7) 실전 진단 플로우(운영에서 빠르게)
7.1 “어떤 쿼리 조합”이 데드락을 만드는지부터 특정
- 에러 로그에서 데드락 블록 수집(시간대/SQL/테이블/인덱스)
- 같은 시간대의 슬로우 쿼리/트래픽 급증과 상관관계 확인
- 데드락 블록에서 충돌 인덱스 이름을 뽑아낸다
7.2 해당 쿼리의 실행 계획과 인덱스 적합성 확인
EXPLAIN/EXPLAIN ANALYZE- 조건 컬럼이 복합 인덱스의 선두(leftmost)로 커버되는지
ORDER BY가 인덱스로 풀리는지- 불필요하게 많은 행을 스캔(
rows과대)하는지
7.3 인덱스 추가/수정 후 “데드락 감소”를 검증
- 데드락은 확률적이므로, 배포 전후로
- 데드락 발생 빈도
- 락 대기 시간(가능하면)
- P95/P99 응답시간
- 업데이트/처리량 을 함께 봐야 합니다.
8) 결론: 데드락 해결의 80%는 ‘인덱스와 접근 경로’다
InnoDB 데드락은 단순히 동시성 탓이 아니라, 어떤 인덱스로 어떤 순서로 어떤 범위를 잠갔는지의 결과입니다. 가장 효과적인 접근은 다음 3단계입니다.
SHOW ENGINE INNODB STATUS/ 에러 로그로 데드락을 수집하고, 충돌 인덱스/락 모드를 읽는다EXPLAIN으로 접근 경로를 확인하고, WHERE/ORDER BY/LIMIT에 맞는 복합 인덱스로 잠금 범위를 줄인다- 애플리케이션에서 재시도(1213) + 멱등성 + 트랜잭션 단축으로 운영 안정성을 올린다
같은 ‘데드락’이라도 PostgreSQL과 MySQL은 잠금/격리 구현이 달라 접근이 달라질 수 있습니다. 비교 관점이 필요하면 PostgreSQL 데드락 40P01 재현·탐지·해결도 함께 읽어두면, 엔진별로 무엇을 로그에서 봐야 하는지 감이 더 빨리 잡힙니다.