MAX+1 채번은 락 유무와 인덱스 유무로 세 가지 상태가 된다
FOR UPDATE 가 유일성을, 그 컬럼의 인덱스가 락 범위를 정합니다. 하나만 봐서는 판정이 안 됩니다
SELECT MAX(컬럼) + 1 로 채번하는 코드를 점검하면서, 처음에는 FOR UPDATE 가 붙어 있는지만 봤습니다. 그것만으로는 판정이 안 됐습니다. 두 가지를 따로 봐야 합니다.
FOR UPDATE가 있는가 → 동시 채번 시 유일성이 지켜지는지를 정합니다- 채번 컬럼에 인덱스가 있는가 → 잠그는 범위를 정합니다
두 축의 조합이 세 가지 상태를 만듭니다. 사내에 셋 다 실물이 있었습니다.
| 상태 | FOR UPDATE | 인덱스 | 유일성 | 락·스캔 범위 |
|---|---|---|---|---|
| A | ✗ | — | 깨짐 | 락 없음 |
| B | ✓ | ✗ | 지켜짐 | 테이블 전 행 X락 |
| C | ✓ | ✓ | 지켜짐 | 마지막 1행 + 갭 |
이하 예시는 booking_item(booking_no, item_no) 복합 PK 테이블에서 item_no 를 채번하는 상황으로 적습니다.
상태 A — 락이 없으면 조용히 중복됩니다
<select id="selectMaxItemNo" resultType="long">
SELECT IFNULL(MAX(ITEM_NO),0) FROM BOOKING_ITEM
</select>
같은 기능의 웹 코드에는 FOR UPDATE 가 있는데, 복제해 간 쪽에는 없었습니다. 결과가 데이터에 남아 있었습니다.
| 항목 | 값 |
|---|---|
| 전체 행 | 23,569 |
| distinct ITEM_NO | 23,380 |
| 중복 그룹 | 187 |
| 발생 기간 | 3개월 반 (진행 중) |
중복 그룹의 생성 시간차 분포가 원인을 특정해 줬습니다.
| 같은 ITEM_NO 행들의 생성 시간차 | 그룹 수 |
|---|---|
| 5초 이내 (동시 경합) | 181 |
| 1시간 이내 | 1 |
| 하루 이내 | 2 |
| 하루 초과 (번호 재사용) | 3 |
97% 가 5초 이내였습니다. 실제 사례는 같은 초에 서로 다른 예약이 같은 번호를 가져갔습니다.
ITEM_NO BOOKING_NO ORDER_NO CREATED_AT
22934 CB962A260828 P20260828000107 2026-08-28 14:35:56
22934 D2A27S260828 P20260828000108 2026-08-28 14:35:56
PK 가 (BOOKING_NO, ITEM_NO) 복합이라 PK 위반이 안 나고 그대로 통과합니다. 에러도 알림도 없습니다. 같은 회사의 결제번호 채번은 PK 가 단일 컬럼이라 Duplicate entry 로 터져서 발견됐는데, 복합 PK 면 그 안전망조차 없습니다.
당장의 피해가 없어도 남는 문제
ITEM_NO 단독 조건으로 조회하는 쿼리가 있으면 다른 예약의 행을 끌어옵니다. 점검 시점에는 그런 쿼리가 한 곳 있었고, 그 유일한 호출부가 주석 처리돼 있어서 실제 오염은 0건이었습니다. 나머지 쿼리는 전부 BOOKING_NO = ? AND ITEM_NO = ? 로 PK 를 온전히 썼습니다. 즉 ITEM_NO 단독 조회를 새로 추가하는 순간 문제가 됩니다.
상태 B — FOR UPDATE 는 있는데 인덱스가 없으면 테이블 락이 됩니다
<select id="selectMaxItemNo" resultType="long">
SELECT IFNULL(MAX(ITEM_NO),0) FROM BOOKING_ITEM FOR UPDATE
</select>
유일성은 지켜집니다. 문제는 잠그는 범위입니다. ITEM_NO 에 인덱스가 없으면 옵티마이저가 다른 인덱스를 통째로 훑고, FOR UPDATE 는 훑은 행을 전부 X락합니다.
dev 실측입니다 (5,133행 테이블).
EXPLAIN: type: index key: ix_booking_item_orderno Extra: Using index
Handler_read_next: 5133 ← 전 행을 읽었고 = 전 행을 잠갔습니다
운영 슬로우로그에도 그대로 찍혀 있었습니다 (62,134행).
Rows examine 60.5k Exec time 0.9s (2회) Lock time 652ms
사실상 테이블 락입니다. 예약 생성 트랜잭션이 커밋될 때까지 다른 모든 예약 쓰기가 대기합니다.
상태 C — 인덱스 하나로 1행 + 갭
ALTER TABLE BOOKING_ITEM ADD INDEX idx_booking_item_item_no (ITEM_NO),
ALGORITHM=INPLACE, LOCK=NONE;
코드 변경은 0줄입니다. dev 실측 before/after 입니다.
| 인덱스 없음 | 인덱스 있음 | |
|---|---|---|
| EXPLAIN | type: index, 다른 인덱스 풀스캔 | Select tables optimized away |
| 읽은(=잠근) 행 | Handler_read_next 5,133 | Handler_read_last 1 |
| 상호배제 | 유지 | 유지 |
상호배제가 살아 있는지는 실측으로 확인해야 했습니다. Select tables optimized away 는 "테이블을 안 읽었다" 처럼 보여서 갭락이 사라졌다고 오해하기 쉽습니다. 두 세션으로 확인한 결과입니다.
세션1: BEGIN; SELECT MAX(ITEM_NO) FROM BOOKING_ITEM FOR UPDATE; -- 락 보유
세션2: BEGIN; SELECT MAX(ITEM_NO) FROM BOOKING_ITEM FOR UPDATE;
→ ERROR 1205 Lock wait timeout exceeded ← 대기합니다
인덱스의 마지막 레코드와 그 뒤 supremum 갭을 잡기 때문에, "더 큰 ITEM_NO 를 동시에 INSERT" 가 여전히 막힙니다.
인덱스 추가 전에 확인한 것
새 단일컬럼 인덱스가 다른 쿼리의 플랜을 바꿀 수 있습니다. 이 테이블은 전 쿼리가 BOOKING_NO = ? AND ITEM_NO = ? 로 PK 를 온전히 써서, 새 인덱스가 선택될 자리가 MAX(ITEM_NO) 밖에 없다는 걸 확인하고 넣었습니다.
인덱스를 넣어도 슬로우로그에는 한동안 남습니다
배포 전 슬로우 로그가 로테이션 기간 동안 남아 있어서, 나중에 슬로우 쿼리 분석을 돌리면 이 쿼리가 다시 상위로 올라옵니다. 실제로 조치 다음 날 점검에서 재분석이 반복됐고, 잡힌 hit 은 인덱스 적용 12시간 전 로그였습니다.
조치 완료한 쿼리를 fingerprint 로 등록해 배포 시각 이전 hit 을 순위에서 제외하는데, 이때 배포 시각을 UTC 로 적어야 합니다. 비교 대상인 슬로우 로그의 # Time: 이 UTC 인데 세션 타임존은 Asia/Seoul 이라, KST 로 적으면 배포 후 9시간짜리 회귀 hit 이 조용히 걸러집니다.
셋 다 못 막는 것 — 삭제 시 번호 재사용
MAX+1 은 최상단 행이 지워지면 그 번호를 다시 씁니다. 락과 무관합니다.
- dev 테이블: 5,133행 / distinct 5,085 → 중복 48건. 중복 행들의 생성 시각이 몇 분에서 몇 시간씩 벌어져 있어 경합이 아니라 재사용입니다.
- 운영은 62,134 / 62,134 로 깨끗했는데, 규칙이 지켜져서가 아니라 행을 안 지워서입니다. 매퍼에는
DELETE FROM BOOKING_ITEM이 실재합니다.
근본 해결은 두 가지입니다.
① 별도 채번 테이블 — 채번용 테이블 한 행을 FOR UPDATE 로 잡고 증가시키는 방식입니다. 사내 표준이 있었고 결제번호에 이미 적용한 선례가 있었습니다. 배포 전에 채번 테이블 시딩이 반드시 선행돼야 합니다.
INSERT INTO SEQ_MASTER (TBL_NM, IDX, REFRESH_DATE)
SELECT 'BOOKING_ITEM', COALESCE(MAX(ITEM_NO),0) + 1000, NOW()
FROM BOOKING_ITEM
WHERE NOT EXISTS (SELECT 1 FROM SEQ_MASTER WHERE TBL_NM='BOOKING_ITEM');
② AUTO_INCREMENT — 복합 PK 여도 UNIQUE KEY (ITEM_NO) 를 만들면 auto_increment 컬럼이 될 수 있습니다. auto_increment 는 "어떤 인덱스의 첫 컬럼" 이기만 하면 됩니다. 락이 아예 없어지고(카운터는 statement 종료 시 해제) 채번 쿼리 자체가 사라집니다. 대신 롤백 시 번호에 구멍이 생기고, 기존 중복을 먼저 정리해야 UNIQUE 를 걸 수 있습니다.
코드에서 MAX(...) + 1 을 보면 확인하는 것
FOR UPDATE가 있는가. 없으면 동시 채번에 중복됩니다.- PK 가 복합인가. 복합이면 중복이 에러 없이 통과하므로 데이터로 직접 확인해야 합니다.
SELECT COUNT(*), COUNT(DISTINCT 채번컬럼) FROM 테이블; - 채번 컬럼에 인덱스가 있는가. 없으면
FOR UPDATE가 테이블 전 행을 잠급니다. - 그 테이블에
DELETE가 있는가. 있으면 번호가 재사용됩니다. - 복제된 코드인가. 한쪽만 고쳐지고 다른 쪽이 남아 있는 경우가 이 건이었습니다.