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 면 DB 가 중복을 에러로 통지하지 않습니다.
당장의 피해가 없어도 남는 문제
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행 중 약 60,500행을 검사했습니다.
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가 있는가. 있으면 번호가 재사용됩니다. - 복제된 코드인가. 한쪽만 수정되고 다른 쪽이 남아 있는 경우가 이 건이었습니다.