INSERT 한 문장이 일으킨 데드락 — 서브쿼리 S락과 트리거 X락의 락 순서 역전 분석

이 글은 "S락·X락·갭 락·락 승격 — InnoDB 락 노트"에 이어지는 2편입니다. 1편에서 정리한 공유락(S)·배타락(X)·락 승격 개념을 그대로 가져다 씁니다.

운영 DB 모니터링에서 데드락(deadlock) 기록을 발견했습니다. 데드락 로그가 지목한 문장은 contract_slots에 행 하나를 넣는 upsert 쿼리였습니다. 단순해 보이는 INSERT 한 번이 교착에 빠진 원인을 추적했습니다.

원인은 한 INSERT 문이 같은 contracts 행을 두 번(S락 → X락) 잠그고 있었다는 데 있습니다. 그 사이에 다른 행의 X락이 끼어들면서 교착이 발생했습니다. 로그를 한 줄씩 따라가며 살펴봅니다.

1. 현상: upsert 한 번에 데드락 로그가 찍혔다

문제의 쿼리는 이렇게 생겼습니다. gcid로 가장 최신 계약(contracts)을 골라 그 id를 contract_slots에 꽂는, ON DUPLICATE KEY UPDATE 기반 upsert입니다.

public Mono<Long> upsertContractSlot(UpsertContractSlot upsertContractSlot) {
    String query =
        """
        insert into contract_slots (contract_id, slot_id, user_id, ...)
        values (
            (select c.id                              -- ⚠️ 문제 1: contracts에 S락
             from contracts as c
             join (select max(c.ezrems_cid) as max_cid
                   from contracts as c
                   where c.ezrems_gcid = :ezrems_gcid
                   group by c.ezrems_gcid) as subquery
             on subquery.max_cid = c.ezrems_cid),
            :slot_id,
            :user_id,
            ...
        )
        on duplicate key update ...                   -- ⚠️ 문제 2: 트리거 발동 → contracts에 X락
        """;

    return databaseClient.sql(query)
        .bind("ezrems_gcid", upsertContractSlot.getGcid())
        .fetch().rowsUpdated();
}

그리고 SHOW ENGINE INNODB STATUS가 남긴 데드락 기록입니다. (테이블은 MariaDB / InnoDB, 일부 컬럼은 생략했습니다.)

------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-10-28 08:42:27

*** (1) TRANSACTION:
TRANSACTION 1110068327, ACTIVE 0 sec starting index read
UPDATE mims_authorizations AS ma
    JOIN contracts AS c ON NEW.contract_id = c.id
    ...
WHERE ma.source_id = NEW.id

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS on table `mgrv_dev`.`contracts`
trx id 1110068327 lock_mode X locks rec but not gap waiting
Record: contracts.id = 197084

*** (1) CONFLICTING WITH:
RECORD LOCKS on table `mgrv_dev`.`contracts`
trx id 1110068328 lock mode S locks rec but not gap
Record: contracts.id = 197084

*** (2) TRANSACTION:
TRANSACTION 1110068328, ACTIVE 0 sec inserting
insert into contract_slots (contract_id, ...)
values ((select c.id from contracts where c.ezrems_gcid = 141431 ...), ...)
on duplicate key update ...

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS on table `mgrv_dev`.`contract_slots`
index contract_slots_ezrems_gcid_ezrems_guest_code_uindex
trx id 1110068328 lock_mode X waiting
Record: ezrems_gcid=141431, ezrems_guest_code=0, id=2088501

*** (2) CONFLICTING WITH:
RECORD LOCKS on table `mgrv_dev`.`contract_slots`
trx id 1110068327 lock_mode X
Record: ezrems_gcid=141431, ezrems_guest_code=0, id=2088501

*** WE ROLL BACK TRANSACTION (1)

로그에는 INSERT 하나에 트랜잭션 둘이 얽혀 있고, 그중 한쪽은 mims_authorizations를 UPDATE 하고 있습니다. 그런데 upsert 쿼리에는 mims_authorizations라는 테이블이 등장하지 않습니다. 단서는 (1) 트랜잭션의 SQL에 박힌 NEW.contract_id, NEW.id였습니다. NEW는 트리거(Trigger) 안에서만 쓸 수 있는 키워드입니다. 즉 이 UPDATE는 직접 호출한 쿼리가 아니라, contract_slots에 행이 들어갈 때 따라 도는 트리거의 본문입니다.

2. 로그 해부: 누가 무엇을 쥐고 무엇을 기다렸나

InnoDB의 데드락 로그는 각 트랜잭션이 무엇을 기다리는지(WAITING FOR), 그리고 그게 누구와 충돌하는지(CONFLICTING WITH)를 기록합니다. 두 트랜잭션이 같은 gcid=141431을 동시에 처리하면서, 같은 contracts 행(197084)과 같은 contract_slots 행(2088501)을 두고 엇갈렸습니다. 정리하면 이렇습니다.

화살표로 그려 보면 서로의 꼬리를 문 모양이 드러납니다.

trx …327 ── 기다림 ──▶ contracts(197084)   ◀── 쥐고 있음 ── trx …328
trx …328 ── 기다림 ──▶ contract_slots(2088501) ◀── 쥐고 있음 ── trx …327

327은 328이 쥔 contracts를, 328은 327이 쥔 contract_slots를 기다립니다. 전형적인 순환 대기(circular wait), 곧 교착입니다. InnoDB는 둘 중 하나를 롤백시켜 교착을 풀며, 로그 마지막 줄 WE ROLL BACK TRANSACTION (1)이 그 결과입니다. 트리거를 돌리던 327이 롤백됐습니다.

여기서 한 가지 의문이 남습니다. 같은 쿼리를 호출한 두 세션인데, 왜 한쪽은 contracts에 S락(공유락)을, 다른 쪽은 X락(배타락)을 쥐고 있는가 하는 점입니다. 다음 절에서 그 이유를 살펴봅니다.

3. 원인: 한 INSERT 문 안의 두 단계 락

이유는 하나의 upsert가 contracts를 두 번, 서로 다른 모드로 잠근다는 데 있습니다. 쿼리에 달아둔 ⚠️ 문제 1, ⚠️ 문제 2가 바로 그 두 지점입니다.

문제 1 — 서브쿼리가 거는 S락. values (...) 안에는 contract_id를 구하려고 contracts를 읽는 서브쿼리가 들어 있습니다. InnoDB는 기본 격리 수준(REPEATABLE READ)에서, 쓰기 문장이 다른 행을 읽어서 그 값을 쓰면 읽은 행의 일관성을 지키기 위해 공유락(S락)을 건다. 그래서 INSERT가 진행되는 동안 선택된 contracts 행(197084)에는 S락이 잡힙니다. S락은 공유락이므로 두 세션이 동시에 같은 행에 S락을 쥐어도 충돌하지 않는다.

문제 2 — 트리거가 거는 X락. ON DUPLICATE KEY UPDATE는 이름과 달리 실제로 행을 쓰는 동작이라, contract_slots에 걸린 AFTER 트리거를 깨운다. 그 트리거가 UPDATE mims_authorizations ... JOIN contracts를 실행하는데, 로그를 보면 이 문장이 contracts(197084)에 배타락(X락)을 요청합니다(lock_mode X locks rec but not gap). 같은 트랜잭션이 서브쿼리에서 S락으로 잡았던 행을, 트리거 시점에는 X락으로 올려야 한다. 락 승격(lock upgrade)이다.

하나의 upsert가 락을 잡는 순서를 시간순으로 늘어놓으면 이렇습니다.

upsertContractSlot 한 번이 잡는 락 (순서대로)
1. 서브쿼리 SELECT contracts            → S락  contracts(197084)
2. INSERT … ON DUPLICATE KEY UPDATE     → X락  contract_slots(2088501)
3. ODKU가 깨운 트리거의 UPDATE … JOIN    → X락  contracts(197084)   ← 1번의 S락을 X로 승격

핵심은 같은 contracts 행을 1번(S)과 3번(X)에서 두 번 잠그고, 그 사이 2번에서 다른 행(contract_slots)의 X락이 끼어든다는 점입니다. 단독으로 실행될 때는 문제가 없다. 하지만 같은 gcid로 두 세션이 동시에 들어오면, 이 순서가 교착의 조건이 됩니다.

4. 락 순서 역전: 왜 풀리지 않는 교착인가

두 세션 A(327)와 B(328)가 같은 gcid=141431로 동시에 upsert를 호출한 경우를 따라가 봅니다.

세션 A (trx …327)                       세션 B (trx …328)
─────────────────────────────────       ─────────────────────────────────
1. contracts(197084) S락 획득            1. contracts(197084) S락 획득   (S끼리는 공존 OK)
2. contract_slots(2088501) X락 획득      2. contract_slots(2088501) X락 … 대기 (A가 쥠)
3. 트리거: contracts X락 … 대기
   (B가 같은 행에 S락을 쥐고 있어
    S→X 승격 불가)
        └──────────── 서로 물림 → 교착 ────────────┘

핵심은 S락은 공존하지만 X락 승격은 다른 세션의 S락이 남아 있으면 진행되지 못한다는 점입니다.

1. A와 B 둘 다 서브쿼리로 같은 contracts 행을 읽으므로, S락을 함께 쥔다.
2. contract_slots 행의 X락은 한 세션만 가질 수 있으므로, 먼저 도착한 A가 가져간다. B는 여기서 대기한다.
3. A는 트리거를 실행하려고 contracts를 S에서 X로 올려야 한다. X로 올리려면 다른 세션이 그 행에 락을 쥐고 있지 않아야 하는데, B가 아직 S락을 쥔 채 2번에서 멈춰 있다. A는 B가 S를 놓기를 기다린다.
4. 그러나 B는 A가 쥔 contract_slots X락을 기다리는 중이라 전진하지 못하고, S락도 놓지 못한다.

A는 B를 기다리고, B는 A를 기다립니다. 어느 쪽도 진행할 수 없으므로 InnoDB가 데드락으로 판정하고 A(327)를 롤백시킵니다. 환경에 따라 간헐적으로 발생하는 것이 아니라, 같은 gcid로 요청이 겹치는 순간 구조적으로 발생하는 교착입니다.

5. 해결 방향: 락을 한 방향으로 정렬한다

데드락의 일반적인 해법은 모든 트랜잭션이 자원을 같은 순서로 잠그게 하는 것입니다. 이 사례에서는 "한 INSERT 문이 contracts를 S로 잡았다가 X로 다시 잡는" 두 단계 구조를 없애는 것이 핵심입니다. 후보는 세 가지입니다.

①과 ②는 락을 한 번만, 한 방향으로 잡게 만들어 순환 자체를 끊습니다. ③은 여기에 더해 "쿼리만 봐서는 드러나지 않던 트리거의 락"을 제거합니다. 세 방향 모두 "같은 행을 한 트랜잭션 안에서 S→X로 두 번 잠그지 않는다"는 원칙으로 수렴합니다.

6. 마무리

이번 분석에서 얻은 교훈을 정리합니다.

• 데드락 로그는 추측이 아니라 증거다. LATEST DETECTED DEADLOCK의 WAITING FOR / CONFLICTING WITH를 읽으면 누가 무엇을 쥐고 무엇을 기다리는지가 드러납니다. 락 모드(S/X)와 레코드 키를 표로 정리하자 순환 대기가 명확해졌습니다.
• 쿼리에 드러나지 않는 락을 의심하자. 데드락을 만든 mims_authorizations UPDATE는 직접 작성한 문장이 아니었습니다. NEW. 키워드가 트리거를 가리켰고, 그 트리거의 X락이 원인의 절반이었습니다. 트리거·외래키·서브쿼리처럼 자동으로 따라붙는 락은 SQL 텍스트만 봐서는 드러나지 않습니다.
• 한 문장이 같은 행을 두 번 잠그면 의심하자. S락(읽기)과 X락(쓰기)을 한 트랜잭션 안에서 같은 행에 순차로 거는 구조는, 동시 요청이 겹치는 순간 락 승격이 막히며 교착으로 이어집니다.

참고: 이 데드락은 환경에 따른 간헐적 버그가 아니라 같은 gcid가 겹치면 재현되는 구조적 문제였습니다. 재현 조건이 명확하면 수정 후 검증도 명확하게 할 수 있습니다.