INSERT ON DUPLICATE KEY UPDATE는 UNIQUE 인덱스 충돌을 에러가 아닌 UPDATE 트리거로 바꾸는 절이다

요약

"있으면 갱신, 없으면 삽입(upsert)"을 쿼리 한 번에 원자적으로 처리하는 MySQL 구문
동시성 안전성은 UNIQUE 인덱스에서 나오고, UNIQUE 인덱스가 없으면 같은 구문이 침묵 속에 중복을 쌓는다 — 에러가 아닌 silent failure라서 더 위험하다

본문

역할 — duplicate를 에러가 아니라 분기로 바꾼다

같은 키로 두 번째 INSERT가 들어오면 평소엔 Duplicate entry 에러가 난다. ON DUPLICATE KEY UPDATE는 그 충돌을 UPDATE 분기 실행으로 바꿔준다.

INSERT INTO ad_event_daily (campaign_id, app_id, data_at, impressions)
VALUES (101, 'app_A', '2026-05-25', 1000)
ON DUPLICATE KEY UPDATE impressions = VALUES(impressions);

직접 짜면 SELECT → 있으면 UPDATE, 없으면 INSERT 인데 이건 쿼리 2~3번 + 두 트랜잭션 사이 race condition. 이 절은 그걸 DB 엔진이 단일 원자 쿼리로 처리해 준다.

UPDATE 분기가 실제로 쓰는 값 — VALUES() / alias

충돌이 나면 ON DUPLICATE KEY UPDATE 뒤의 SET 절이 실행된다. 여기서 핵심은 "INSERT가 넣으려다 거부당한 값"을 어떻게 참조하느냐다.

VALUES(impressions) = 이번 INSERT가 넣으려던 값(예시의 1000). 즉 거부된 입력값을 UPDATE 쪽으로 끌어다 쓰는 함수.
SET 절에 안 적은 컬럼은 기존 값 그대로 유지된다. 전체 행을 덮는 게 아님.

덮어쓰기 vs 누적이 SET 절 한 줄로 갈린다:

... ON DUPLICATE KEY UPDATE impressions = VALUES(impressions)                -- 덮어쓰기(최신값)
... ON DUPLICATE KEY UPDATE impressions = impressions + VALUES(impressions)  -- 누적(기존+신규)

좌변 impressions = 이미 있던 행의 값, VALUES(impressions) = 들어온 값.

MySQL 8.0.20부터 VALUES()는 deprecated. 같은 걸 행 alias로 쓴다:
INSERT INTO ad_event_daily (...) VALUES (...) AS new
ON DUPLICATE KEY UPDATE impressions = new.impressions;

동시성 보장 — UNIQUE 인덱스 슬롯의 X락

두 세션이 동시에 같은 키로 호출해도 race condition이 없는 이유: InnoDB가 UNIQUE 인덱스의 해당 슬롯에 X락을 걸어 둘을 직렬화한다.

세션 A: INSERT 시도 → UNIQUE 슬롯 X락 획득
세션 B: 같은 슬롯 락 대기 ⏸
세션 A: COMMIT (행 생성됨)
세션 B: 락 풀림 → "어, 이미 있네?" → UPDATE 분기로 실행

핵심: 늦은 쪽은 에러를 받지 않는다. 락이 풀린 시점에 충돌을 깨닫고 자동으로 UPDATE 경로로 빠진다. 누적 업데이트(impressions = impressions + VALUES(impressions))도 행 단위 X락으로 직렬화되니까 값 손실 없음.

정확히는 값에 락이 걸리는 게 아니라 UNIQUE 인덱스 트리의 해당 키 슬롯에 락이 걸린다. 그래서 UNIQUE 인덱스가 없으면 락 잡을 슬롯도 없고 직렬화도 일어나지 않는다.

UNIQUE 인덱스 없으면 — 침묵 속의 중복 적재

이 구문이 동작하려면 PRIMARY KEY 또는 UNIQUE 인덱스가 반드시 필요하다. 없이 쓰면:

문법은 통과 → 에러도 경고도 없음
ON DUPLICATE KEY UPDATE 절은 죽은 코드가 됨 (충돌 판정 기준이 없으니까)
같은 자연 키 조합이 호출할 때마다 새 행으로 쌓임

+----+-------------+--------+------------+-------------+
| id | campaign_id | app_id | data_at    | impressions |
+----+-------------+--------+------------+-------------+
|  1 |         101 | app_A  | 2026-05-25 |        1000 |
|  2 |         101 | app_A  | 2026-05-25 |        1000 |  ← 중복!
+----+-------------+--------+------------+-------------+

개발자가 upsert를 의도하고 작성했어도 실제로는 중복 생성된다. 이게 가장 위험한 부분 — Duplicate entry 에러가 나면 즉시 알아채지만, 침묵 속의 중복은 발견이 늦다. 집계 쿼리 결과가 조용히 N배로 뻥튀기될 때 비로소 드러난다.

일반 인덱스로는 안 된다

"duplicate key"의 기준은 PRIMARY KEY 또는 UNIQUE 인덱스뿐. 일반(non-unique) 인덱스는 중복을 허용하니까 충돌이 정의되지 않는다.

복합 키일 때도 주의:

-- ❌ 따로따로면 안 됨 — "같은 campaign_id만 있어도" 충돌로 판정됨
UNIQUE KEY (campaign_id), UNIQUE KEY (app_id), UNIQUE KEY (data_at)

-- ✅ 자연 키를 하나의 복합 UNIQUE로 묶어야 함
UNIQUE KEY uk_report (campaign_id, app_id, data_at)

부가 함정

AUTO_INCREMENT 갭: UPDATE 분기로 빠진 쪽이 예약한 auto_increment 값은 버려진다. PK 값이 빠르게 점프함
데드락 위험: 테이블에 UNIQUE 인덱스가 2개 이상이면 두 세션이 서로 다른 순서로 락을 잡으며 데드락 가능
갭 락 (REPEATABLE READ): 존재하지 않는 키로 시도하면 InnoDB가 gap lock을 잡아 인접 범위의 다른 INSERT까지 막을 수 있음
NULL 처리: UNIQUE 컬럼에 NULL이 허용되면 NULL끼리는 "서로 다른 값"으로 취급돼서 중복 차단 안 됨 → 자연 키 컬럼은 NOT NULL

적용 순서

UNIQUE 인덱스를 먼저 만든다. 기존에 이미 중복 행이 있으면 ALTER가 실패하는데, 그게 오히려 데이터 오염을 잡아주는 안전장치
그 다음에 ON DUPLICATE KEY UPDATE 코드를 쓴다. 순서가 바뀌면 침묵 속에 데이터가 오염된 채로 시간이 흐른다

참고

InnoDB는 Next-Key Lock으로 팬텀 리드를 방지한다 — 같은 InnoDB의 락 메커니즘. UNIQUE 슬롯에 걸리는 X락과 gap lock의 작동 원리
Partial Index로 특정 조건에서만 유니크 제약을 적용할 수 있다 — UNIQUE 제약을 조건부로 거는 변형. upsert와 같은 "자연 키 unique" 발상의 연장