세컨더리 인덱스와 PK 인덱스 간 데드락, 왜 발생할까?
MySQL InnoDB에서 UPDATE, DELETE, SELECT ... FOR UPDATE 등으로 레코드를 잠글 때
PK 인덱스 뿐 아니라 세컨더리 인덱스까지 락이 걸리면서 교착 상태(Deadlock)가 발생할 수 있습니다.
InnoDB는 보조 인덱스를 통해 접근하더라도,
결국 실제 데이터는 클러스터드 PK 인덱스에 저장되어 있기 때문에
세컨더리 인덱스 → PK 순서로 락을 획득하게 됩니다.
만약 서로 다른 트랜잭션이
- 하나는 세컨더리 인덱스 경로로,
- 하나는 PK 경로로
같은 row를 잠그게 되면, 락 획득 순서가 엇갈리며 Deadlock이 발생할 수 있습니다.
MySQL의 락 동작 메커니즘
InnoDB는 Row-Level Lock처럼 보이지만
내부적으로는 인덱스 레코드 기반 잠금 (Record Lock)을 사용합니다.
즉,
- PK 인덱스도 잠금 대상
- 세컨더리 인덱스도 잠금 대상
입니다.
예제 시나리오
CREATE TABLE user_mission (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
mission_id BIGINT NOT NULL,
status TINYINT NOT NULL,
UNIQUE KEY uk_user_mission (user_id, mission_id),
KEY idx_user_status (user_id, status)
) ENGINE=InnoDB;
이번 문제는 다음과 같이 동시 접근이 충분히 발생할 수 있는 상황을 가정합니다:
트랜잭션 A
유저가 미션 완료 버튼을 눌러, 진행중인(IN_PROGRESS) 미션을 잠그고 처리합니다.
BEGIN;
SELECT *
FROM user_mission
WHERE user_id = 100 AND status = 0
FOR UPDATE;
-- → idx_user_status(user_id, status) 보조 인덱스로 탐색
-- → 해당 인덱스 레코드 잠금
-- → 이어서 PK(id=10) 레코드 잠금 시도
-- → (세컨더리 락 → PK 락) 순서로 락 획득
트랜잭션 B
배치나 백오피스에서 해당 미션을 만료 처리하거나 다른 상태로 변경합니다.
BEGIN;
UPDATE user_mission
SET status = 2
WHERE id = 10;
-- → PK(id=10) 레코드 X-lock 획득
-- → status 변경으로 인해 (user_id, status) 인덱스 엔트리 갱신 필요
-- → secondary 인덱스 레코드 잠금 시도
-- → (PK 락 → 세컨더리 락) 순서로 락 획득
이 상태에서 A와 B가 동일한 row를 가리키는 PK를 각자 다른 인덱스 경로로 접근하면, 서로가 상대의 락을 기다리며 데드락이 발생합니다.
내부 락 플로우 분석
| 순서 | A (세컨더리 인덱스 경로) | B (PK 경로) |
|---|---|---|
| 1 | idx_user_status 인덱스 레코드 잠금 (user_id=100, status=0) | PK(id=10) 레코드 잠금 |
| 2 | PK(id=10) 잠금 시도 → 이미 B가 PK 락 보유 | status 변경 → idx_user_status 인덱스 엔트리 갱신 필요 |
| 3 | PK 대기 상태 진입 | Secondary 인덱스 잠금 시도 → A가 보유 |
| 4 | 서로 상대 락을 기다리며 Deadlock 발생 | |
즉, 락 획득 순서 불일치 (Lock Ordering Inconsistency)가 데드락의 핵심 원인입니다.
| 순서 | 트랜잭션 A | 트랜잭션 B |
|---|---|---|
| 1 | idx_user_status 인덱스 레코드 잠금 | PK(id=10) 락 획득 |
| 2 | PK 잠금 시도 → 대기 | status 변경으로 인한 secondary 인덱스 갱신 필요 |
| 3 | 대기 상태 | Secondary 인덱스 잠금 시도 → A가 보유 |
| 4 | 🚨 교착 발생 | 🚨 교착 발생 |
트랜잭션 B는 PK로만 접근했는데 왜 세컨더리 인덱스(idx_user) 락을 기다릴까?
InnoDB는 UPDATE 시 변경되는 컬럼이 세컨더리 인덱스 키에 포함되어 있다면 해당 인덱스 엔트리도 수정해야 합니다.
이번 예제에서 status는 (user_id, status) 복합 인덱스의 일부이므로
인덱스 엔트리 삭제 + 추가 작업이 발생합니다.
따라서 B는:
- PK 레코드 잠금
- 인덱스 정합성 유지를 위해 secondary 인덱스 갱신 시도
- 이미 A가 해당 secondary 인덱스 레코드를 잠그고 있으면 대기
하게 됩니다.
데드락 로그 확인 방법
SHOW ENGINE INNODB STATUS\G