Lock wait timeout exceeded와 Deadlock found는 다른 문제라는 것

문제 발생

부하가 몰릴 때 두 오류가 번갈아 나왔습니다.

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

둘 다 "재시도하라"는 문구가 붙어 있어서 같은 문제로 보고 재시도만 넣었는데, 1205는 계속 났습니다.

원인 분석

메시지는 비슷하지만 상황이 다릅니다.

1213 (ER_LOCK_DEADLOCK, SQLSTATE 40001) — 두 트랜잭션이 서로가 가진 잠금을 기다리는 순환이 생겼습니다. 이대로면 영원히 안 끝나므로 InnoDB가 감지해서 한쪽을 희생시킵니다. 원인은 대체로 트랜잭션마다 행을 잠그는 순서가 다르기 때문입니다. 즉시 감지되므로 보통 빠르게 실패합니다.

1205 (ER_LOCK_WAIT_TIMEOUT, SQLSTATE HY000) — 순환은 없습니다. 그냥 누군가 잠금을 오래 쥐고 있어서 기다리다 제한 시간(innodb_lock_wait_timeout, 기본 50초)이 지난 것입니다. 원인은 상대 트랜잭션이 너무 길다는 쪽입니다 — 외부 API 호출이 트랜잭션 안에 있거나, 커밋을 잊었거나, 배치가 한 번에 너무 많은 행을 잠근 경우입니다.

그래서 재시도의 효과가 다릅니다. 1213은 재시도하면 순서가 달라져 대개 성공하지만, 1205는 오래 잡고 있는 쪽을 고치지 않으면 재시도해도 다시 기다리다 실패합니다.

해결 방안

1. 1213은 잠금 순서를 통일합니다. 여러 행을 갱신할 때 항상 같은 기준(예: id 오름차순)으로 정렬해 접근하면 순환이 생기지 않습니다.

UPDATE accounts SET balance = balance - ? WHERE id = ? ORDER BY id;

2. 1205는 트랜잭션을 짧게 만듭니다. 이게 핵심입니다.
   • 외부 API 호출, 파일 업로드, 사용자 확인 대기를 트랜잭션 밖으로 뺍니다.
   • 읽기만 하는 조회를 트랜잭션 안에 오래 두지 않습니다.
   • 큰 배치는 청크로 나눠 커밋합니다.
3. 누가 잡고 있는지 확인합니다. 추측하지 말고 실제로 봅니다.

SELECT * FROM performance_schema.data_lock_waits;
SHOW ENGINE INNODB STATUS;   -- LATEST DETECTED DEADLOCK 절

4. 재시도는 두 오류 모두에 넣되 지수 백오프를 씁니다. 즉시 재시도는 같은 충돌을 반복합니다. 재시도 가능한 오류인지는 SQLSTATE로 구분할 수 있습니다.
5. 타임아웃 값을 늘려서 해결하지 않습니다. innodb_lock_wait_timeout을 키우면 오류는 줄지만 요청이 그만큼 오래 매달립니다. 증상만 뒤로 미루는 조치입니다.
6. 불필요한 잠금을 만들지 않습니다. 인덱스가 없으면 InnoDB가 넓은 범위를 잠급니다 — 조건 컬럼에 인덱스가 있는지 확인하는 것만으로 경합이 크게 줄기도 합니다.

공식 문서

• MySQL: 서버 오류 참조 — ER_LOCK_WAIT_TIMEOUT / ER_LOCK_DEADLOCK
• MySQL: 데드락 다루기
• MySQL: innodb_lock_wait_timeout