交易與鎖定：ACID 不只是面試題
隔離層級、髒讀、幻讀與死鎖實戰

-- 轉帳範例：從帳戶 A 轉 1000 元到帳戶 B
BEGIN;  -- 開始交易

UPDATE accounts SET balance = balance - 1000 WHERE id = 'A';
UPDATE accounts SET balance = balance + 1000 WHERE id = 'B';

-- 如果中途發生錯誤，用 ROLLBACK 回滾所有操作
-- ROLLBACK;

COMMIT;  -- 確認提交，所有變更才永久生效

-- ────────────────────────────────────────
-- 在應用層（Node.js + pg 為例）
const client = await pool.connect();
try {
    await client.query('BEGIN');
    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;
} finally {
    client.release();
}

-- 為單一交易設定隔離層級
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 'A';
-- ... 其他操作 ...
COMMIT;

-- 查看當前預設隔離層級
SHOW transaction_isolation;  -- PostgreSQL

-- 修改 session 預設
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;

// ❌ 錯誤做法：先讀後寫，沒有鎖（高並發時必然超賣！）
async function buyProductUnsafe(productId: number, userId: number) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const { rows } = await client.query('SELECT stock FROM products WHERE id = $1', [productId]);
    const stock = rows[0].stock;  // A 讀到 1，B 同時也讀到 1
    // A 和 B 都認為有庫存，各自執行 UPDATE → stock 變 -1，超賣！
    if (stock > 0) {
      await client.query('UPDATE products SET stock = stock - 1 WHERE id = $1', [productId]);
    }
    await client.query('COMMIT');
  } finally {
    client.release();
  }
}

// ─────────────────────────────────────────────────────────────────────
// ✅ 正確做法：SELECT FOR UPDATE 取得排他鎖，IF/ELSE 控制流在應用層
async function buyProduct(productId: number, userId: number) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    // FOR UPDATE：鎖定這列，其他交易的 FOR UPDATE 請求會阻塞到此交易結束
    const { rows } = await client.query(
      'SELECT stock FROM products WHERE id = $1 FOR UPDATE',
      [productId]
    );
    const stock = rows[0].stock;

    // 控制流在 Node.js，不是在 SQL（SQL 沒有 IF/ELSE，那是 PL/pgSQL 的語法）
    if (stock <= 0) {
      await client.query('ROLLBACK');
      throw new Error('Out of stock');
    }

    await client.query('UPDATE products SET stock = stock - 1 WHERE id = $1', [productId]);
    await client.query('INSERT INTO orders (product_id, user_id) VALUES ($1, $2)', [productId, userId]);
    await client.query('COMMIT');
  } catch (err) {
    // 主動 ROLLBACK（含 DB 錯誤、業務邏輯錯誤等所有異常）
    // 若連線已斷，ROLLBACK 本身可能失敗，但交易超時後 DB 會自動回滾
    try { await client.query('ROLLBACK'); } catch { /* 連線已斷，DB 自動回滾 */ }
    throw err;
  } finally {
    client.release();  // 無論如何都要釋放連線回 pool
  }
}