beautifulsoups.dev
Published on

MySQL InnoDB Deadlock 원인 쿼리 추적·해결 가이드

Authors

서버가 느려지고 애플리케이션에서는 Deadlock found when trying to get lock; try restarting transaction 예외가 간헐적으로 터지는데, 재현은 어렵고 “가끔”만 발생한다면 대부분 InnoDB 데드락입니다. 데드락 자체는 MySQL이 감지하면 한 트랜잭션을 롤백해 풀어버리므로 장애가 아니라 이벤트로 볼 수도 있지만, 빈도가 늘면 재시도 폭증 → 커넥션 점유 → 응답 지연으로 확장됩니다.

이 글은 (1) 데드락이 왜 생기는지, (2) 어떤 쿼리가 데드락을 만들었는지 추적하는 방법, (3) 인덱스/쿼리/트랜잭션 구조로 해결하는 방법을 “운영에서 바로 쓰는” 순서로 정리합니다.

> DB 타임아웃/풀 고갈까지 번질 때는 애플리케이션 레벨의 커넥션/재시도 설정도 함께 점검해야 합니다. 관련해서는 Spring Boot HikariCP 풀 고갈·DB 타임아웃 10분 진단도 같이 보면 흐름이 이어집니다.

Deadlock의 본질: “락 순서”와 “락 범위”

InnoDB 데드락은 보통 아래 두 축에서 만들어집니다.

  • 락 순서(order) 불일치: 트랜잭션 A는 (row1 → row2) 순서로 잠그고, 트랜잭션 B는 (row2 → row1) 순서로 잠그면 서로가 서로를 기다리게 됩니다.
  • 락 범위(range) 과대: 인덱스가 부실하거나 조건이 애매하면 InnoDB가 더 많은 레코드/갭을 잠그며(Next-Key Lock) 예상보다 넓은 범위를 잠급니다.

특히 REPEATABLE READ(MySQL 기본)에서는 팬텀 방지를 위해 Next-Key Lock(레코드 락 + 갭 락) 이 적극적으로 사용되어 데드락이 더 “쉽게” 만들어질 수 있습니다.

1단계: 데드락 로그로 “원인 쿼리”를 먼저 잡는다

1) 즉시 확인: SHOW ENGINE INNODB STATUS

가장 빠른 1회성 확인 방법입니다. 가장 최근 데드락 1건을 보여줍니다.

SHOW ENGINE INNODB STATUS\G

출력에서 핵심은 LATEST DETECTED DEADLOCK 섹션입니다.

  • 각 트랜잭션의 실행 쿼리
  • 어떤 인덱스/레코드를 잡고 있었는지
  • 어떤 락을 기다렸는지(WAITING FOR THIS LOCK)
  • 어떤 트랜잭션이 ROLL BACK 되었는지

운영에서는 이 값이 금방 덮어씌워지므로, 데드락이 발생한 시점에 자동 수집하도록 만드는 게 좋습니다(뒤에서 예시 제공).

2) 운영에서 필수: innodb_print_all_deadlocks

MySQL이 데드락을 감지할 때마다 에러 로그로 남기게 합니다.

SET GLOBAL innodb_print_all_deadlocks = ON;
  • 장점: “최근 1건”이 아니라 발생하는 모든 데드락을 남김
  • 단점: 로그가 늘어날 수 있으니 로테이션/수집 체계를 함께 고려

컨테이너/쿠버네티스 환경이라면 로그 수집 파이프라인(예: Fluent Bit → Elasticsearch/CloudWatch 등)로 데드락 블록을 파싱해 알람을 걸어두면 추적 시간이 급격히 줄어듭니다.

3) 성능 스키마로 보강(가능하면)

MySQL 8.0에서는 performance_schema에서 락/대기 정보를 더 풍부하게 볼 수 있습니다. 환경에 따라 테이블/계측이 꺼져 있을 수 있으니 “가능하면” 활용하세요.

대표적으로 다음 계열이 도움이 됩니다.

  • performance_schema.data_locks
  • performance_schema.data_lock_waits
  • performance_schema.events_statements_history(_long)

예: 현재 락 대기 관계를 보는 쿼리(환경에 따라 컬럼명이 다를 수 있음)

SELECT
  w.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx,
  w.BLOCKING_ENGINE_TRANSACTION_ID   AS blocking_trx,
  dl1.OBJECT_SCHEMA, dl1.OBJECT_NAME, dl1.INDEX_NAME,
  dl1.LOCK_TYPE, dl1.LOCK_MODE, dl1.LOCK_STATUS
FROM performance_schema.data_lock_waits w
JOIN performance_schema.data_locks dl1
  ON w.REQUESTING_ENGINE_LOCK_ID = dl1.ENGINE_LOCK_ID
ORDER BY dl1.OBJECT_SCHEMA, dl1.OBJECT_NAME;

데드락은 “끝난 사건”이라 실시간 대기만으로는 부족할 수 있으니, 에러 로그/INNODB STATUS와 함께 교차검증하는 용도로 쓰는 게 좋습니다.

2단계: 데드락 로그를 읽는 기준(무엇을 봐야 하나)

LATEST DETECTED DEADLOCK을 보면 보통 아래 구조로 나옵니다.

  • *** (1) TRANSACTION:
    • 트랜잭션 ID, 상태, 실행 시간
    • mysql tables in use, lock struct(s)
    • query id 및 실제 SQL
    • HOLDS THE LOCK(S): (이미 잡은 락)
    • WAITING FOR THIS LOCK TO BE GRANTED: (대기 중인 락)
  • *** (2) TRANSACTION:
  • *** WE ROLL BACK TRANSACTION (x)

여기서 원인 쿼리를 특정할 때는 다음 질문에 답하면 됩니다.

  1. 각 트랜잭션이 어떤 인덱스에서 잠겼나? (index/PRIMARY/보조 인덱스)
  2. 레코드 락인지, 갭/넥스트키인지? (locks rec but not gap, locks gap, locks next-key 등)
  3. WHERE 조건이 인덱스를 타는가? (범위가 넓으면 락이 넓어짐)
  4. 두 트랜잭션의 락 획득 순서가 다른가?

대부분의 해결책은 결국 (A) 락 범위를 줄이거나, (B) 락 획득 순서를 통일하거나, (C) 애초에 동시에 잡지 않게(구조 변경) 입니다.

3단계: 자주 나오는 데드락 패턴 4가지와 해결법

패턴 1) 서로 다른 순서로 같은 행들을 UPDATE

예: 주문 아이템 두 행을 서로 반대 순서로 업데이트

-- 트랜잭션 A
START TRANSACTION;
UPDATE order_item SET status='DONE' WHERE id=10;
UPDATE order_item SET status='DONE' WHERE id=20;
COMMIT;

-- 트랜잭션 B
START TRANSACTION;
UPDATE order_item SET status='DONE' WHERE id=20;
UPDATE order_item SET status='DONE' WHERE id=10;
COMMIT;

해결

  • 항상 같은 순서로 잠그게 정렬(예: PK 오름차순)
  • 여러 행을 건드릴 때는 가능하면 단일 쿼리로 합치기
-- 항상 id 오름차순으로 잠그도록 유도
START TRANSACTION;
SELECT id FROM order_item WHERE id IN (10,20) ORDER BY id FOR UPDATE;
UPDATE order_item SET status='DONE' WHERE id IN (10,20);
COMMIT;

SELECT ... FOR UPDATE로 “락을 먼저 선점”하고 순서를 통일하면, 데드락 대신 단순 대기로 바뀌는 경우가 많습니다.

패턴 2) 인덱스 미스/범위 조건으로 Next-Key Lock이 커짐

예: (user_id, created_at)로 조회하며 업데이트하는데, 적절한 인덱스가 없으면 넓은 범위를 잠글 수 있습니다.

-- 인덱스가 부실한 상태에서
UPDATE coupon
SET used = 1
WHERE user_id = 123 AND used = 0
ORDER BY created_at
LIMIT 1;

동시에 여러 요청이 들어오면 “첫 번째 쿠폰”을 찾는 과정에서 범위 잠금이 커지고 충돌이 잦아집니다.

해결

  • 조건을 커버하는 복합 인덱스 설계
  • 불필요한 범위 스캔 제거
CREATE INDEX idx_coupon_user_used_created
ON coupon(user_id, used, created_at, id);

그리고 가능하면 2단계(선정→갱신)로 제어하되, 락 범위를 최소화합니다.

START TRANSACTION;
SELECT id
FROM coupon
WHERE user_id=123 AND used=0
ORDER BY created_at
LIMIT 1
FOR UPDATE;

UPDATE coupon SET used=1 WHERE id = ?;
COMMIT;

패턴 3) 유니크 인덱스/갭 락과 INSERT 경쟁

동일 키로 INSERT가 경쟁하거나, 존재 여부 확인 후 INSERT하는 패턴에서 데드락/락 대기가 생깁니다.

-- 안티 패턴: 존재 확인 후 삽입
START TRANSACTION;
SELECT id FROM user_email WHERE email='a@b.com' FOR UPDATE;
INSERT INTO user_email(email, user_id) VALUES('a@b.com', 10);
COMMIT;

해결

  • 가능한 경우 INSERT ... ON DUPLICATE KEY UPDATE로 단일 문장화
INSERT INTO user_email(email, user_id)
VALUES('a@b.com', 10)
ON DUPLICATE KEY UPDATE user_id = VALUES(user_id);
  • 또는 애플리케이션 레벨에서 “멱등 키”를 두고 중복 요청을 차단

패턴 4) FK(외래키)로 인한 부모/자식 테이블 락 전파

자식 테이블 INSERT/UPDATE 시 부모 테이블의 참조 무결성 체크로 인해 의외의 락 경합이 생길 수 있습니다. 특히 부모 행을 갱신하는 트랜잭션과 자식 삽입 트랜잭션이 엇갈리면 데드락이 날 수 있습니다.

해결

  • 부모/자식 갱신 순서를 통일(항상 부모→자식 또는 자식→부모)
  • FK 컬럼 인덱스 보장(자식 FK 컬럼 인덱스는 사실상 필수)
-- 자식 테이블 FK 컬럼 인덱스
CREATE INDEX idx_child_parent_id ON child(parent_id);

4단계: “원인 쿼리”를 서비스 코드까지 연결하는 방법

데드락 로그에는 보통 SQL 텍스트가 나오지만, OR/M(예: JPA, Hibernate)나 동적 SQL이면 “이게 어느 API 요청이지?”가 문제입니다.

1) 애플리케이션에서 커넥션 세션에 트레이스 태그 심기

요청 ID를 MySQL 세션 변수로 심어두면, 일부 로깅/트레이싱에서 연결고리가 생깁니다.

SET @trace_id = 'req-20260223-abcdef';

또는 커넥션 풀에서 checkout 시점에

SET SESSION application_name = 'order-api';

같은 “세션 메타”를 남길 수 있으면, DB 로그와 앱 로그를 맞추기 쉬워집니다(지원 여부는 드라이버/환경에 따라 다름).

2) 슬로우 로그/일반 로그는 신중히

  • general_log는 매우 무거울 수 있어 운영 상시 ON은 비권장
  • slow_query_log는 deadlock과 직접 동일시할 순 없지만, 락 대기로 인해 느려진 쿼리를 잡는 데 유용
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.2;

락 대기가 길어져 슬로우로 잡히는 쿼리를 보면, 데드락의 “전조”를 찾는 데 도움이 됩니다.

5단계: 해결 전략 체크리스트(우선순위 순)

1) 트랜잭션을 짧게

  • 트랜잭션 안에서 외부 API 호출/파일 IO/긴 계산을 하지 않기
  • 필요한 row만 잠그고 즉시 커밋

2) 락 획득 순서를 통일

  • 여러 테이블/여러 행을 갱신한다면 항상 동일한 순서(테이블 순서, PK 오름차순)
  • 배치/워커/웹 요청이 같은 자원을 만질 때 규칙을 강제

3) 인덱스로 락 범위를 줄이기

  • WHERE 조건에 맞는 인덱스가 없으면 “잠금 범위”가 커져 데드락 확률이 상승
  • 특히 UPDATE ... WHERE non-indexed column은 경고 신호

4) 격리 수준 검토(신중)

REPEATABLE READ → READ COMMITTED로 낮추면 갭 락이 줄어 데드락이 줄어드는 케이스가 있습니다. 다만 애플리케이션이 RR을 전제로 하는 경우(일관 읽기, 특정 로직)에는 부작용이 있을 수 있으니 테이블/세션 단위로 제한적 적용을 고려하세요.

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

5) 재시도는 “필수”지만 무제한은 금지

데드락은 정상적으로도 발생할 수 있으므로 애플리케이션은 재시도를 해야 합니다.

  • 재시도 횟수: 보통 2~3회
  • 지수 백오프 + 지터
  • 동일 요청 중복 실행이 안전하도록 멱등성 확보

예: (의사코드)

for attempt in 1..3:
  try:
    begin
    ...
    commit
    return ok
  except Deadlock:
    rollback
    sleep(backoff(attempt) + jitter)
throw

재시도가 커넥션 풀을 고갈시키며 장애로 커지는 경우가 많으니, 풀/타임아웃/재시도 상호작용은 반드시 함께 봐야 합니다. 이 흐름은 Spring Boot HikariCP 풀 고갈·DB 타임아웃 10분 진단에서 더 자세히 다뤘습니다.

6단계: 데드락을 “재현”해서 고친다(최소 재현 스크립트)

운영에서 잡은 두 쿼리를 바탕으로, 로컬/스테이징에서 두 세션으로 재현하면 해결이 빨라집니다.

예시: 두 세션으로 데드락 재현

세션 1:

CREATE TABLE t (
  id INT PRIMARY KEY,
  v  INT NOT NULL
) ENGINE=InnoDB;

INSERT INTO t VALUES (1, 0), (2, 0);

START TRANSACTION;
UPDATE t SET v=v+1 WHERE id=1;
-- 여기서 잠깐 대기(애플리케이션에서는 로직 수행 구간)
DO SLEEP(2);
UPDATE t SET v=v+1 WHERE id=2;
COMMIT;

세션 2(세션 1이 SLEEP 중일 때 실행):

START TRANSACTION;
UPDATE t SET v=v+1 WHERE id=2;
UPDATE t SET v=v+1 WHERE id=1;
COMMIT;

이런 식으로 “락 순서”가 엇갈리는 구조를 재현한 뒤, 앞서 소개한 정렬된 FOR UPDATE 선점 또는 단일 UPDATE로 바꾸며 데드락이 사라지는지 확인합니다.

7단계: 운영 자동화 팁(데드락 발생 시 즉시 수집)

데드락이 터질 때마다 아래를 자동으로 남기면, 다음 번에는 훨씬 빨리 원인을 찾습니다.

  • 에러 로그의 데드락 블록(innodb_print_all_deadlocks)
  • 발생 시점의 SHOW ENGINE INNODB STATUS 스냅샷
  • (가능하면) performance_schema.data_lock_waits 스냅샷

이런 “사건 발생 시점 스냅샷” 접근은 인프라 문제를 추적할 때도 동일하게 유효합니다. 예를 들어 부팅 실패 원인을 스냅샷/진단 로그로 좁혀가는 방식은 Azure VM 부팅 실패, Boot Diagnostics로 원인 추적·복구와도 결이 같습니다.

결론: 데드락은 없앨 대상이 아니라 ‘통제’할 대상

  • 데드락은 동시성 시스템에서 자연스러운 결과이며, MySQL은 이를 감지해 한쪽을 롤백합니다.
  • 중요한 건 원인 쿼리(락 범위/순서)를 정확히 특정하고,
  • 인덱스/쿼리/트랜잭션 설계로 빈도를 낮춘 뒤,
  • 애플리케이션에서 제한된 재시도 + 백오프로 흡수하는 것입니다.

지금 운영에서 데드락이 문제라면, 먼저 innodb_print_all_deadlocks=ON으로 “증거”를 안정적으로 남기고, LATEST DETECTED DEADLOCK에서 (1) 어떤 인덱스에서 (2) 어떤 락을 (3) 어떤 순서로 잡았는지를 기준으로 리팩터링 우선순위를 잡아보세요.

Discuss on Twitter • View on GitHub