beautifulsoups.dev
Published on

MySQL Deadlock 1213 재현·로그·인덱스로 해결

Authors

서버가 멀쩡한데 간헐적으로 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction이 터지면, 대부분은 “재시도하면 되지”로 끝나기 쉽습니다. 하지만 재시도만으로는 동일 패턴의 데드락이 계속 발생하고, 트래픽이 늘면 재시도 폭탄이 되어 DB 부하를 더 키웁니다.

이 글에서는 InnoDB 데드락을 (1) 확실히 재현하고, (2) 어떤 로그를 어디서 봐야 하는지 정리한 뒤, (3) 인덱스와 쿼리/트랜잭션 순서로 해결하는 과정을 단계별로 다룹니다. 운영 환경에서 1213을 “운으로” 잡지 않도록, 재현 가능한 형태로 원인을 고정시키는 것이 목표입니다.

> 참고: 장애 상황에서 로그/진단을 빠르게 좁히는 접근은 인프라 이슈에서도 동일합니다. 예를 들어 EKS에서 504가 나는데 Pod는 정상일 때도 ‘관측 지점’을 먼저 고정합니다: EKS ALB Ingress 504인데 Pod는 정상일 때

1) Deadlock 1213의 본질: “서로가 서로를 기다리는 사이클”

InnoDB 데드락은 단순히 락이 오래 걸리는 것(락 대기)과 다릅니다.

  • 락 대기(lock wait): A가 가진 락을 B가 기다림. A가 끝나면 B가 진행.
  • 데드락(deadlock): A가 B를 기다리고, B가 A를 기다림. 사이클이 생겨 영원히 진행 불가.

InnoDB는 이를 감지하면 한 트랜잭션을 희생(victim)시켜 롤백하고, 나머지를 살립니다. 희생된 쪽이 흔히 보는 1213 에러를 받습니다.

자주 나오는 원인 패턴

  • 서로 다른 순서로 동일 자원(행/범위)을 잠금
    • T1: row A → row B
    • T2: row B → row A
  • 인덱스 부재로 인해 잠금 범위가 커짐(갭락/넥스트키락 포함)
  • SELECT ... FOR UPDATE / UPDATE ... WHERE ...가 넓은 범위를 스캔
  • 외래키/보조 인덱스 업데이트로 잠금 대상이 늘어남

2) 데드락 재현: 2세션으로 100% 만들기

먼저 재현 가능한 최소 예제를 만듭니다. 아래는 “서로 다른 순서로 두 행을 업데이트”하는 고전적인 데드락입니다.

2.1 테이블 준비

DROP TABLE IF EXISTS accounts;

CREATE TABLE accounts (
  id BIGINT PRIMARY KEY,
  balance INT 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);

2.2 세션 A

-- Session A
SET autocommit = 0;
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 여기서 잠깐 멈춰두고 Session B를 실행
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

2.3 세션 B

-- Session B
SET autocommit = 0;
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 2;
-- Session A가 두 번째 UPDATE를 실행하도록 유도
UPDATE accounts SET balance = balance + 100 WHERE id = 1;

COMMIT;

실행 타이밍을 맞추면 둘 중 하나가 1213으로 롤백됩니다.

왜 이게 데드락인가?

  • Session A: id=1 행 락 보유 → id=2 행 락을 기다림
  • Session B: id=2 행 락 보유 → id=1 행 락을 기다림

서로가 서로를 기다리는 사이클이므로 InnoDB가 감지하고 한쪽을 죽입니다.

3) 데드락 로그 확인: “어떤 락을 왜 잡았는지” 읽기

재현이 되면 이제 로그입니다. 운영에서 가장 중요한 건 “데드락이 났다는 사실”이 아니라 “어떤 쿼리가 어떤 인덱스/레코드를 잡고 있었는지”입니다.

3.1 즉시 확인: SHOW ENGINE INNODB STATUS

SHOW ENGINE INNODB STATUS\G

출력에서 다음 섹션을 찾습니다.

  • LATEST DETECTED DEADLOCK

여기에는 보통 다음 정보가 들어 있습니다.

  • 각 트랜잭션의 SQL
  • 어떤 인덱스의 어떤 레코드(또는 갭)를 잠그려 했는지
  • 어떤 락 모드였는지 (X, S, gap, next-key 등)

> 팁: 이 출력은 “마지막 데드락 1건” 중심이라, 빈번한 데드락을 장기 분석하기엔 부족합니다.

3.2 서버 로그로 남기기: innodb_print_all_deadlocks

운영에서 데드락을 “사후”에 분석하려면 서버 로그에 남기는 옵션이 유용합니다.

-- 현재 값 확인
SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';

-- 동적으로 켜기(권한 필요)
SET GLOBAL innodb_print_all_deadlocks = ON;

이 옵션을 켜면 데드락이 발생할 때마다 에러 로그(MySQL error log)에 기록됩니다.

  • 장점: 사후 분석 가능
  • 단점: 로그가 많아질 수 있음(고빈도 환경에서는 샘플링/집계가 필요)

3.3 “데드락은 아닌데 기다린다”를 구분: Performance Schema

1213은 데드락이지만, 운영에서는 **락 대기(timeouts)**와 섞여 보입니다.

  • 데드락: 1213
  • 락 대기 타임아웃: ERROR 1205 (HY000): Lock wait timeout exceeded

MySQL 8에서는 Performance Schema로 락 대기를 더 구조적으로 볼 수 있습니다(환경에 따라 테이블/설정이 필요).

-- 대기 이벤트(락 포함) 개괄
SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'wait/lock/%'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

운영에서 “DB가 느리다”가 들어오면, 애플리케이션/프록시/네트워크/DB 중 어디서 병목이 생겼는지 관측 지점을 먼저 고정해야 합니다. HTTP 레벨에서도 비슷한 방식으로 접근합니다: LLM SSE 스트리밍 499 502 급증과 응답 끊김을 잡는 프록시 튜닝 체크리스트

4) 인덱스로 해결: “락 범위를 줄여 데드락 확률을 낮추기”

데드락을 완전히 0으로 만드는 건 현실적으로 어렵습니다(특히 경쟁이 높은 쓰기 워크로드). 대신 목표는 다음입니다.

  1. 락을 잡는 시간(트랜잭션 지속시간)을 줄이고
  2. 락을 잡는 범위(스캔 범위/갭)를 줄이고
  3. 락을 잡는 순서를 일관되게 만들어
  4. 결과적으로 데드락 발생 확률을 낮추는 것

이 중 인덱스는 “범위 축소”에 가장 직접적입니다.

4.1 인덱스가 없으면 왜 데드락이 늘까?

예를 들어 아래 쿼리가 있다고 합시다.

UPDATE orders
SET status = 'PAID'
WHERE user_id = 123 AND status = 'CREATED';

만약 (user_id, status) 인덱스가 없다면 InnoDB는 조건을 만족하는 행을 찾기 위해 더 넓게 스캔합니다. 이때 격리 수준(기본 REPEATABLE READ)에서는 넥스트 키 락(next-key lock) 때문에 레코드뿐 아니라 “범위”까지 잠그는 상황이 생길 수 있어 경쟁이 커집니다.

4.2 해결: 조건절에 맞는 복합 인덱스 추가

CREATE INDEX idx_orders_user_status ON orders(user_id, status);

인덱스가 생기면

  • 스캔 범위 감소
  • 잠금 대상 감소
  • 잠금 유지 시간 감소(덜 읽고 빨리 끝남)

로 이어져 데드락 확률이 줄어듭니다.

4.3 EXPLAIN으로 “인덱스를 타는지” 확인

EXPLAIN UPDATE orders
SET status = 'PAID'
WHERE user_id = 123 AND status = 'CREATED';

확인 포인트:

  • type이 ALL(풀스캔)인지
  • key가 원하는 인덱스인지
  • rows 추정치가 과도하게 큰지

인덱스를 추가했는데도 옵티마이저가 안 타면, 통계/카디널리티/조건 형태(함수 사용 등)를 점검해야 합니다.

5) 쿼리/트랜잭션 설계로 해결: “락 순서와 범위를 제어”

인덱스만으로 충분하지 않을 때가 많습니다. 특히 데드락은 “순서” 문제로도 자주 발생합니다.

5.1 업데이트 순서를 강제해 데드락 제거

앞서 재현한 예제는 두 행을 서로 반대 순서로 잡아서 생겼습니다. 해결은 간단합니다.

  • 항상 작은 id부터 잠근다 같은 규칙을 만들고
  • 트랜잭션 내에서 그 규칙을 지키게 합니다.

예시(두 계좌 이체):

-- from_id, to_id가 어떤 값이든 항상 작은 id부터 잠그기
START TRANSACTION;

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

-- 이제 안전하게 업데이트
UPDATE accounts SET balance = balance - ? WHERE id = ?;
UPDATE accounts SET balance = balance + ? WHERE id = ?;

COMMIT;

SELECT ... FOR UPDATE로 잠금 획득 순서를 통일하면, 동일 자원을 두고 경쟁해도 사이클이 생기기 어렵습니다.

5.2 트랜잭션을 짧게: “락을 오래 쥐지 마라”

실무에서 흔한 실수:

  • 트랜잭션 시작
  • 외부 API 호출
  • 여러 테이블 읽기/쓰기
  • 커밋

이러면 락을 쥔 채로 네트워크 대기까지 포함되어 데드락/락 대기가 급증합니다.

원칙:

  • 트랜잭션은 DB 작업만 포함
  • 필요한 데이터는 트랜잭션 밖에서 준비
  • 쓰기 작업은 가능한 한 한 번에, 짧게

5.3 격리 수준과 갭락: 필요한 경우에만 조정

MySQL(InnoDB) 기본 격리 수준은 REPEATABLE READ이고, 이때 범위 조건에서 갭락/넥스트키락으로 경쟁이 커질 수 있습니다.

일부 워크로드에서는 READ COMMITTED로 낮추면 불필요한 갭락이 줄어 데드락이 완화되기도 합니다. 다만 이는 정합성/팬텀 리드 허용 여부와 맞물리므로 무조건 처방하면 안 됩니다.

-- 세션 단위로 테스트
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

운영 적용 전에는 반드시

  • 해당 트랜잭션이 기대하는 읽기 일관성
  • 유니크 제약/업서트 패턴
  • 동일 조건 범위 업데이트

를 함께 검토해야 합니다.

6) 애플리케이션 레벨 대응: “재시도는 하되, 설계가 먼저”

데드락은 완전히 사라지기 어렵기 때문에, 애플리케이션은 재시도 전략을 가져야 합니다. 다만 “재시도만” 있으면 DB를 더 때릴 수 있으니, 다음 원칙을 권합니다.

  • 데드락(1213)만 선별 재시도(다른 에러까지 무차별 재시도 금지)
  • 지수 백오프 + 지터
  • 최대 재시도 횟수 제한
  • 재시도 시 동일 트랜잭션을 그대로 재실행(부분 상태가 남지 않게)

Node.js 예시(의사 코드):

async function withDeadlockRetry(fn, { maxRetries = 3 } = {}) {
  for (let i = 0; i <= maxRetries; i++) {
    try {
      return await fn();
    } catch (e) {
      const isDeadlock = e && (e.code === 'ER_LOCK_DEADLOCK' || e.errno === 1213);
      if (!isDeadlock || i === maxRetries) throw e;

      const base = 50 * Math.pow(2, i);
      const jitter = Math.floor(Math.random() * 50);
      await new Promise(r => setTimeout(r, base + jitter));
    }
  }
}

재시도는 “안전망”이고, 근본은 앞서 다룬 인덱스/락 순서/트랜잭션 축소입니다.

7) 체크리스트: 1213을 줄이기 위한 실전 점검

7.1 로그/관측

  • SHOW ENGINE INNODB STATUS\G에서 LATEST DETECTED DEADLOCK 확인
  • innodb_print_all_deadlocks=ON으로 에러 로그에 누적
  • (가능하면) Performance Schema로 락 대기/핫스팟 테이블 파악

7.2 쿼리/인덱스

  • 데드락 로그에 나온 index/table을 기준으로 EXPLAIN 수행
  • WHERE 조건에 맞는 복합 인덱스 추가
  • 함수/형변환/LIKE '%...%' 등 인덱스 무력화 패턴 제거

7.3 트랜잭션

  • 잠금 획득 순서를 전 코드 경로에서 일관되게
  • 트랜잭션 범위에서 외부 호출/긴 로직 제거
  • 필요한 경우 SELECT ... FOR UPDATE로 선점 잠금 + 정렬

결론

MySQL Deadlock 1213은 “재시도하면 가끔 해결되는 오류”가 아니라, 락 순서/락 범위/트랜잭션 길이가 만들어낸 구조적 결과입니다.

  1. 먼저 2세션으로 재현해 패턴을 고정하고,
  2. LATEST DETECTED DEADLOCK 및 에러 로그로 “어떤 인덱스/레코드에서” 막혔는지 읽고,
  3. 인덱스와 쿼리/트랜잭션 설계를 통해 락 경합을 줄이면,

운영에서 1213은 “가끔 나는 미스터리”가 아니라 “원인-해결이 가능한 이벤트”가 됩니다.

Discuss on Twitter • View on GitHub