본문으로 건너뛰기

세컨더리 인덱스와 PK 인덱스 간 데드락, 왜 발생할까?

· 약 6분
Johny Cho
Johny Cho
Back End Engineer @ NHN


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 경로)
1idx_user_status 인덱스 레코드 잠금 (user_id=100, status=0)PK(id=10) 레코드 잠금
2PK(id=10) 잠금 시도 → 이미 B가 PK 락 보유status 변경 → idx_user_status 인덱스 엔트리 갱신 필요
3PK 대기 상태 진입Secondary 인덱스 잠금 시도 → A가 보유
4

서로 상대 락을 기다리며 Deadlock 발생

즉, 락 획득 순서 불일치 (Lock Ordering Inconsistency)가 데드락의 핵심 원인입니다.

순서트랜잭션 A트랜잭션 B
1idx_user_status 인덱스 레코드 잠금PK(id=10) 락 획득
2PK 잠금 시도 → 대기status 변경으로 인한 secondary 인덱스 갱신 필요
3대기 상태Secondary 인덱스 잠금 시도 → A가 보유
4🚨 교착 발생🚨 교착 발생

트랜잭션 B는 PK로만 접근했는데 왜 세컨더리 인덱스(idx_user) 락을 기다릴까?​

InnoDB는 UPDATE 시 변경되는 컬럼이 세컨더리 인덱스 키에 포함되어 있다면 해당 인덱스 엔트리도 수정해야 합니다.

이번 예제에서 status는 (user_id, status) 복합 인덱스의 일부이므로 인덱스 엔트리 삭제 + 추가 작업이 발생합니다.

따라서 B는:

  1. PK 레코드 잠금
  2. 인덱스 정합성 유지를 위해 secondary 인덱스 갱신 시도
  3. 이미 A가 해당 secondary 인덱스 레코드를 잠그고 있으면 대기

하게 됩니다.

데드락 로그 확인 방법​

SHOW ENGINE INNODB STATUS\G
Loading comments...