beautifulsoups.dev
Published on

MySQL Deadlock 1213 로그로 원인 찾고 재현하기

Authors

운영에서 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction가 뜨면, 대부분은 “재시도하면 되겠지”로 끝나기 쉽습니다. 하지만 deadlock은 우연이 아니라 락 획득 순서가 꼬이는 구조적 패턴에서 반복됩니다. 재시도로 증상은 완화되지만, 트래픽이 커질수록 재시도 폭증, 지연 증가, 커넥션 점유로 이어져 장애로 번질 수 있습니다.

이 글은 다음을 목표로 합니다.

  • MySQL 1213 deadlock을 로그(진단 정보)로 원인 규명하는 방법
  • 최소한의 SQL로 동일 패턴을 재현하는 방법
  • 재현 결과를 바탕으로 쿼리/인덱스/트랜잭션 설계를 수정하는 체크리스트

중간에 OS 리소스 이슈(커넥션 폭증, 파일 디스크립터 부족)로 증상이 더 커질 수 있으니, 필요하면 Linux EMFILE(Too many open files) 원인과 해결도 함께 점검하세요.

1) Deadlock 1213의 본질: “서로가 가진 락을 서로가 기다림”

InnoDB는 트랜잭션 격리와 일관성을 위해 다양한 락을 잡습니다.

  • Record lock: 인덱스 레코드 단위 락
  • Gap lock / Next-key lock: 범위 조건에서 팬텀 방지용 락(주로 REPEATABLE READ에서)
  • Insert intention lock: 삽입 시 인덱스 갭에 대한 의도 락

Deadlock은 보통 아래 중 하나로 발생합니다.

  1. 서로 다른 트랜잭션이 서로 다른 순서로 동일 자원(행/인덱스 범위) 을 잠금
  2. 인덱스가 부적절해 범위가 넓어져 예상보다 많은 레코드/갭을 잠금
  3. SELECT ... FOR UPDATE 같은 잠금 읽기와 UPDATE/INSERT가 섞여 락 호환이 깨짐

핵심은 “어떤 락을 어떤 순서로 잡았는지”를 알아내는 것입니다.

2) 원인 파악의 시작점: InnoDB deadlock 로그 확보

2.1 즉시 확인: SHOW ENGINE INNODB STATUS

Deadlock이 발생한 직후라면, 아래로 가장 최근 deadlock 정보를 볼 수 있습니다.

SHOW ENGINE INNODB STATUS\G

출력에서 LATEST DETECTED DEADLOCK 섹션을 찾습니다. 여기에는 보통 다음이 포함됩니다.

  • 트랜잭션 ID, 스레드 ID
  • 어떤 SQL을 실행했는지
  • 어떤 인덱스의 어떤 레코드/갭을 잠그려 했는지

주의: 이 정보는 가장 최근 1건만 유지되는 성격이라, 빈번한 deadlock 환경에서는 금방 덮어써집니다.

2.2 운영에서 지속 수집: deadlock 로깅 활성화

운영에서 재현이 어렵고 간헐적이면, MySQL 에러 로그로 deadlock 정보를 남기는 게 좋습니다.

SET GLOBAL innodb_print_all_deadlocks = ON;
  • 활성화하면 deadlock 발생 시마다 에러 로그에 상세 정보가 남습니다.
  • 단, 로그가 많아질 수 있으니 로테이션 정책도 함께 고려합니다.

에러 로그 위치는 환경마다 다르지만 보통 아래로 확인합니다.

SHOW VARIABLES LIKE 'log_error';

3) Deadlock 로그 읽는 법: “어떤 인덱스의 어떤 락을 잡았나”

LATEST DETECTED DEADLOCK에서 특히 중요한 부분은 다음입니다.

  • TRANSACTION 블록: 트랜잭션이 실행 중인 SQL과 상태
  • WAITING FOR THIS LOCK TO BE GRANTED: 지금 기다리는 락
  • HOLDS THE LOCK(S): 이미 잡고 있는 락
  • RECORD LOCKS ... index ... 라인: 어떤 인덱스에서 락이 걸렸는지

여기서 실무적으로는 아래 질문에 답하면 됩니다.

  1. 두 트랜잭션의 SQL은 각각 무엇인가
  2. 두 SQL이 공통으로 만지는 테이블/인덱스는 무엇인가
  3. 락이 걸린 인덱스가 PK인지, 보조 인덱스인지
  4. 조건절이 범위인지(예: BETWEEN, \u003e=, LIKE 'prefix%') 정확 매칭인지
  5. 인덱스가 없거나 비효율적이라 범위 락이 커진 것은 아닌지

RECORD LOCKS ... index에서 index명이 보조 인덱스라면, “업데이트는 PK로 했는데 왜 보조 인덱스에서 락이 걸리지” 같은 의문이 생길 수 있습니다. InnoDB는 보조 인덱스 유지/검증 과정에서도 락이 필요합니다. 즉, 업데이트 대상 컬럼이 보조 인덱스에 포함되거나, 조건절이 보조 인덱스로 탐색되면 보조 인덱스 레코드 락이 핵심이 될 수 있습니다.

4) 재현이 90%다: 가장 흔한 deadlock 패턴을 SQL로 만들기

아래는 “서로 다른 순서로 두 행을 업데이트”하는 전형적인 deadlock 재현입니다.

4.1 재현용 테이블

DROP TABLE IF EXISTS accounts;

CREATE TABLE accounts (
  id BIGINT PRIMARY KEY,
  balance BIGINT NOT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 1000);

4.2 세션 2개로 교차 업데이트

세션 A:

SET autocommit = 0;
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 여기서 멈추고 세션 B 실행
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

세션 B:

SET autocommit = 0;
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 2;
-- 여기서 세션 A의 두 번째 UPDATE 실행
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;

결과적으로 A는 id=1을 잡고 id=2를 기다리며, B는 id=2를 잡고 id=1을 기다립니다. InnoDB는 둘 중 하나를 희생자(victim)로 골라 롤백시키고 1213을 발생시킵니다.

이 재현은 단순하지만, 운영에서 자주 보는 “두 리소스를 동시에 잠그는 로직(예: 송금, 재고 이동, 좌석 예약)”의 축소판입니다.

5) 실제 원인 추적 절차: 로그에서 재현 쿼리로 역추적

운영 deadlock 로그를 받았다고 가정하면, 다음 순서로 진행하면 효율적입니다.

5.1 로그에서 SQL 원문과 테이블, 인덱스명 추출

  • 트랜잭션 1의 SQL
  • 트랜잭션 2의 SQL
  • 락이 걸린 테이블
  • index 이름

이 4가지를 뽑으면, 재현 환경에서 최소 케이스를 만들 수 있습니다.

5.2 실행 계획과 인덱스 적합성 확인

각 SQL에 대해 아래를 확인합니다.

EXPLAIN FORMAT=TRADITIONAL
SELECT ...;

EXPLAIN FORMAT=TRADITIONAL
UPDATE ...;

포인트:

  • type이 range 또는 ALL이면 락 범위가 커질 가능성이 큼
  • key가 NULL이면 사실상 풀스캔으로 많은 레코드를 잠글 수 있음
  • rows 추정치가 큰데 FOR UPDATE 또는 UPDATE면 위험

5.3 격리 수준에 따른 갭/넥스트키 락 영향 점검

기본 격리 수준이 REPEATABLE READ인 경우, 범위 조건에서 갭/넥스트키 락이 더 적극적으로 걸립니다.

SELECT @@transaction_isolation;
  • 단순히 격리 수준을 낮추는 건 부작용이 큽니다.
  • 하지만 “왜 범위 조건에서 잠금이 넓게 걸렸는가”를 이해하는 데 중요합니다.

6) 재현을 더 현실적으로 만드는 방법

운영 deadlock은 단순히 두 UPDATE가 아니라, 다음 요소가 섞여 발생합니다.

  • 트랜잭션 내부에 여러 쿼리
  • 인덱스 미스/범위 조건
  • 동일 테이블에 대한 INSERT와 UPDATE 혼합

6.1 SELECT ... FOR UPDATE를 섞어보기

예: 주문 처리에서 “상태 확인 후 업데이트” 패턴

START TRANSACTION;

SELECT id, status
FROM orders
WHERE id = 10
FOR UPDATE;

UPDATE orders
SET status = 'PAID'
WHERE id = 10;

COMMIT;

여기에 다른 트랜잭션이 같은 주문을 다른 순서로 잠그면 deadlock이 발생할 수 있습니다.

6.2 범위 업데이트로 락 범위 확대

UPDATE coupons
SET used = 1
WHERE user_id = 100
  AND used = 0
  AND expires_at >= NOW();

위 쿼리는 인덱스가 부적절하면 user_id=100 범위 전체를 넓게 잠글 수 있습니다. deadlock 로그에서 index가 의도치 않은 보조 인덱스로 찍히면, 인덱스 설계부터 의심해야 합니다.

7) 해결 전략: “재시도”는 마지막이 아니라 기본 안전장치

7.1 애플리케이션 레벨: 재시도는 필수, 하지만 제한적으로

Deadlock은 DB가 의도적으로 한 트랜잭션을 죽여 전체 정합성을 지키는 메커니즘입니다. 따라서 애플리케이션은 1213을 정상적인 일시 오류로 보고 재시도해야 합니다.

권장:

  • 재시도 횟수 2~3회
  • 지수 백오프(예: 20ms, 50ms, 100ms)
  • 멱등성 보장(특히 결제/차감 로직)

7.2 트랜잭션 설계: 락 획득 순서를 통일

가장 강력한 해결책은 “항상 같은 순서로 잠그기”입니다.

예: 두 계정을 잠글 때

  • 항상 작은 id부터 잠금
-- 예시: 애플리케이션에서 from_id, to_id 정렬 후 처리
START TRANSACTION;

SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

이렇게 하면 교차 순서가 사라져 deadlock 가능성이 크게 줄어듭니다.

7.3 인덱스: “잠그는 범위를 줄이는” 방향으로

deadlock 로그에서 보조 인덱스/범위 락이 핵심이면, 인덱스로 잠금 범위를 줄이세요.

예:

  • 조건절이 (user_id, used, expires_at)를 자주 쓰면 복합 인덱스 고려
  • 업데이트 대상과 조건절 컬럼이 섞이면, 어떤 인덱스를 타는지 EXPLAIN으로 확인
CREATE INDEX idx_coupons_user_used_exp
ON coupons (user_id, used, expires_at);

주의: 인덱스는 쓰기 비용도 증가시키므로, “재현 케이스에서 락 범위가 줄어드는지”를 먼저 확인하고 적용하는 것이 안전합니다.

7.4 쿼리 패턴: 큰 트랜잭션을 쪼개고, 불필요한 잠금 읽기를 제거

  • 트랜잭션 내부에서 외부 API 호출, 긴 비즈니스 로직 수행 금지
  • 꼭 필요하지 않다면 FOR UPDATE를 제거하거나 범위를 축소
  • 배치성 업데이트는 PK 범위로 쪼개기

8) 운영 점검 체크리스트

마지막으로, deadlock을 “원인 규명 → 재현 → 수정” 사이클로 닫기 위한 체크리스트입니다.

  • innodb_print_all_deadlocks로 에러 로그에 deadlock을 남기고 있는가
  • deadlock 로그에서 두 트랜잭션의 SQL과 index명을 확보했는가
  • 각 SQL의 EXPLAIN을 확인했고, 풀스캔/광범위 range가 아닌가
  • 동일 자원을 잠그는 로직에서 락 획득 순서를 통일했는가
  • 필요한 인덱스를 추가해 잠금 범위를 줄였는가
  • 애플리케이션에서 1213 재시도가 멱등하게 구현되어 있는가
  • deadlock 증가 시 커넥션/스레드/FD 고갈로 연쇄 장애가 나지 않게 리소스 상한을 점검했는가(필요 시 리눅스 Too many open files 해결 - ulimit·systemd·Nginx 참고)

9) 마무리: 로그는 “사후 분석용”이 아니라 “재현 스크립트의 재료”

MySQL 1213 deadlock은 피할 수 없는 경우도 있지만, 대부분은 락 순서 불일치와 과도한 잠금 범위에서 반복됩니다. InnoDB deadlock 로그를 읽어 “어떤 인덱스에서 무엇을 기다렸는지”를 뽑아내고, 그 정보를 기반으로 최소 재현 SQL을 만든 뒤, 인덱스/쿼리/트랜잭션 순서를 조정하면 재발률을 크게 낮출 수 있습니다.

다음 단계로는, 실제 운영 로그 한 건을 예시로 두 트랜잭션을 재구성하고(테이블 스키마, 인덱스 포함) 재현 스크립트를 만드는 방식으로 팀 내 런북을 만들어두면, 비슷한 deadlock이 들어왔을 때 대응 속도가 압도적으로 빨라집니다.

Discuss on Twitter • View on GitHub