자식 테이블에 단순히 INSERT 하나 했을 뿐인데 한참 멈춰 있다가 Lock wait timeout exceeded 에러가 납니다. 쿼리 자체는 가벼운데 왜 락에 걸릴까요? 외래키(FK)가 있는 테이블에서는 부모 행에 대한 추가 잠금이 조용히 걸리기 때문입니다.
외래키가 거는 락의 정체
InnoDB는 자식 행을 넣거나 바꿀 때, 참조하는 부모 행이 사라지지 않도록 부모 행에 공유 락(shared lock)을 겁니다. 문제는 다른 트랜잭션이 그 부모 행을 수정 중이면 락이 충돌한다는 점입니다.
| 상황 | 거는 락 | 충돌 대상 |
|---|---|---|
자식 INSERT/UPDATE (FK 참조) | 부모 행 공유 락 (S) | 부모 행을 UPDATE/DELETE 중인 트랜잭션 |
부모 UPDATE/DELETE | 부모 행 배타 락 (X) | 그 부모를 참조 중인 자식 INSERT |
ON DELETE CASCADE | 자식 행까지 연쇄 락 | 대량 자식 행 잠금 → 대기 폭증 |
즉 자식 쪽 작업과 부모 쪽 작업이 같은 부모 행을 두고 서로를 기다리면, 한쪽은 락 대기에 빠지거나 데드락이 됩니다.
잠금 대기 진단
대기 중인 트랜잭션이 무엇을 기다리는지부터 봅니다. MySQL 8.0 기준입니다.
SQL
-- 현재 잠금 대기 관계
SELECT * FROM performance_schema.data_lock_waits;
-- 어떤 트랜잭션이 오래 잡고 있는지
SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;
-- 데드락이면 마지막 데드락 기록 확인
SHOW ENGINE INNODB STATUS;
trx_started가 오래된 트랜잭션이 락을 쥔 채 커밋을 안 하고 있다면, 그게 대기의 근원입니다. 애플리케이션이 트랜잭션을 열어둔 채 외부 API를 기다리는 경우가 흔합니다.
해소와 예방
- 트랜잭션을 짧게 — 부모 행을 건드린 트랜잭션은 즉시 커밋합니다. 트랜잭션 안에서 네트워크 호출·사용자 입력 대기를 두지 않습니다.
- 접근 순서 통일 — 모든 코드가 부모 → 자식 순서로 잠그게 맞추면 데드락이 줄어듭니다.
- CASCADE 주의 —
ON DELETE CASCADE로 부모 하나를 지우면 자식 수천 행이 한 번에 잠깁니다. 대량 삭제는 배치로 나눕니다. - 타임아웃 조정 — 임시방편으로
innodb_lock_wait_timeout을 늘릴 수 있지만, 근본 원인(긴 트랜잭션)을 먼저 잡아야 합니다. - 막힌 세션 종료 — 운영 중 급할 때는 원인 트랜잭션을
KILL <thread_id>로 끊습니다.
요점 정리
- 외래키는 부모 행에 자동으로 락을 걸어, 자식·부모 작업이 같은 행에서 충돌한다.
Lock wait timeout의 범인은 대개 커밋 안 한 긴 트랜잭션이다.- 진단은
data_lock_waits와innodb_trx로 대기 관계와 오래된 트랜잭션을 찾는다. - 트랜잭션을 짧게, 접근 순서를 통일,
CASCADE대량 삭제를 배치로 나누는 것이 예방책이다.
외래키 락 충돌을 직접 재현하고 잠금 대기를 풀어보는 실습은 데이터베이스 트랙에서 회원가입 없이 무료로 할 수 있습니다.