MySQL InnoDB 1205·1213 잠금대기 원인별 해결

서버가 멀쩡한데도 특정 API만 간헐적으로 느려지고, 결국 ERROR 1205 (HY000): Lock wait timeout exceeded 또는 ERROR 1213 (40001): Deadlock found로 터지는 경우가 있습니다. 이 둘은 모두 InnoDB 잠금 경합의 결과지만, 성격과 대응이 다릅니다.

• 1205: “기다렸는데도 잠금이 풀리지 않았다” (타임아웃)
• 1213: “서로가 서로를 기다리는 순환이 생겼다” (데드락) → InnoDB가 한 트랜잭션을 강제로 롤백

이 글은 증상을 ‘원인별’로 쪼개서, 어떤 SQL/패턴이 어떤 오류를 만들고 어떻게 고치면 되는지 실전 기준으로 정리합니다.

> 참고: DB 잠금 문제는 분산 시스템의 중복 처리/재시도 설계와도 연결됩니다. 결제/주문 같은 도메인이라면 MSA 사가 실패로 중복결제 터질 때 Outbox로 막기처럼 “재시도해도 안전한 처리”를 함께 고려하는 게 좋습니다.

1) 1205 vs 1213: 무엇이 다를까?

1205 (Lock wait timeout exceeded)

• 어떤 트랜잭션이 레코드/갭/넥스트키 락 등을 잡고 오래 유지
• 다른 트랜잭션이 해당 락이 풀리길 기다리다가 innodb_lock_wait_timeout을 초과
• 주로 긴 트랜잭션, 인덱스 미사용으로 인한 광범위 락, 대량 업데이트, 외래키 검사에서 많이 발생

1213 (Deadlock found)

• 트랜잭션 A가 가진 락을 B가 기다리고, B가 가진 락을 A가 기다리는 순환 대기
• InnoDB는 감지 후 “피해가 적어 보이는” 트랜잭션 하나를 롤백
• 애플리케이션은 보통 재시도가 필요 (단, 재시도가 안전하도록 설계되어야 함)

2) 즉시 확인할 것: 잠금/대기/데드락 증거 수집

(1) 현재 누가 누구를 막고 있는지 보기

MySQL 8.0 기준으로는 performance_schema가 가장 유용합니다.

-- 대기 중인 락(누가 기다리는지)
SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query,
  l.lock_table,
  l.lock_index,
  l.lock_type,
  l.lock_mode
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id
JOIN information_schema.innodb_trx b ON w.blocking_trx_id = b.trx_id
JOIN information_schema.innodb_locks l ON w.requested_lock_id = l.lock_id;

> 주의: information_schema.innodb_* 뷰는 버전/설정에 따라 비활성화되거나 대체될 수 있습니다. MySQL 8.0에서는 performance_schema.data_locks, performance_schema.data_lock_waits를 쓰는 구성이 더 일반적입니다.

(2) 마지막 데드락 상세 보기

1213은 원인 파악에 SHOW ENGINE INNODB STATUS가 거의 필수입니다.

SHOW ENGINE INNODB STATUS\G

출력 중 LATEST DETECTED DEADLOCK 섹션에:

• 충돌한 두 트랜잭션의 SQL
• 어떤 인덱스/레코드에 어떤 락을 걸었는지
• 어떤 트랜잭션이 롤백되었는지

가 상세히 나옵니다.

(3) “긴 트랜잭션” 찾기

긴 트랜잭션은 1205/1213 모두의 공통 원인입니다.

SELECT
  trx_id,
  trx_mysql_thread_id,
  trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_age_sec,
  trx_state,
  trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

3) 1205 원인별 해결

원인 A: 긴 트랜잭션(커밋/롤백이 늦음)

전형적인 패턴

• 트랜잭션 안에서 외부 API 호출
• 트랜잭션 안에서 대량 루프 처리
• 사용자 입력 대기(웹 요청이 길게 유지)
• SELECT ... FOR UPDATE 후 비즈니스 로직이 오래 걸림

해결

• 트랜잭션 범위를 최소화: “락이 필요한 순간”에만 시작
• 외부 통신/파일 I/O는 트랜잭션 밖으로 이동
• 배치성 업데이트는 작은 청크로 나눠 커밋

-- 나쁜 예: 락 잡아놓고 오래 처리
START TRANSACTION;
SELECT * FROM orders WHERE id = 123 FOR UPDATE;
-- 여기서 외부 결제 승인 API 호출(수 초~수십 초)
UPDATE orders SET status='PAID' WHERE id = 123;
COMMIT;

-- 개선: 결제 승인(외부) -> DB 반영(짧은 트랜잭션)
-- 1) 외부 승인
-- 2) 승인 결과를 짧게 반영
START TRANSACTION;
UPDATE orders SET status='PAID' WHERE id = 123 AND status='PENDING';
COMMIT;

AND status='PENDING' 같은 조건은 **중복 처리 방지(멱등성)**에도 도움이 됩니다.

원인 B: 인덱스 미사용으로 “너무 많은 행”을 잠금

전형적인 패턴

• UPDATE ... WHERE non_indexed_column = ...
• SELECT ... FOR UPDATE가 풀스캔을 유발
• 조인 업데이트에서 드라이빙 테이블 선택이 비효율적

InnoDB는 조건을 만족하는 레코드를 찾는 과정에서 잠금 범위가 커질 수 있습니다(특히 REPEATABLE READ에서 넥스트키 락).

진단

EXPLAIN UPDATE users SET last_login = NOW() WHERE email = 'a@b.com';

• type=ALL(풀스캔) 또는 rows가 과도하면 위험 신호

해결

• 조건절 컬럼에 적절한 인덱스 추가
• 불필요한 범위 조건/함수 적용 제거

CREATE INDEX idx_users_email ON users(email);

UPDATE users
SET last_login = NOW()
WHERE email = 'a@b.com';

원인 C: 갭 락/넥스트키 락으로 인한 대기(특히 REPEATABLE READ)

전형적인 패턴

• 존재 여부 확인 후 INSERT
• 범위 조건으로 SELECT ... FOR UPDATE 수행

-- 세션1
START TRANSACTION;
SELECT * FROM coupons
WHERE code BETWEEN 'A100' AND 'A200'
FOR UPDATE;
-- 범위에 갭/넥스트키 락이 걸릴 수 있음

-- 세션2
INSERT INTO coupons(code) VALUES('A150'); -- 대기/타임아웃 가능

해결 옵션

• 가능하면 “정확히 한 건”을 잠그도록 유니크 키를 사용
• 범위 잠금이 필요 없다면 트랜잭션 격리수준을 조정 검토(신중)
• 애플리케이션 로직을 INSERT ... ON DUPLICATE KEY UPDATE로 단순화

-- code에 UNIQUE 인덱스가 있다고 가정
INSERT INTO coupons(code, used)
VALUES('A150', 0)
ON DUPLICATE KEY UPDATE used = used;

원인 D: 외래키(FK)로 인한 잠금 전파

부모/자식 테이블에 FK가 있으면:

• 부모 삭제/업데이트 시 자식 검사로 인해 자식 인덱스 레코드를 잠글 수 있고
• 자식 INSERT/UPDATE 시 부모 존재 확인으로 부모 레코드를 참조합니다.

해결

• FK 컬럼에 인덱스가 반드시 있어야 함(자식 테이블)
• 대량 삭제는 자식부터 순서대로
• ON DELETE CASCADE가 오히려 락 범위를 키울 수도 있어 워크로드에 맞게 선택

-- 자식 테이블의 FK 컬럼 인덱스 확인/추가
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

원인 E: innodb_lock_wait_timeout만 늘려서 “숨기는” 경우

innodb_lock_wait_timeout을 늘리면 1205는 줄어들 수 있지만, 근본 원인(긴 트랜잭션/인덱스 문제)이 남아 있으면 지연이 더 길어져 장애가 커질 수 있습니다.

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
-- 필요 시 세션 단위로만 조정 권장
SET SESSION innodb_lock_wait_timeout = 10;

운영에서는 “늘리기”보다:

• 트랜잭션 짧게
• 인덱스 맞추기
• 충돌이 잦은 핫스팟 업데이트 구조 변경

이 우선입니다.

4) 1213(데드락) 원인별 해결

원인 A: 서로 다른 순서로 같은 자원을 잠금(가장 흔함)

재현 예시

-- 트랜잭션1
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- 트랜잭션2(반대 순서)
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
COMMIT;

서로가 상대가 잡은 행을 기다리면서 데드락이 발생할 수 있습니다.

해결

• 항상 동일한 순서로 잠그기(예: id 오름차순)

-- 두 계좌 이체 시, 작은 id부터 업데이트
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = LEAST(1,2);
UPDATE accounts SET balance = balance + 100 WHERE id = GREATEST(1,2);
COMMIT;

(실제로는 금액 증감 방향이 달라 위 예시는 단순화되어 있습니다. 핵심은 “락 순서 고정”입니다.)

원인 B: 유니크 인덱스 경합 + 동시 INSERT/UPDATE

INSERT ... ON DUPLICATE KEY UPDATE나 유니크 키를 두고 동시에 업서트할 때, 인덱스 레벨에서 교착이 생길 수 있습니다.

해결

• 충돌이 잦은 키(예: 특정 사용자/상품)에 대해 요청을 직렬화(애플리케이션 레벨)
• 가능하면 “한 행에 모든 것을 몰아넣는” 카운터/집계 테이블을 샤딩
• 재시도 로직 구현(1213은 재시도 가치가 큼)

원인 C: 보조 인덱스/클러스터드 인덱스 락 순서 차이

InnoDB는 업데이트 시:

• 보조 인덱스 엔트리
• 클러스터드(PK) 레코드

등 여러 구조를 건드리며, 트랜잭션마다 접근 경로가 달라지면 교착 가능성이 올라갑니다.

해결

• 업데이트/조회 쿼리의 실행 계획을 안정화(인덱스 정비)
• 불필요한 보조 인덱스 제거(쓰기 비용 및 락 포인트 감소)
• 동일한 조건/인덱스를 타도록 쿼리 통일

원인 D: 대량 배치 작업과 온라인 트래픽의 충돌

배치가 많은 행을 업데이트하면서 온라인 트래픽의 짧은 트랜잭션과 충돌하면 1213/1205 모두 증가합니다.

해결

• 배치를 작은 단위로 쪼개고 커밋
• 오프피크 시간대 실행
• 배치 범위를 PK 기준으로 나누어 예측 가능하게 진행

-- PK 범위로 청크 업데이트
UPDATE events
SET processed = 1
WHERE id > ? AND id <= ?;

5) 애플리케이션 레벨 처방: 재시도, 멱등성, 타임아웃

1213은 “정상적인 상황”으로 보고 재시도 설계

데드락은 완전히 제거하기 어렵고, InnoDB가 감지/해결(롤백)해주기 때문에 재시도가 가장 현실적인 대응입니다.

• 재시도 대상: 1213, 경우에 따라 1205도 제한적으로
• 재시도 횟수: 2~5회 정도(서비스 특성에 따라)
• 백오프: 지터 포함 지수 백오프 권장
• 전제: 같은 요청을 여러 번 실행해도 안전해야 함(멱등성)

(예시) Node.js에서 1213/1205 재시도

import mysql from 'mysql2/promise';

const RETRYABLE = new Set([1213, 1205]);

export async function withRetry(fn, { max = 4 } = {}) {
  let attempt = 0;
  while (true) {
    try {
      return await fn();
    } catch (e) {
      const errno = e?.errno;
      attempt++;
      if (!RETRYABLE.has(errno) || attempt > max) throw e;

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

// 사용 예
// await withRetry(() => conn.execute('UPDATE ...'));

> 재시도는 DB를 더 때릴 수 있으니, “락 순서 고정/인덱스 개선/트랜잭션 축소” 같은 근본 개선과 함께 적용해야 합니다.

6) 운영 체크리스트(원인별 빠른 분기)

증상: 1205가 많다

• 긴 트랜잭션이 있는가? (innodb_trx로 age 확인)
• 인덱스 없이 UPDATE/SELECT FOR UPDATE가 있는가? (EXPLAIN)
• 범위 잠금이 필요한가? (REPEATABLE READ + 범위 조건)
• 배치가 온라인 트래픽을 막고 있나? (청크/오프피크)

증상: 1213이 많다

• 같은 테이블/레코드를 서로 다른 순서로 업데이트하나?
• 업서트 충돌이 잦은 핫키가 있나?
• 쿼리 경로가 제각각(인덱스 혼재)인가?
• 데드락 로그에 반복되는 패턴이 있나? (SHOW ENGINE INNODB STATUS)

7) “고쳐도 또 생기는” 구조적 원인과 개선 방향

잠금 문제는 종종 “DB 설정”이 아니라 데이터 모델/쓰기 패턴에서 옵니다.

• 하나의 행에 카운터를 몰아넣는 구조(핫스팟) → 샤딩/버퍼링/비동기 집계
• 주문/결제처럼 재시도가 필수인 도메인 → 멱등 키, Outbox, 상태 전이(조건부 업데이트)
• 트랜잭션이 길어지는 아키텍처 → 외부 의존성 분리, 트랜잭션 경계 재설계

DB 잠금은 “한 번의 튜닝”으로 끝나기보다, 트래픽이 커질수록 다시 튀어나옵니다. 그래서 재현 가능한 원인 분류 + 관측(락/트랜잭션) + 안전한 재시도의 3박자가 중요합니다.

8) 마무리: 가장 효과가 큰 5가지 처방

1. 트랜잭션을 짧게(외부 호출/긴 로직을 트랜잭션 밖으로)
2. UPDATE/SELECT FOR UPDATE 조건에 인덱스 맞추기
3. 동일 자원은 항상 동일한 순서로 잠그기(데드락 급감)
4. 배치는 청크 처리 + 오프피크 + PK 범위
5. 1213은 재시도(지터 백오프) + 멱등성 보장

원하시면 SHOW ENGINE INNODB STATUS의 데드락 로그를 익명화해서 붙여주시면, 어떤 인덱스/쿼리/락 순서가 문제인지 로그 기반으로 원인까지 짚어 구체적인 수정안을 제안해 드릴게요.