개요
운영 중인 서비스에서 아래와 같은 데드락이 발생했다. 동일한 쿼리가 두 개의 스레드에서 동시에 실행되었는데, 어떻게 데드락이 발생했는지 분석한다.
delete from `{테이블명}` where `{테이블명}`.`mall_no` = {mallNo} limit 1000환경
- DB: MySQL 8.0 (InnoDB)
- 문제 쿼리: 특정
mall_no에 해당하는 데이터를 1000건씩 배치 삭제
테이블 구조 (요약)
create table {테이블명}
(
{PK컬럼} int auto_increment primary key,
mall_no int not null,
{날짜컬럼} datetime null,
...
);
-- 관련 인덱스
create index {인덱스A} on {테이블명} (mall_no, {날짜컬럼});데드락 로그 (요약)
*** (1) TRANSACTION:
TRANSACTION 18300121487, ACTIVE 0 sec
delete from `{테이블명}` where `{테이블명}`.`mall_no` = {mallNo} limit 1000
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS index {인덱스A} ... lock_mode X locks rec but not gap
heap no 595
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS index PRIMARY ... lock_mode X locks rec but not gap waiting
heap no 54
*** (2) TRANSACTION:
TRANSACTION 18300121467, ACTIVE 0 sec, undo log entries 1
delete from `{테이블명}` where `{테이블명}`.`mall_no` = {mallNo} limit 1000
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS index PRIMARY ... lock_mode X locks rec but not gap
heap no 54
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS index {인덱스A} ... lock_mode X locks rec but not gap waiting
heap no 595
*** WE ROLL BACK TRANSACTION (1)기본 배경 지식: 세컨더리 인덱스란?
InnoDB에서 인덱스는 두 종류로 나뉜다.
- 클러스터드 인덱스 (PK): 실제 행 데이터를 포함한다.
- 세컨더리 인덱스: 인덱스 컬럼 값 + PK 값만 저장하며, 실제 데이터는 PK를 통해 접근한다.
{인덱스A} (세컨더리 인덱스)
┌──────────┬───────────┬──────────┐
│ mall_no │ {날짜컬럼} │ {PK컬럼} │ ← PK 참조
└──────────┴───────────┴──────────┘
↓ PK 룩업
PRIMARY INDEX (클러스터드 인덱스)
┌──────────┬────────────────────┐
│ {PK컬럼} │ 실제 행 데이터 전체 │
└──────────┴────────────────────┘즉, 세컨더리 인덱스를 통해 검색하더라도, PK 를 통한 데이터블록에 엑세스가 필요하다.
따라서 InnoDB에서 세컨더리 인덱스를 통해 DELETE를 실행하면, 락은 일반적으로 아래 순서로 획득된다.
세컨더리 인덱스 레코드 X락 → PK 레코드 X락 → 삭제데드락 발생 구조
로그에서 확인된 락 상태는 아래와 같다.
| TX1 | TX2 | |
|---|---|---|
| 보유 중인 락 | {인덱스A} heap no 595 | PRIMARY heap no 54 |
| 대기 중인 락 | PRIMARY heap no 54 | {인덱스A} heap no 595 |
두 트랜잭션이 동일한 레코드 쌍에 대해 서로 상대방이 보유한 락을 기다리는 전형적인 순환 대기 구조다.
TX1: [{인덱스A} heap 595 보유] → [PK heap 54 대기] ⏳
TX2: [PK heap 54 보유] → [{인덱스A} heap 595 대기] ⏳핵심 의문: 왜 락 획득 순서가 달랐나?
여기서 중요한 의문이 생긴다.
동일한 쿼리라면 두 트랜잭션 모두 같은 인덱스를 같은 순서로 스캔해야 한다.
그렇다면 락 순서도 동일해야 하고, 데드락이 아닌 단순 Lock Wait만 발생해야 한다.
그런데 실제로는 TX1은 세컨더리 인덱스 락을 보유하고 PK 락을 대기, TX2는 PK 락을 보유하고 세컨더리 인덱스 락을 대기하는 역전된 구조가 나타났다.
주목할 만한 단서
LIMIT 1000: 트랜잭션 내에서 레코드를 한 건씩 순차 처리한다.- TX2의
undo log entries 1: TX2는 이미 1건을 삭제 완료한 상태다. - 해당 mall_no에 새로운 데이터는 발생하지 않지만, 다른 mall_no의 INSERT는 발생할 수 있다.
가설
로그만으로는 락 순서가 역전된 정확한 원인을 단정할 수 없다. 아래는 가능성 있는 가설들이다.
가설 1. TX2의 선행 삭제가 인덱스 구조에 영향을 줬다
TX2는 이미 1건을 삭제(undo log entries 1)한 상태다. InnoDB는 삭제 시 즉시 물리적으로 제거하지 않고 delete mark 처리를 한다. 이 과정에서 인덱스 페이지의 구조가 변경되어 TX1과 TX2가 서로 다른 순서로 레코드를 탐색하게 됐을 가능성이 있다.
가설 2. 다른 mall_no의 INSERT로 인한 인덱스 페이지 분할
다른 mall_no의 INSERT가 동시에 발생할 수 있는 환경이다. INSERT로 인해 {인덱스A}의 페이지 분할(Page Split) 이 발생했다면, 두 트랜잭션이 탐색하는 인덱스 페이지 순서가 달라졌을 수 있다.
가설 3. 실행 타이밍 차이로 인한 레코드 접근 순서 역전
TX2가 먼저 시작하여 일부 레코드를 처리하는 동안 TX1이 시작됐다. LIMIT 1000 처리 중 TX2가 특정 레코드의 PK 락을 잡고 다음 세컨더리 인덱스 항목으로 이동하려는 찰나, TX1이 그 세컨더리 인덱스 레코드를 먼저 획득했을 가능성이 있다. 다만 이 경우도 왜 TX1이 세컨더리 인덱스를 PK보다 먼저 접근했는지는 추가 설명이 필요하다.
해결 방법
1. 동시 실행 방지 (근본적 해결)
동일한 배치 삭제가 중복 실행되지 않도록 애플리케이션 레벨에서 제어한다.
// 분산 락, 스케줄러 중복 실행 방지 등2. ORDER BY로 일관된 접근 순서 보장
두 트랜잭션이 항상 동일한 순서로 레코드에 접근하도록 강제한다.
DELETE FROM {테이블명}
WHERE mall_no = {mallNo}
ORDER BY {PK컬럼} -- PK 기준 정렬
LIMIT 1000;3. 재시도 로직 추가
데드락은 완전히 방지하기 어렵기 때문에, 애플리케이션에서 데드락 감지 시 자동으로 재시도하도록 처리한다. TX1은 이미 롤백되었으므로 재시도가 가능하다.
결론
| 항목 | 내용 |
|---|---|
| 원인 | 동일한 DELETE 쿼리의 중복 실행으로 인한 순환 락 대기 |
| 핵심 구조 | 세컨더리 인덱스 락 ↔ PK 락의 교차 보유 |
| 미해결 | 왜 동일 쿼리임에도 락 획득 순서가 역전됐는지 정확한 원인 불명확 |
| 권장 조치 | 중복 실행 방지 + ORDER BY 추가 + 재시도 로직 |
이 분석은 데드락 로그만을 기반으로 한 것으로, 일부 원인은 가설 수준임을 밝힌다.
정확한 원인 파악을 위해서는 실행 계획(EXPLAIN), 인덱스 페이지 상태, 동시 실행 중인 다른 트랜잭션 정보가 추가로 필요하다.
