CREATE TABLE inventory (
    id          BIGSERIAL PRIMARY KEY,
    product_id  BIGINT NOT NULL UNIQUE,
    stock       INTEGER NOT NULL CHECK (stock >= 0),
    version     INTEGER NOT NULL DEFAULT 0,  -- ← 낙관적 락용 버전
    updated_at  TIMESTAMPTZ DEFAULT NOW()
);

-- 인덱스: product_id로 조회가 많으므로
CREATE INDEX idx_inventory_product_id ON inventory(product_id);

CREATE TABLE seats (
    id          BIGSERIAL PRIMARY KEY,
    seat_number VARCHAR(10) NOT NULL,
    is_reserved BOOLEAN DEFAULT FALSE,
    updated_at  TIMESTAMPTZ DEFAULT NOW()  -- ← 버전 대신 타임스탬프
);

-- 조회 시 updated_at도 함께 읽기
SELECT is_reserved, updated_at FROM seats WHERE id = 101;

-- 수정 시 조건으로 확인
UPDATE seats
SET is_reserved = TRUE, updated_at = NOW()
WHERE id = 101 AND updated_at = '2024-03-15 14:23:01.123456+00';

INSERT INTO inventory (product_id, stock, version)
VALUES (42, 10, 0),
       (99, 1, 0);

-- 확인
SELECT * FROM inventory;

import time
import random
from sqlalchemy.orm import exc as orm_exc

def with_optimistic_retry(func, max_retries: int = 3, base_delay: float = 0.1):
    """
    낙관적 락 충돌 시 지수 백오프로 재시도.
    max_retries=3, base_delay=0.1초 → 0.1s, 0.2s, 0.4s 대기
    """
    for attempt in range(max_retries + 1):
        try:
            return func()
        except orm_exc.StaleDataError:
            if attempt == max_retries:
                # 최대 재시도 초과 → 상위로 예외 전파
                raise RuntimeError(
                    f"낙관적 락 충돌: {max_retries}회 재시도 후 실패"
                )

            # 지수 백오프 + 지터(jitter) — 동시 재시도 폭풍 방지
            delay = base_delay * (2 ** attempt) + random.uniform(0, 0.05)
            print(f"충돌 감지, {delay:.2f}초 후 재시도 (시도 {attempt + 1}/{max_retries})")
            time.sleep(delay)
            session.rollback()  # 재시도 전 세션 초기화 필수!

# 사용 예시
def purchase_item(product_id: int, quantity: int):
    def _do_purchase():
        with Session(engine) as session:
            decrease_stock(session, product_id, quantity)

    with_optimistic_retry(_do_purchase, max_retries=3)

@Service
public class InventoryService {

    private static final int MAX_RETRIES = 3;

    public void decreaseStockWithRetry(Long productId, int quantity) {
        int attempt = 0;

        while (attempt <= MAX_RETRIES) {
            try {
                decreaseStock(productId, quantity);
                return; // 성공
            } catch (OptimisticLockingFailureException e) {
                attempt++;
                if (attempt > MAX_RETRIES) {
                    throw new RuntimeException("재고 차감 실패: 동시 요청 충돌", e);
                }

                long delayMs = (long) (100 * Math.pow(2, attempt - 1))
                             + ThreadLocalRandom.current().nextLong(50);
                log.warn("낙관적 락 충돌, {}ms 후 재시도 ({}/{})", delayMs, attempt, MAX_RETRIES);

                try {
                    Thread.sleep(delayMs);
                } catch (InterruptedException ie) {
                    Thread.currentThread().interrupt();
                    throw new RuntimeException("재시도 중단", ie);
                }
            }
        }
    }
}

CREATE TABLE courses (
    id           BIGSERIAL PRIMARY KEY,
    name         VARCHAR(200) NOT NULL,
    capacity     INTEGER NOT NULL,         -- 정원
    enrolled     INTEGER NOT NULL DEFAULT 0, -- 현재 수강 인원
    version      INTEGER NOT NULL DEFAULT 0, -- 낙관적 락 버전
    CHECK (enrolled >= 0),
    CHECK (enrolled <= capacity)
);

CREATE TABLE enrollments (
    id         BIGSERIAL PRIMARY KEY,
    course_id  BIGINT NOT NULL REFERENCES courses(id),
    student_id BIGINT NOT NULL,
    enrolled_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (course_id, student_id)         -- 중복 수강 방지
);

-- 테스트 강의 삽입
INSERT INTO courses (name, capacity, enrolled, version)
VALUES ('PostgreSQL 고급 과정', 30, 28, 0); -- 잔여 2석

-- Step 1: 잠금 없이 현재 상태 조회
SELECT id, capacity, enrolled, version
FROM courses
WHERE id = 1;
-- 결과: id=1, capacity=30, enrolled=28, version=5

-- Step 2: 정원 확인 후 version 조건 포함 UPDATE
-- (애플리케이션에서 enrolled < capacity 검증 후 실행)
UPDATE courses
SET enrolled = enrolled + 1,
    version  = version + 1
WHERE id = 1
  AND version = 5          -- ← 낙관적 락
  AND enrolled < capacity; -- ← DB 레벨 이중 안전장치

-- affected rows 확인:
-- 1 → 성공 → enrollments에 INSERT
-- 0 → 실패 (충돌 또는 정원 초과) → 재조회 후 판단

from sqlalchemy import text
from sqlalchemy.orm import Session

class EnrollmentService:

    def enroll(self, session: Session, course_id: int, student_id: int) -> str:
        """
        낙관적 락 기반 수강신청.
        Returns: "success" | "full" | "already_enrolled"
        """
        max_retries = 3

        for attempt in range(max_retries):
            # Step 1: 현재 상태 조회 (잠금 없음)
            row = session.execute(
                text("SELECT id, capacity, enrolled, version FROM courses WHERE id = :id"),
                {"id": course_id}
            ).fetchone()

            if row is None:
                raise ValueError("강의를 찾을 수 없습니다")

            # Step 2: 정원 초과 확인 (애플리케이션 레벨)
            if row.enrolled >= row.capacity:
                return "full"

            # Step 3: 중복 수강 확인
            existing = session.execute(
                text("SELECT 1 FROM enrollments WHERE course_id=:c AND student_id=:s"),
                {"c": course_id, "s": student_id}
            ).fetchone()
            if existing:
                return "already_enrolled"

            # Step 4: 낙관적 락 UPDATE
            affected = session.execute(
                text("""
                    UPDATE courses
                    SET enrolled = enrolled + 1, version = version + 1
                    WHERE id = :id AND version = :version AND enrolled < capacity
                """),
                {"id": course_id, "version": row.version}
            ).rowcount

            if affected == 1:
                # 성공: 수강 이력 기록
                session.execute(
                    text("INSERT INTO enrollments (course_id, student_id) VALUES (:c, :s)"),
                    {"c": course_id, "s": student_id}
                )
                session.commit()
                return "success"
            else:
                # 충돌: 재시도
                session.rollback()
                if attempt < max_retries - 1:
                    time.sleep(0.05 * (2 ** attempt))  # 지수 백오프

        return "conflict"  # 최대 재시도 초과

-- 두 터미널에서 동시에 실행해 충돌 재현
-- 터미널 A:
BEGIN;
SELECT enrolled, version FROM courses WHERE id = 1;  -- enrolled=28, version=5
-- (여기서 잠깐 멈춤)

-- 터미널 B: (먼저 커밋)
BEGIN;
UPDATE courses SET enrolled=29, version=6 WHERE id=1 AND version=5;
COMMIT;  -- 성공

-- 터미널 A: (늦게 시도)
UPDATE courses SET enrolled=29, version=6 WHERE id=1 AND version=5;
-- affected rows = 0 → 충돌 감지 → ROLLBACK 후 재시도
ROLLBACK;