- Published on
MySQL InnoDB 데드락 로그로 10분 재현·해결
- Authors
- Name
- 스타차일드
- https://x.com/ETFBITX
운영에서 데드락이 한 번 터지면 보통 이런 흐름으로 번집니다. 애플리케이션 로그에는 Deadlock found when trying to get lock 정도만 남고, 재현은 어렵고, “그냥 재시도하면 된다”로 덮여서 같은 문제가 계속 반복됩니다.
하지만 InnoDB는 데드락 발생 시점의 잠금 그래프를 꽤 자세히 남깁니다. 핵심은 이 로그를 정확히 읽고, 로그에 나온 순서를 그대로 두 세션으로 재현한 다음, 잠금 순서(락 오더)와 인덱스/쿼리 형태를 바꿔 데드락 자체를 구조적으로 제거하는 것입니다.
이 글은 다음을 목표로 합니다.
SHOW ENGINE INNODB STATUS또는performance_schema에서 데드락 로그를 확보한다- 로그를 “잠금 순서” 관점으로 해석한다
- 동일 패턴을 10분 안에 2세션으로 재현한다
- 해결책을
쿼리/인덱스/트랜잭션관점에서 적용한다 - 재발 방지(관측, 재시도, 배포 검증)까지 마무리한다
1) 데드락이란: 충돌이 아니라 “순환”이다
데드락은 단순한 락 경합(lock wait)과 다릅니다.
- 락 경합: A가 B를 기다리면 언젠가 풀리면 진행됨
- 데드락: A가 B를 기다리고, B가 A를 기다리는 순환(cycle) 이 생겨서 진행 불가
InnoDB는 이를 감지하면 한 트랜잭션을 희생(victim)시켜 롤백하고, 다른 트랜잭션을 살립니다. 즉, “DB가 알아서 풀어준다”는 건 맞지만, 희생된 트랜잭션의 작업은 유실되고 애플리케이션은 에러를 받습니다.
2) 데드락 로그 확보: 가장 빠른 두 가지
2.1 SHOW ENGINE INNODB STATUS
가장 흔한 방법입니다. 최근 1건(또는 제한적으로 몇 건)의 데드락 정보를 포함합니다.
SHOW ENGINE INNODB STATUS\G
출력에서 LATEST DETECTED DEADLOCK 섹션을 찾습니다.
2.2 performance_schema로 누적 수집(추천)
운영에서 “그때그때 수동으로”는 놓치기 쉽습니다. MySQL 8에서는 performance_schema를 통해 더 체계적으로 수집할 수 있습니다.
환경에 따라 테이블 구성이 다를 수 있지만, 최소한 아래는 확인해두면 좋습니다.
-- performance_schema 활성 여부
SHOW VARIABLES LIKE 'performance_schema';
-- InnoDB 락/트랜잭션 관련 인스트루먼트가 켜져 있는지 점검
SELECT *
FROM performance_schema.setup_instruments
WHERE NAME LIKE 'wait/lock/%'
OR NAME LIKE 'transaction%'
LIMIT 50;
운영에서는 데드락 로그를 애플리케이션 로그처럼 중앙 수집(예: Cloud Logging, ELK)하는 편이 재현 속도가 압도적으로 빨라집니다.
3) 데드락 로그 읽는 법: “두 트랜잭션의 락 획득 순서”만 뽑아라
데드락 로그는 길지만, 실전에서 필요한 정보는 보통 아래 4가지입니다.
- (1)번 트랜잭션이 실행한 SQL
- (2)번 트랜잭션이 실행한 SQL
- 각 트랜잭션이 이미 잡고 있는 락
- 각 트랜잭션이 추가로 기다리는 락
아래는 전형적인 패턴 예시입니다(형태만 참고).
- 트랜잭션 1:
orders의 특정 레코드(또는 갭)에 X 락을 잡고order_items를 업데이트하려다 대기 - 트랜잭션 2:
order_items의 특정 레코드에 X 락을 잡고orders를 업데이트하려다 대기
이때 해결의 80%는 “두 쿼리가 서로 다른 테이블/인덱스를 반대 순서로 잠근다”는 사실을 확인하는 데서 끝납니다.
정리 팁:
- 로그를 보면
(1) TRANSACTION:과(2) TRANSACTION:블록이 있습니다. - 각 블록에서
WAITING FOR THIS LOCK TO BE GRANTED바로 위/아래를 읽으면 “무엇을 기다리는지”가 나옵니다. index이름이 나오면, “그 인덱스 범위를 어떻게 스캔했길래 락을 넓게 잡았는지”를 의심합니다.
4) 10분 재현: 두 세션으로 잠금 순서를 고정한다
재현이 빨라지는 핵심은 “동시성”이 아니라 “락 순서”를 강제로 만드는 것입니다. 아래는 자주 터지는 부모-자식(orders / order_items) 구조에서 데드락을 인위적으로 만드는 최소 예시입니다.
4.1 재현용 스키마
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
status VARCHAR(20) NOT NULL,
updated_at DATETIME NOT NULL,
KEY idx_status_updated (status, updated_at)
) ENGINE=InnoDB;
CREATE TABLE order_items (
id BIGINT PRIMARY KEY,
order_id BIGINT NOT NULL,
qty INT NOT NULL,
KEY idx_order_id (order_id),
CONSTRAINT fk_order_items_order
FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB;
INSERT INTO orders(id, status, updated_at)
VALUES (1, 'NEW', NOW());
INSERT INTO order_items(id, order_id, qty)
VALUES (10, 1, 1), (11, 1, 2);
4.2 세션 A: orders 먼저 잠그고 order_items로 이동
-- Session A
SET autocommit = 0;
BEGIN;
-- 1) 부모 먼저 잠금
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
-- 2) 잠시 대기(동시성 타이밍 고정)
DO SLEEP(3);
-- 3) 자식 갱신
UPDATE order_items
SET qty = qty + 1
WHERE order_id = 1;
COMMIT;
4.3 세션 B: order_items 먼저 잠그고 orders로 이동
-- Session B
SET autocommit = 0;
BEGIN;
-- 1) 자식 먼저 잠금
SELECT * FROM order_items WHERE order_id = 1 FOR UPDATE;
-- 2) 잠시 대기
DO SLEEP(3);
-- 3) 부모 갱신
UPDATE orders
SET status = 'PAID', updated_at = NOW()
WHERE id = 1;
COMMIT;
위 순서를 맞추면, 환경에 따라 다르지만 높은 확률로 둘 중 하나가 데드락 희생자가 됩니다.
재현이 안 되면 체크할 것:
- 두 세션이 같은 행(또는 같은 인덱스 범위)을 정말로 잠그는지
SELECT ... FOR UPDATE대상이 인덱스를 타는지(EXPLAIN)- 트랜잭션 격리 수준이 무엇인지
SELECT @@transaction_isolation;
EXPLAIN SELECT * FROM order_items WHERE order_id = 1 FOR UPDATE;
5) 원인 분류: 데드락을 만드는 4가지 대표 패턴
5.1 락 오더 불일치(가장 흔함)
- 트랜잭션 A: 테이블 1 잠금 후 테이블 2
- 트랜잭션 B: 테이블 2 잠금 후 테이블 1
해결은 “항상 같은 순서로 잠그기”가 정답입니다.
5.2 인덱스 미스로 인한 과도한 범위 잠금
조건이 인덱스를 못 타면, InnoDB가 더 많은 레코드/갭에 락을 잡아 데드락 확률이 급상승합니다.
WHERE절 컬럼에 적절한 인덱스가 없음- 복합 인덱스 선두 컬럼을 건너뜀
- 타입 캐스팅/함수 적용으로 인덱스 사용 불가
5.3 SELECT ... FOR UPDATE 남발
읽기처럼 보이지만 실제로는 쓰기 잠금(X)을 잡습니다. 특히 “먼저 읽고 나중에 업데이트” 패턴에서 트랜잭션이 길어지면 데드락이 잘 납니다.
5.4 FK 제약과 연쇄 잠금
외래키가 있으면 부모/자식 테이블에 추가 잠금이 걸릴 수 있습니다. DELETE 나 UPDATE 시에 특히 주의합니다.
6) 해결: 로그에 나온 “락 순서”를 단일화하라
앞서 재현 예시의 해결은 간단합니다. 두 트랜잭션이 orders 와 order_items를 서로 반대로 잠그는 게 문제이므로, 애플리케이션에서 항상 같은 순서로 잠그도록 바꿉니다.
6.1 해결안 A: 잠금 순서 통일(권장)
예: 항상 orders를 먼저 잠근 뒤 order_items를 잠근다.
-- 어떤 코드 경로든 동일한 락 오더를 강제
BEGIN;
SELECT id FROM orders WHERE id = ? FOR UPDATE;
SELECT id FROM order_items WHERE order_id = ? FOR UPDATE;
-- 이후 업데이트들
UPDATE orders SET ... WHERE id = ?;
UPDATE order_items SET ... WHERE order_id = ?;
COMMIT;
핵심은 “업데이트 문 자체”가 아니라, 선행 잠금 단계에서 순서를 맞추는 것입니다.
6.2 해결안 B: 인덱스 보강으로 락 범위 축소
데드락 로그에 index 이름이 찍히고, 해당 인덱스가 비효율적이라면 인덱스 설계가 해결책이 됩니다.
예를 들어 order_items를 order_id로 자주 잠그는데 인덱스가 없다면 아래는 거의 필수입니다.
ALTER TABLE order_items
ADD INDEX idx_order_id (order_id);
또한 “상태별로 오래된 주문을 잡아 처리” 같은 배치가 있다면 복합 인덱스를 조건/정렬에 맞게 재설계해야 합니다.
-- 예: status로 필터하고 updated_at으로 정렬/범위 처리
ALTER TABLE orders
ADD INDEX idx_status_updated (status, updated_at);
6.3 해결안 C: 트랜잭션을 짧게, 잠금 시간을 줄이기
- 트랜잭션 안에서 외부 API 호출 금지
- 불필요한
SELECT ... FOR UPDATE제거 - 같은 트랜잭션에서 많은 행을 한 번에 갱신하지 말고 배치 크기 제한
-- 예: 배치 처리 시 한 번에 너무 많이 잠그지 않기
UPDATE orders
SET status = 'ARCHIVED'
WHERE status = 'DONE'
ORDER BY updated_at
LIMIT 200;
LIMIT 업데이트는 설계에 따라 주의가 필요하지만, “한 트랜잭션이 잡는 락의 총량”을 줄이는 데는 효과적입니다.
6.4 해결안 D: 충돌이 잦은 자원은 논리 락으로 직렬화
정말로 동일 자원(예: 같은 order_id)에 동시 업데이트가 빈번하다면, 애플리케이션 레벨에서 직렬화하는 것이 더 단순할 때도 있습니다.
MySQL에서는 GET_LOCK 같은 사용자 락을 쓸 수 있지만, 남용하면 병목이 되므로 “핵심 키에만” 제한적으로 적용합니다.
SELECT GET_LOCK(CONCAT('order:', 1), 3);
-- critical section
SELECT RELEASE_LOCK(CONCAT('order:', 1));
7) 재발 방지: 재시도는 필요조건이지 충분조건이 아니다
데드락은 완전히 0으로 만들기 어렵고, 특히 고동시성 환경에서는 “가끔” 발생할 수 있습니다. 따라서 아래를 같이 가져가야 합니다.
7.1 애플리케이션 재시도(백오프 포함)
- 데드락 에러 코드(예: 1213)를 식별해 짧게 재시도
- 즉시 재시도는 또 충돌하므로 지수 백오프 + 지터 권장
for attempt in 1..3:
try:
tx()
return
catch Deadlock:
sleep(backoff_with_jitter(attempt))
throw
7.2 관측: 데드락을 “사건”이 아니라 “지표”로
- 데드락 건수, 희생 트랜잭션의 쿼리 패턴, 발생 테이블 TOP N을 대시보드화
- 배포 직후 급증하면 롤백 판단이 빨라집니다
운영 장애를 빠르게 진단하는 관점은 네트워크/인프라 이슈에서도 동일합니다. 예를 들어 커넥션 드랍 원인을 추적하는 흐름은 EKS conntrack 테이블 포화로 연결 끊김 해결법 같은 글의 접근과도 닮아 있습니다. “증상”을 “관측 가능한 지표”로 바꾸는 게 핵심입니다.
8) 실전 체크리스트: 로그 한 장으로 결론 내기
데드락 로그를 받았을 때 아래 순서로 보면, 재현과 해결이 거의 자동으로 따라옵니다.
- 두 트랜잭션의 SQL을 추출한다
- 각 트랜잭션이 잡은 락과 기다리는 락을 표로 정리한다
- 공통 자원(테이블/인덱스/레코드 범위)을 찾는다
- 락 오더가 뒤집혀 있는지 확인한다
EXPLAIN으로 인덱스 사용 여부를 확인한다- 해결책을 우선순위로 적용한다
- 1순위: 락 오더 통일
- 2순위: 인덱스로 락 범위 축소
- 3순위: 트랜잭션 단축
- 4순위: 제한적 직렬화
- 재시도 + 관측으로 운영 안전장치를 건다
9) 마무리: “재현 가능”해지면 데드락은 빨리 끝난다
InnoDB 데드락은 무서운 문제가 아니라, 로그가 이미 해답을 주는 문제에 가깝습니다. 로그를 락 그래프 관점으로 읽고, 두 세션으로 잠금 순서를 그대로 재현해보면 원인은 금방 드러납니다. 그 다음은 대부분 “락 오더 통일”과 “인덱스/쿼리로 락 범위 축소”로 정리됩니다.
운영에서 반복되는 데드락을 더 이상 운에 맡기지 않으려면, 오늘 바로 아래 두 가지부터 해보면 좋습니다.
- 데드락 로그를 중앙 수집해, 발생 즉시 원문을 확보하기
- 상위 1~2개 패턴에 대해 2세션 재현 SQL을 만들어 레포에 남기기
이 두 가지만 해도 “다음 데드락”은 10분 안에 재현하고, 같은 날에 해결할 가능성이 크게 올라갑니다.