PostgreSQL INSERT를 ROLLBACK했는데 다음 ID가 2인 이유

PostgreSQL에서 ID 1을 받은 INSERT를 ROLLBACK하면 행은 사라져도 다음 INSERT가 ID 2를 받을 수 있다. 행 저장을 취소해도 시퀀스의 nextval()이 이미 할당한 번호는 회수하지 않기 때문이다. 행의 롤백과 번호 발급은 따로 확인해야 한다. ID가 비었다는 사실만으로 롤백 실패나 데이터 유실을 판단할 수는 없다.

PostgreSQL 18의 시퀀스 함수 문서↗ (새 탭에서 열림)는 같은 시퀀스에서 번호를 받는 동시 트랜잭션을 막지 않도록 이 동작을 정했다고 설명한다. 뒤에서 트랜잭션이 취소될 가능성이 있어도 다른 세션은 다음 번호를 받을 수 있다. 따라서 시퀀스가 발급한 번호를 “커밋된 행의 순번”으로 해석하면 기대가 어긋난다.

행은 0개인데 시퀀스는 1을 사용한 상태였다

새 PostgreSQL 18.6 실습 DB에서 시작값 1, 증가폭 1, 캐시 크기 1인 시퀀스로 비교했다. 새 테이블에 두 번 INSERT하고 각각 COMMIT한 비교에서는 ID 1과 2가 모두 남았다. 반면 첫 INSERT를 ROLLBACK한 경우에는 ID 2인 행만 남았다. 실제 서비스에서 겪은 장애 기록이 아니라 별도로 실행한 실험이다.

아래 SQL로 롤백 직후의 행 수와 시퀀스 상태, 다음 성공 INSERT의 ID를 함께 확인할 수 있다. 연습용 DB에 접속한 새 psql 세션에서 자동 커밋을 켜고 한 번 실행한다. 임시 객체를 만들 권한이 필요하다. 두 CREATE와 ALTER는 첫 BEGIN 전에 실행하며, 전체를 하나의 트랜잭션으로 감싸지 않는다. 임시 테이블과 시퀀스는 연결을 닫으면 사라진다.

CREATE TEMP SEQUENCE rollback_gap_seq CACHE 1;
CREATE TEMP TABLE rollback_gap_demo (
  id bigint PRIMARY KEY DEFAULT nextval('rollback_gap_seq'),
  note text NOT NULL
);
ALTER SEQUENCE rollback_gap_seq OWNED BY rollback_gap_demo.id;

BEGIN;
INSERT INTO rollback_gap_demo(note) VALUES ('cancelled') RETURNING id;
ROLLBACK;

SELECT count(*) AS rows_after_rollback FROM rollback_gap_demo;
SELECT last_value, is_called FROM rollback_gap_seq;

BEGIN;
INSERT INTO rollback_gap_demo(note) VALUES ('kept') RETURNING id;
COMMIT;
SELECT id, note FROM rollback_gap_demo;

첫 RETURNING id는 1이었다. ROLLBACK 직후의 두 SELECT는 다음 결과를 냈다. 아래 출력은 열 구분자를 |로 표시한 것이며 t는 true다. 기본 psql에서는 같은 값이 정렬된 표로 보일 수 있다.

rows_after_rollback
0
last_value|is_called
1|t

행 수 0은 첫 INSERT가 취소됐음을 보여 준다. last_value=1, is_called=true는 이 시퀀스가 이미 번호를 발급한 상태임을 보여 준다. 새 시퀀스도 호출 전에는 last_value가 1이지만 is_called는 false였다. 숫자 1만 보고 아직 사용하지 않은 시퀀스라고 판단하면 안 된다.

두 번째 RETURNING id는 2였고 COMMIT 뒤 마지막 조회에는 다음 한 행만 있었다.

id|note
2|kept

이 조건에서 최종 행에 ID 1이 없는 것은 첫 행이 롤백됐기 때문이다. RETURNING id를 받았다는 사실은 그 행의 최종 커밋을 보장하지 않는다. 반대로 이 재현에서 롤백 뒤 행이 남았다면, 같은 연결에서 BEGIN과 ROLLBACK을 실행했는지와 자동 커밋 설정부터 확인해야 한다.

상태를 확인하려고 nextval을 호출하면 번호를 더 쓴다

별도의 새 시퀀스에서는 INSERT 없이 BEGIN → SELECT nextval(...) → ROLLBACK만 실행했다. 첫 호출은 1이었고 롤백 뒤 상태는 역시 1|t였다. 다음 호출은 2였다. 번호 소비에는 행 삽입 자체가 필요하지 않았다.

따라서 운영 시퀀스의 현재 상태를 알아보겠다고 nextval()을 호출하지 않는다. 위 예제의 SELECT last_value, is_called FROM rollback_gap_seq는 번호를 새로 발급하지 않는 조회다. 실제 대상에서는 정확한 시퀀스 이름과 조회 권한을 먼저 확인한다. CREATE SEQUENCE 문서↗ (새 탭에서 열림)는 캐시 크기가 1보다 크면 last_value에 아직 반환하지 않은 예약 번호까지 반영될 수 있다고 설명한다. 이 글의 숫자는 캐시 크기 1인 단일 세션의 관찰이며, 운영 시퀀스의 last_value 하나로 다음 INSERT의 ID를 예측하는 방법은 아니다.

연속 번호가 필요하면 업무 번호를 별도로 설계한다

빈 번호를 채우려고 시퀀스를 setval()로 되감는 것을 일반적인 복구 방법으로 삼으면 안 된다. 다른 작업이 이미 받아 간 번호를 다시 발급하면 충돌할 수 있다. 시퀀스 함수 문서는 setval()의 변경도 롤백으로 취소되지 않는다고 명시한다. BEGIN으로 감쌌다고 안전한 시험이 되는 것도 아니다.

기본 키가 행을 식별하는 용도라면 중간 번호가 비어도 그 역할은 유지된다. 청구서 번호처럼 연속성이 별도 요구라면 내부 ID와 업무 번호를 나누고, 어느 시점에 번호를 확정할지, 취소 번호를 어떻게 기록할지, 동시 발급과 재시도를 어떻게 처리할지부터 정해야 한다. 카운터를 저장한 테이블을 배타적으로 잠가 발급하는 방식은 CREATE SEQUENCE 문서에도 나오지만, 동시 요청이 많을수록 시퀀스보다 비용이 크다는 조건이 붙는다. 중복 방지 제약과 잠금 범위까지 갖춘 별도 설계가 필요하다.

직접 확인한 범위는 PostgreSQL 18.6에서 한 세션이 명시적으로 ROLLBACK한 경우다. 여러 연결의 캐시, 서버 비정상 종료, ORM의 선할당, 연속 업무 번호 구현과 성능은 실험하지 않았다. 운영 데이터의 모든 ID 공백이 롤백 때문이라고 단정하지 말고, 해당 트랜잭션의 종료 결과와 실제 행을 확인한다.