- Published on
MySQL InnoDB Deadlock 로그로 원인 SQL 찾기
- Authors
- Name
- 스타차일드
- https://x.com/ETFBITX
서로가 서로의 락을 기다리며 영원히 진행하지 못하는 상태가 데드락(deadlock)입니다. MySQL(InnoDB)은 데드락을 감지하면 한 트랜잭션을 희생(victim)으로 롤백시켜 시스템을 진행시키지만, 운영 입장에서는 **“대체 어떤 SQL이 문제였나?”**가 핵심입니다. 다행히 InnoDB는 데드락 순간의 정보를 로그로 남기며, 이 로그만 제대로 읽을 수 있으면 재현 없이도 원인 SQL을 상당히 높은 확률로 특정할 수 있습니다.
이 글은 SHOW ENGINE INNODB STATUS(또는 error log에 기록된 deadlock)에서 원인 SQL을 찾는 방법, 그리고 로그에 SQL이 잘 안 보일 때의 **보강 수단(Performance Schema, application instrumentation)**까지 한 번에 정리합니다.
> 참고로 애플리케이션 스레드/동시성 이슈가 얽히면 DB 데드락과 증상이 섞여 보일 수 있습니다. JVM/스레드 관점의 진단은 Spring Boot 3 가상스레드 적용 후 지연·데드락 진단도 함께 보면 원인 분리가 쉬워집니다.
1) InnoDB Deadlock 로그를 얻는 3가지 경로
1-1. 즉시 확인: SHOW ENGINE INNODB STATUS
데드락이 발생한 직후라면 가장 먼저 확인합니다. InnoDB는 가장 최근의 데드락 1건을 LATEST DETECTED DEADLOCK 섹션에 남깁니다.
SHOW ENGINE INNODB STATUS\G
주의할 점:
- “최근 1건”만 남습니다. 데드락이 연속 발생하면 이전 기록이 덮입니다.
- 운영에서 수집 자동화를 하지 않으면 놓치기 쉽습니다.
1-2. 에러 로그에 기록: innodb_print_all_deadlocks
운영에서는 이 옵션을 켜두면 좋습니다. 데드락이 발생할 때마다 MySQL error log에 남습니다.
-- 동적으로 가능(버전에 따라 다를 수 있음)
SET GLOBAL innodb_print_all_deadlocks = ON;
- 장점: 누적 기록이 남아 사후 분석이 쉬움
- 단점: 로그가 늘어날 수 있으니 로테이션/보관 정책 필요
1-3. 성능 스키마/모니터링으로 보강
로그만으로 SQL을 못 찾는 경우가 있습니다(예: 프레임워크가 prepared statement를 쓰고, 로그에는 바인드 값이 안 찍히는 등). 이때는 Performance Schema에서 락 대기/트랜잭션/스레드를 함께 보며 역추적합니다.
2) Deadlock 로그에서 “원인 SQL”을 찾는 읽기 순서
InnoDB 데드락 로그는 길고 복잡해 보이지만, 읽는 순서는 정형화할 수 있습니다.
- 희생된 트랜잭션(ROLL BACK TRANSACTION) 확인
- 각 트랜잭션의 thread id / query id / mysql tables in use 확인
WAITING FOR THIS LOCK TO BE GRANTED에 나온 락 대상(테이블/인덱스/레코드) 확인HOLDS THE LOCK(S)에 나온 상대가 잡고 있는 락 확인- 두 트랜잭션이 서로 어떤 순서로 어떤 인덱스 레코드를 잡았는지를 연결
- 마지막으로
query:라인(또는MySQL thread id ... query id ...)에 있는 SQL을 후보로 확정
아래는 전형적인 로그의 축약 형태입니다(실제 로그는 더 길 수 있습니다).
------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 123456, ACTIVE 0 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 1001, OS thread handle 140..., query id 9001 10.0.0.10 app_user updating
UPDATE orders SET status='PAID' WHERE id=42;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 55 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 0 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 1002, OS thread handle 141..., query id 9002 10.0.0.11 app_user updating
UPDATE orders SET status='CANCEL' WHERE id=42;
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 55 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 55 page no 124 n bits 72 index `idx_customer` of table `shop`.`orders`
lock_mode X locks rec but not gap waiting
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 55 page no 124 n bits 72 index `idx_customer` of table `shop`.`orders`
lock_mode X locks rec but not gap
*** WE ROLL BACK TRANSACTION (1)
이 예시는 단순화했지만 핵심 포인트가 다 들어 있습니다.
- (1)은
orders.PRIMARY(id=42)를 기다림 - (2)는
orders.idx_customer(...)를 기다림 - 서로 반대편 인덱스 레코드를 이미 잡고 있어 사이클이 형성
- MySQL이 (1)을 희생시켜 롤백
여기서 원인 SQL은 단순히 “(1)의 UPDATE” 또는 “(2)의 UPDATE”가 아니라, 두 트랜잭션이 락을 잡는 순서가 달라지는 코드 경로입니다. 즉, 같은 테이블을 갱신하되 인덱스 접근 경로가 달라지거나(조건/인덱스/실행계획), 여러 테이블을 다른 순서로 업데이트하는 경우가 흔합니다.
3) 로그에 나오는 락 종류를 “원인 SQL” 관점에서 해석하기
데드락 로그에서 SQL을 찾았어도, 왜 그 SQL이 데드락을 만들었는지 이해해야 재발을 막을 수 있습니다.
3-1. locks rec but not gap vs locks gap vs next-key
locks rec but not gap: 특정 레코드(행)만 잠금locks gap: 레코드 사이의 “갭” 잠금(팬텀 방지)next-key lock: 레코드+갭을 함께 잠금(기본적으로 REPEATABLE READ에서 자주 등장)
특히 범위 조건(WHERE col BETWEEN ..., >, <, LIKE 'prefix%') + 인덱스 사용 조합에서 next-key/gap lock이 늘어나 데드락 빈도가 올라갑니다.
3-2. “같은 행 업데이트인데 왜 데드락?”
단일 행 업데이트는 보통 단순 대기(wait)로 끝나지만, 다음이 섞이면 데드락이 됩니다.
- 보조 인덱스가 여러 개이고, 각 트랜잭션이 서로 다른 인덱스 레코드를 먼저 잠금
- FK(외래키) 검증으로 인해 부모/자식 테이블 락이 추가로 필요
- 트리거/프로시저로 인해 추가 UPDATE/SELECT ... FOR UPDATE가 수행
INSERT ... ON DUPLICATE KEY UPDATE가 서로 다른 유니크 인덱스를 경유
4) “원인 SQL”을 더 정확히 특정하는 실전 절차
4-1. thread id로 세션을 찾는 방법(발생 당시)
데드락이 “지금” 발생 중이라면, 로그의 MySQL thread id로 해당 세션을 찾아 현재 실행 SQL/상태를 확인할 수 있습니다.
-- 전체 프로세스에서 thread id(=processlist id)를 찾아 확인
SHOW FULL PROCESSLIST;
또는 INFORMATION_SCHEMA:
SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE ID IN (1001, 1002);
4-2. Performance Schema로 “어떤 SQL이 어떤 락을 기다렸나” 연결
운영에서 데드락이 자주 나고, SHOW ENGINE INNODB STATUS만으로는 부족하다면 아래를 추천합니다.
- 대기 이벤트/현재 SQL
SELECT
t.PROCESSLIST_ID,
t.PROCESSLIST_USER,
t.PROCESSLIST_HOST,
es.EVENT_NAME,
es.TIMER_WAIT,
es.SQL_TEXT
FROM performance_schema.threads t
JOIN performance_schema.events_statements_current es
ON t.THREAD_ID = es.THREAD_ID
WHERE t.PROCESSLIST_ID IN (1001, 1002);
- InnoDB 락 대기(버전에 따라 테이블 구성은 달라질 수 있음)
-- MySQL 8.0 기준: data_locks / data_lock_waits
SELECT *
FROM performance_schema.data_lock_waits\G
SELECT *
FROM performance_schema.data_locks\G
여기서 핵심은:
BLOCKING_THREAD_ID(락을 쥔 쪽)와REQUESTING_THREAD_ID(기다리는 쪽)- 락이 걸린
OBJECT_SCHEMA/OBJECT_NAME(테이블) - 인덱스/레코드 식별 정보
을 통해 “어떤 SQL이 어떤 테이블/인덱스를 어떤 모드로 잠갔는지”를 연결하는 것입니다.
4-3. Prepared Statement 때문에 SQL이 안 보일 때
프레임워크(JPA, MyBatis 등)는 종종 UPDATE t SET c=? WHERE id=? 형태로만 로그에 남고, 바인드 값이 빠집니다. 이 경우:
- 애플리케이션 쪽에 SQL + bind 값 로깅을 임시로 강화
- DB 쪽에
performance_schema의 statement digest(정규화된 SQL)로 패턴을 찾고, 애플리케이션 로그의 trace id와 매칭
이 조합이 가장 현실적입니다.
5) 데드락 로그로 자주 발견되는 “범인 패턴” 6가지
5-1. 서로 다른 순서로 테이블을 업데이트
- 트랜잭션 A:
UPDATE parent ...후UPDATE child ... - 트랜잭션 B:
UPDATE child ...후UPDATE parent ...
해결: 항상 같은 순서로 락을 잡도록(=같은 순서로 테이블 접근) 코드/쿼리를 정렬합니다.
5-2. 범위 조건 + REPEATABLE READ의 next-key lock
-- 예: 상태가 PENDING인 주문을 여러 워커가 가져가 처리
SELECT id
FROM orders
WHERE status = 'PENDING'
ORDER BY created_at
LIMIT 10
FOR UPDATE;
여러 워커가 동시에 범위를 잠그면 충돌이 커집니다.
해결 후보:
- 적절한 인덱스(
status, created_at)로 잠금 범위를 최소화 - 워커 패턴이면
SKIP LOCKED사용 고려(MySQL 8.0)
SELECT id
FROM orders
WHERE status='PENDING'
ORDER BY created_at
LIMIT 10
FOR UPDATE SKIP LOCKED;
5-3. INSERT ... ON DUPLICATE KEY UPDATE의 교차 유니크 인덱스
서로 다른 유니크 키 충돌이 교차하면 데드락이 생길 수 있습니다.
해결:
- 유니크 키 설계를 단순화
- 가능하면 업서트를 단일 유니크 키 경로로 통일
- 대량 업서트는 배치 크기/정렬을 조정해 동일 키가 한 트랜잭션에 모이게 함
5-4. FK로 인한 숨은 잠금
자식 테이블 INSERT/UPDATE 시 부모 테이블을 참조 검사하면서 공유/레코드 락이 걸릴 수 있습니다.
해결:
- FK 컬럼 인덱스 점검(부모/자식 모두)
- 트랜잭션 내에서 부모/자식 처리 순서 통일
5-5. 보조 인덱스 업데이트로 인한 락 확장
UPDATE가 보조 인덱스 컬럼을 변경하면, InnoDB는 보조 인덱스 엔트리 삭제/삽입까지 수행하며 락이 늘어납니다.
해결:
- 정말 필요한 인덱스만 유지
- 자주 변경되는 컬럼에 인덱스를 남발하지 않기
5-6. 긴 트랜잭션(락 보유 시간 증가)
데드락 자체는 “사이클”이 원인이지만, 락을 오래 쥐고 있으면 사이클이 생길 확률이 폭증합니다.
해결:
- 트랜잭션 범위를 최소화(네트워크 호출/외부 API 호출을 트랜잭션 밖으로)
- 필요한 데이터만 잠그기(불필요한
SELECT ... FOR UPDATE제거)
6) 재발 방지 체크리스트(운영 적용 순서)
innodb_print_all_deadlocks=ON으로 사후 분석 가능하게 만들기- 데드락 로그에서 두 트랜잭션의 SQL을 확보하고, 락 대상 인덱스/레코드를 확인
- 두 SQL이 같은 테이블/인덱스를 서로 다른 순서로 잠그는지 확인
- 실행 계획이 달라지는지(
EXPLAIN) 확인 → 인덱스/조건/통계 영향 점검 - 해결책 적용 우선순위
- 트랜잭션에서 테이블 접근 순서 통일
- 인덱스 정리/추가로 잠금 범위 축소
- 워커 경쟁이면
SKIP LOCKED등 동시성 제어 도입 - 애플리케이션 재시도(Deadlock은 재시도 가능한 오류로 취급)
애플리케이션 재시도 예시(Deadlock 에러코드 1213):
import time
import mysql.connector
MAX_RETRY = 3
def run_tx(conn, fn):
for i in range(MAX_RETRY):
try:
conn.start_transaction()
fn(conn)
conn.commit()
return
except mysql.connector.Error as e:
conn.rollback()
# 1213: Deadlock found when trying to get lock
if e.errno == 1213 and i < MAX_RETRY - 1:
time.sleep(0.05 * (2 ** i))
continue
raise
재시도는 “근본 해결”은 아니지만, 데드락이 드물고 짧은 스파이크라면 사용자 영향도를 크게 줄일 수 있습니다.
7) 마무리: 로그에서 SQL만 찾지 말고 “락 순서”를 찾자
InnoDB deadlock 로그의 목표는 단순히 “어떤 UPDATE가 죽었나”가 아니라, 두 트랜잭션이 어떤 락을 어떤 순서로 잡았는지를 파악하는 것입니다. 로그의 WAITING FOR THIS LOCK / HOLDS THE LOCK를 중심으로 락 대상(테이블/인덱스)을 연결하면, 원인 SQL은 자연스럽게 좁혀집니다.
DB 데드락이 애플리케이션 병목/스레드 정체와 함께 나타날 때는 원인 분리가 특히 중요합니다. 이 경우에는 애플리케이션 측 동시성 진단도 병행하는 것이 효과적이며, 필요하다면 Spring Boot 3 가상스레드 적용 후 지연·데드락 진단의 체크 포인트를 함께 적용해 보세요.