- Published on
MySQL InnoDB 데드락 로그로 범인 쿼리 찾기
- Authors
- Name
- 스타차일드
- https://x.com/ETFBITX
운영 중 간헐적으로 Deadlock found when trying to get lock; try restarting transaction가 터지면, 대부분의 팀이 먼저 하는 일은 “재시도 로직이 있으니 괜찮겠지”입니다. 하지만 데드락이 빈번해지면 TPS가 흔들리고, 커넥션 풀이 밀리며, 결국 장애로 번집니다. 중요한 건 “데드락이 났다”가 아니라 누가(어떤 트랜잭션/쿼리)가 어떤 락을 잡고 있었고, 무엇을 기다리다 꼬였는지를 정확히 찾아내는 것입니다.
이 글은 InnoDB 데드락 로그(SHOW ENGINE INNODB STATUS)를 통해 범인 쿼리를 특정하는 실전 절차를 정리합니다. 애플리케이션이 Spring Boot라면 데드락이 커넥션 풀 고갈로 번지는 케이스도 자주 있으니, 필요하면 Spring Boot 3 503? HikariCP 풀 고갈 원인·해결도 함께 보세요.
데드락 로그를 얻는 3가지 방법
1) 즉시 확인: SHOW ENGINE INNODB STATUS
데드락이 최근에 발생했다면, 아래 출력의 LATEST DETECTED DEADLOCK 섹션에 남아 있습니다.
SHOW ENGINE INNODB STATUS\G
주의할 점:
- 이 출력은 가장 최근 데드락 1건 위주로 보입니다. 연속으로 터지는 상황에서는 “내가 보고 싶은 사건”이 이미 덮였을 수 있습니다.
- 그래서 운영에서는 “발생 즉시 덤프”가 중요합니다(자동 수집 권장).
2) MySQL 에러 로그로 남기기: innodb_print_all_deadlocks
에러 로그에 데드락을 계속 남기면, 사건이 덮이는 문제를 완화할 수 있습니다.
SET GLOBAL innodb_print_all_deadlocks = ON;
- 장점: 서버 로그에 쌓여서 사후 분석이 쉬움
- 단점: 데드락이 잦으면 로그가 과도해질 수 있음
3) Performance Schema로 보강
데드락 로그만으로 “어떤 SQL인지”가 애매할 때가 있습니다(특히 Prepared Statement, 프레임워크가 쿼리를 숨길 때). 이때는 Performance Schema의 statement/transaction 이벤트와 함께 보면 정확도가 올라갑니다.
-- 현재 잠금 대기/락 관련 상태는 환경에 따라 테이블이 다를 수 있습니다.
-- 우선 Performance Schema 활성 여부부터 확인하세요.
SHOW VARIABLES LIKE 'performance_schema';
운영 환경에서 Performance Schema를 이미 쓰고 있다면, 데드락 시점의 thread id를 기준으로 애플리케이션 쿼리까지 역추적하는 루트를 만들어둘 수 있습니다.
InnoDB 데드락 로그를 “범인 쿼리” 관점으로 읽는 법
LATEST DETECTED DEADLOCK는 보통 아래 흐름으로 구성됩니다.
- 어떤 트랜잭션이 데드락에 참여했는지 (보통
*** (1) TRANSACTION:/*** (2) TRANSACTION:) - 각 트랜잭션이 지금 실행 중인 쿼리
- 어떤 락을 가지고 있는지 (
HOLDS THE LOCK(S)) - 어떤 락을 기다리는지 (
WAITING FOR THIS LOCK TO BE GRANTED) - InnoDB가 어떤 트랜잭션을 희생(victim) 시켰는지 (
WE ROLL BACK TRANSACTION (1))
아래는 이해를 위한 축약 예시입니다(형식만 참고).
------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 123456, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 111, OS thread handle 0x..., query id 999 host appuser updating
UPDATE orders SET status='PAID' WHERE id=10;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 100 page no 123 n bits 72 index PRIMARY of table `shop`.`orders`
lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: ...
*** (2) TRANSACTION:
TRANSACTION 123457, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 112, OS thread handle 0x..., query id 1000 host appuser updating
UPDATE orders SET status='CANCEL' WHERE id=20;
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 100 page no 123 n bits 72 index PRIMARY of table `shop`.`orders`
lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 100 page no 124 n bits 72 index PRIMARY of table `shop`.`orders`
lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)
여기서 범인 쿼리를 찾는 핵심 포인트는 다음입니다.
1) “희생된 쿼리”가 범인이라는 오해를 버리기
InnoDB는 데드락을 풀기 위해 한 트랜잭션을 롤백합니다. 하지만 롤백된 쪽이 반드시 “나쁜 쿼리”는 아닙니다.
- 범인은 보통 락을 오래 잡는 쪽
- 혹은 락 획득 순서가 일관되지 않은 쪽
- 또는 불필요하게 많은 로우를 잠그는 쪽입니다
따라서 WE ROLL BACK TRANSACTION (1)만 보고 결론 내리면 자주 틀립니다.
2) 각 트랜잭션의 “WAITING FOR”와 “HOLDS”를 연결하기
데드락은 결국 “A는 B가 가진 락을 기다리고, B는 A가 가진 락을 기다리는” 순환입니다.
TRANSACTION (1)의WAITING FOR대상이 무엇인지 확인TRANSACTION (2)의HOLDS에 동일한 리소스가 있는지 확인- 반대 방향도 동일하게 확인
이렇게 하면 “어떤 인덱스의 어떤 레코드 락”에서 교착이 났는지 구조가 보입니다.
3) 테이블/인덱스 이름이 나오면, “왜 그 인덱스를 탔는지”까지 확인
로그에는 보통 index PRIMARY 또는 보조 인덱스명이 나옵니다.
PRIMARY에서 충돌: 특정 PK 레코드를 서로 물고 늘어지는 전형적인 케이스- 보조 인덱스에서 충돌: 범위 조건, 정렬/스캔, 유니크 제약, FK 체크 등으로 락 범위가 커졌을 수 있음
특히 인덱스가 부정확하면, 의도보다 더 많은 로우를 스캔하면서 락을 크게 잡아 데드락 확률이 급등합니다.
데드락을 재현하는 최소 예제(두 세션)
데드락을 이해하는 가장 빠른 방법은 “두 세션으로 같은 자원을 반대 순서로 잠그기”입니다.
예시 테이블:
CREATE TABLE accounts (
id BIGINT PRIMARY KEY,
balance BIGINT NOT NULL
) ENGINE=InnoDB;
INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 1000);
세션 A:
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 여기서 멈춘다고 가정
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
세션 B:
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;
-- 여기서 멈춘다고 가정
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;
두 세션이 동시에 진행되면,
- A는
id=1을 X 락으로 잡고id=2를 기다림 - B는
id=2를 X 락으로 잡고id=1을 기다림
로 순환이 생기며 데드락이 납니다. 이때 SHOW ENGINE INNODB STATUS\G에서 두 트랜잭션의 쿼리와 기다리는 락을 확인할 수 있습니다.
로그에서 “진짜 범인 쿼리”를 특정하는 실전 절차
운영에서 데드락이 났을 때, 아래 순서대로 보면 속도가 빨라집니다.
1) 트랜잭션별로 “현재 쿼리”를 먼저 추출
데드락 로그에는 각 트랜잭션이 마지막으로 실행한 SQL이 찍힙니다. 먼저 두 트랜잭션의 SQL을 각각 복사해 둡니다.
- 프레임워크가 SQL을 줄여 찍거나 바인딩을 숨기는 경우가 있습니다.
- 이때는 애플리케이션 로그(슬로우쿼리 로그, SQL 로깅)와 대조가 필요합니다.
2) 테이블/인덱스/락 모드로 “충돌 지점”을 요약
아래처럼 한 줄 요약을 만들면, 팀 커뮤니케이션이 쉬워집니다.
- 테이블:
shop.orders - 인덱스:
PRIMARY - 락:
RECORD LOCK+X - 상황: 트랜잭션 A는
id=10을 기다림, 트랜잭션 B는id=20을 기다림
이 요약이 되면, “어떤 비즈니스 흐름에서 서로 반대 순서로 같은 리소스를 만지는지”가 보이기 시작합니다.
3) “락 획득 순서 불일치”를 찾는다
가장 흔한 원인은 같은 테이블을 업데이트하면서도 코드 경로에 따라 갱신 순서가 달라지는 겁니다.
- 주문 상태 업데이트는
ordersثمorder_items - 취소 로직은
order_itemsثمorders
처럼 순서가 뒤집히면, 트래픽이 조금만 올라가도 데드락이 자주 납니다.
해결은 보통 아래 중 하나입니다.
- 업데이트 순서를 항상 동일하게 맞추기
- 한 트랜잭션에서 여러 로우를 갱신해야 한다면 정렬된 순서로 처리하기(예: PK 오름차순)
4) “불필요하게 넓은 락”인지 확인한다
다음과 같은 쿼리는 의도보다 많은 로우를 잠글 수 있습니다.
- 인덱스를 못 타는 조건(함수 적용, 타입 불일치 등)
- 범위 조건 + 보조 인덱스 + 격리수준 조합으로 인한 갭 락
SELECT ... FOR UPDATE를 광범위하게 사용
이 경우 EXPLAIN으로 실행 계획을 확인하고, 인덱스/조건을 정리해 락 범위를 줄이는 쪽이 정공법입니다.
EXPLAIN UPDATE orders
SET status = 'PAID'
WHERE user_id = 123 AND created_at >= '2026-01-01';
type이 ALL에 가깝거나, rows 추정치가 과도하면 “락을 크게 잡을 가능성”도 커집니다.
운영에서 바로 쓰는 개선 패턴 6가지
1) 트랜잭션을 짧게: 잠금 보유 시간을 줄이기
- 트랜잭션 안에서 외부 API 호출, 파일 IO, 긴 비즈니스 로직을 수행하지 않기
- 꼭 필요하면 “락을 잡는 구간”만 최소화
2) 동일 자원은 항상 같은 순서로 락 잡기
앞서 말한 것처럼, 데드락의 고전적인 해법입니다.
3) 배치/대량 갱신은 청크 처리
한 번에 수만 건을 갱신하면 락 경합이 폭발합니다.
-- 예: PK 범위로 쪼개서 처리(애플리케이션에서 반복 실행)
UPDATE orders
SET status = 'ARCHIVED'
WHERE id BETWEEN 1 AND 10000;
4) 필요한 경우에만 SELECT ... FOR UPDATE
FOR UPDATE는 강력하지만, 범위를 잘못 잡으면 데드락 제조기가 됩니다. 정말 “선점 락”이 필요한지부터 검토하세요.
5) 데드락 재시도는 “증상 완화”일 뿐, 지표로 관리
재시도는 필요하지만, 재시도 횟수가 늘면 DB 부하가 더 커집니다.
- 데드락 발생 횟수
- 재시도 성공률
- 평균 재시도 횟수
를 지표로 두고, 증가 추세면 원인 제거를 우선하세요.
6) 애플리케이션 스레딩/동시성 모델도 함께 점검
동시성이 커지면 데드락 확률이 기하급수로 늘어납니다. 특히 가상 스레드나 비동기 처리 도입 이후 “동시에 같은 테이블을 더 자주 건드리게” 되는 경우가 있습니다. 관련해서는 Spring Boot 3 가상스레드 도입 후 Deadlock·TPS 저하 진단도 같이 보면 원인 범위를 좁히는 데 도움이 됩니다.
데드락 로그 수집 자동화(추천)
사건이 덮이기 전에 잡아야 합니다. 가장 현실적인 방법은 다음 중 하나입니다.
- MySQL에서
innodb_print_all_deadlocks를 켜고 에러 로그를 중앙 수집(예: CloudWatch, ELK) - 애플리케이션에서 예외 발생 시점에 추가 정보를 남김
- 트랜잭션 ID는 직접 얻기 어렵지만, 최소한 “비즈니스 키(주문번호 등)”와 “요청 ID”를 남기면 로그 상관관계가 생깁니다.
또한 복제 환경에서는 데드락이 직접적인 복제 지연으로 이어지기도 합니다(락 대기 증가, 쿼리 지연, 리플리카 apply 지연). 데드락이 잦고 Seconds_Behind_Master가 튄다면 MySQL 복제 지연(Seconds_Behind_Master) 0으로 만드는 실전 가이드도 함께 점검해보세요.
체크리스트: 로그를 봤는데도 원인이 안 보일 때
- 로그에 찍힌 SQL이 너무 짧거나 추상적이다
- Prepared Statement일 수 있음: 애플리케이션 SQL 로깅/슬로우 로그로 원문 확보
- 항상 같은 테이블인데도 데드락 패턴이 다양하다
- 인덱스 부재/비효율로 스캔 범위가 흔들리는지 확인
- 데드락은 적은데 장애가 난다
- 데드락 자체보다 락 대기/커넥션 풀 고갈이 본체일 수 있음
마무리
InnoDB 데드락 로그는 “DB가 알아서 하나 롤백했으니 끝”이 아니라, 락 충돌의 구조를 그대로 드러내는 포렌식 자료입니다. TRANSACTION (1)/(2)의 쿼리, WAITING FOR와 HOLDS의 연결, 인덱스/락 모드만 제대로 읽어도 범인 쿼리를 높은 확률로 특정할 수 있습니다.
정리하면,
- 데드락은 보통 “락 획득 순서 불일치” 또는 “락 범위가 과도함”에서 온다
SHOW ENGINE INNODB STATUS\G의 데드락 섹션은 쿼리와 락 정보를 함께 준다- 롤백된 트랜잭션이 범인이라는 단정은 금물이며, 락 보유 시간/순서를 중심으로 봐야 한다
다음 단계로는, 실제 데드락 로그 한 건을 가져와서(민감 정보 마스킹 후) 트랜잭션별로 락 그래프를 그려보면 팀 내 공유와 개선이 훨씬 빨라집니다.