답변

SEQUENCE(예: PostgreSQL 및 Oracle) — 현재 값을 저장하고 삽입 시 고유한 숫자를 얻을 수 있도록 하는 특별한 데이터베이스 객체입니다(일반적으로 키). IDENTITY — 삽입 시 다음 순서 값을 자동으로 할당하는 열 속성입니다(SQL Server, MySQL, PostgreSQL의 테이블 자동 증가).

주요 차이점:

SEQUENCE — 테이블 외부의 객체로, 여러 위치에서 접근할 수 있으며 삽입 없이도 다음 값을 얻을 수 있습니다(nextval / currval). 여러 테이블에서 사용할 수 있으며, 하나의 테이블에 여러 SEQUENCE가 있을 수 있습니다.
IDENTITY — 열 정의의 일부로, 행 삽입 시에만 자동으로 증가합니다.

예시:

-- PostgreSQL SEQUENCE
CREATE SEQUENCE order_seq;
INSERT INTO orders(id, name) VALUES (nextval('order_seq'), 'First Order');

-- PostgreSQL IDENTITY
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  username TEXT
);
INSERT INTO users(username) VALUES ('ivan');  -- id가 자동으로 할당됩니다.

문제 및 주의사항:

수동으로 id를 삽입하면 "구멍"이나 중복이 발생할 수 있습니다.
행 삭제 시 — 값이 재사용되지 않아 키 범위에 간격이 발생할 수 있습니다.
일부 데이터베이스 시스템(예: MySQL)에서는 트랜잭션을 롤백하면 autoincrement가 "앞으로 건너뛰어" 이전 값을 유지할 수 있습니다.
SEQUENCE는 여러 테이블에서 사용할 수 있지만 이는 고유성 유지를 어렵게 만듭니다.

함정 질문

하나의 SEQUENCE에서 두 요청이 동시에 nextval()을 요청하면 어떻게 됩니까? 비유니크한 값을 얻을 수 있습니까?

답변: 아니요, SEQUENCE는 유일성을 보장합니다. 각 요청은 고유한 값을 받으며, 동시에 요청하더라도 유일한 값이 보장됩니다. 그러나 병렬성 때문에 삽입 순서와는 다를 수 있습니다.

예시:

-- 첫 번째 스레드는 nextval=100을 받고, 두 번째는 잠시 후 nextval=101을 받습니다.
-- 첫 번째 요청이 롤백하더라도 100은 이미 사용 중입니다.

이야기 #1

MySQL에서 PostgreSQL로 데이터베이스를 이전한 후 SEQUENCE와 IDENTITY의 차이를 잊고 수동으로 id를 삽입하려고 시도한 결과 "duplicate key" 오류가 발생했습니다. 이를 수정하기 위해 키 관리를 SEQUENCE로 완전히 이전했습니다.

이야기 #2

autoincrement가 있는 테이블에서 행을 부분적으로 삭제한 후 id 범위를 "빽빽하게" 채워야 할 필요가 있었습니다. 통제되지 않은 새로운 행 생성으로 모호함이 생겼고 — 키가 비즈니스 논리와 일치하지 않게 되었으며, 제어를 위해 개별 SEQUENCE로 전환해야 했습니다.

이야기 #3

대규모 OLTP 시스템에서 SEQUENCE 제어가 없어서 주문 번호가 2,147,483,647를 초과하여 통합 API에서 비상 중단이 발생했습니다. 이는 bigint 값에 대해 준비가 되어 있지 않았기 때문입니다. 요청은 SEQUENCE에 대한 제어 한계를 가진 새로운 구현으로 다시 작성되었습니다.

SQL에서 고유 키를 자동으로 생성할 때 SEQUENCE/IDENTITY의 생성 및 처리 방식은 어떻게 작동하며 본질적으로 무엇이 다릅니까? 발생할 수 있는 잠재적인 문제는 무엇입니까?

답변

함정 질문