인덱스 · 트랜잭션 · ACID · 격리수준

-- 인덱스 생성 (가장 흔한 케이스)
CREATE INDEX idx_users_email ON users(email);

-- 복합 인덱스 (컬럼 순서 중요!)
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at DESC);

-- 유니크 인덱스 = 인덱스 + 중복 방지
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 실행 계획 확인 — 인덱스 쓰는지 검증하는 핵심 명령
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'kim@example.com';
-- "Index Scan using idx_users_email"   ← 인덱스 사용 중 ✅
-- "Seq Scan on users"                  ← 풀스캔 중 ❌ (인덱스 없거나 안 먹힘)

-- ❌ 위험: 트랜잭션 없이 두 줄
UPDATE accounts SET balance = balance - 1000 WHERE id = 'A';
-- 여기서 서버 크래시 → B는 못 받음, A만 손해
UPDATE accounts SET balance = balance + 1000 WHERE id = 'B';

-- ✅ 안전: 트랜잭션으로 묶기
BEGIN;
  UPDATE accounts SET balance = balance - 1000 WHERE id = 'A';
  UPDATE accounts SET balance = balance + 1000 WHERE id = 'B';
COMMIT;
-- 중간에 에러나면 전체 ROLLBACK

-- 실전: 앱 코드 (Node.js + Postgres)
await client.query('BEGIN');
try {
  await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [1000, 'A']);
  await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [1000, 'B']);
  await client.query('COMMIT');
} catch (err) {
  await client.query('ROLLBACK');   // ← 필수
  throw err;
}

-- 재고 차감 같은 민감 로직 → 격리 수준 올리기
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

  SELECT stock FROM products WHERE id = 'X';   -- 100개
  -- 다른 트랜잭션이 동시에 재고 차감 시도 시 직렬화 실패로 롤백
  UPDATE products SET stock = stock - 1 WHERE id = 'X';

COMMIT;

-- 또는 더 가벼운 방법: 행 잠금
BEGIN;
  SELECT stock FROM products WHERE id = 'X' FOR UPDATE;  -- ← 이 행 잠금
  UPDATE products SET stock = stock - 1 WHERE id = 'X';
COMMIT;