關聯式資料庫與 SQL（進階）：交易、並行控制與進階查詢

兩個人同時搶最後一張票，資料庫怎麼不出錯？

入門篇我們學會了用 SQL 把資料拆表、串外鍵、JOIN 回來。那是一個「只有你一個人」的世界。但真實系統從來不是這樣：選課系統開放的那一秒，可能有三千名學生同時對同一門課按下「加選」；社團報名只剩最後一個名額，兩個人在同一毫秒送出申請。如果資料庫只會老實地「先讀剩餘名額、判斷大於零、再寫回減一」，那麼兩個人都讀到「剩 1」、都判斷可以、都寫回「剩 0」——名額被超賣了，而且帳面上看起來完全正常。

這就是進階資料庫真正的戰場：並行(concurrency)。一旦多個交易(transaction)同時動到同一份資料，正確性就不再是「SQL 寫對」這麼簡單，而是「在交錯執行下，結果仍然像是某種合理的循序執行」。這篇文章要深入交易、隔離級別、多版本並行控制(MVCC)，並順帶把入門篇沒談的進階 SQL——視窗函數與 CTE——一起補上。這些才是讓資料庫從「能用」走向「能在高負載下不出錯」的關鍵機制。

交易：要嘛全做，要嘛全不做

入門篇提過交易的 ACID 四性質，但只是點名。這裡我們要真正用起來。一個交易是一組被當成單一邏輯單位的 SQL 操作，由 BEGIN（或 START TRANSACTION）開始，以 COMMIT（確認）或 ROLLBACK（回滾）結束。

最經典的例子是轉帳：把甲帳戶扣 1000、乙帳戶加 1000。這兩件事必須同生共死，不能扣了甲卻沒加到乙：

START TRANSACTION;

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

COMMIT;  -- 兩行都成功才一起生效

如果第二行執行前系統當機，COMMIT 沒跑到，整個交易會被回滾，甲帳戶的扣款也跟著消失，彷彿什麼都沒發生。這就是原子性(atomicity)：交易是不可分割的最小單位。配合持久性(durability)——一旦 COMMIT 回報成功，即使下一秒斷電，資料也保證寫入了——資料庫才能成為「可信賴」的儲存。

原子性與持久性背後的功臣是預寫日誌(Write-Ahead Logging, WAL)：資料庫在真正修改資料頁之前，先把「我要做什麼變更」寫進一份循序的日誌檔。當機重啟後，資料庫重放(redo)已 COMMIT 但還沒落盤的變更、回滾(undo)沒 COMMIT 的變更，就能回到一致狀態。我們在開頭的轉帳能「彷彿沒發生」，靠的正是 undo log。

並行的四種惡夢：隔離不足會發生什麼

真正棘手的是隔離性(isolation)。理想上，每個交易都該覺得自己是世界上唯一的交易；但為了效能，資料庫允許交易交錯執行，於是會冒出各種異常(anomaly)。SQL 標準定義了四種典型異常，理解它們是理解隔離級別的前提。

髒讀(dirty read)：交易 A 改了某列但還沒 COMMIT，交易 B 卻讀到了這個「未確認」的值。如果 A 隨後 ROLLBACK，B 就讀到了一個從未真正存在的幽靈資料。

不可重複讀(non-repeatable read)：交易 A 在同一個交易內讀同一列兩次，中間交易 B 改掉並 COMMIT 了那列，於是 A 兩次讀到不同的值——明明是同一個交易，世界卻在腳下變了。

幻讀(phantom read)：交易 A 用某條件查了一批列（例如「所有餘額 > 500 的帳戶」），交易 B 插入了一筆符合條件的新列並 COMMIT，A 再查一次同樣的條件，竟多出一列「幽靈」。差別在於：不可重複讀是既有的列「值變了」，幻讀是「列數變了」。

遺失更新(lost update)：就是開頭搶票的情境。兩個交易都讀到「剩 1」，各自算出「剩 0」再寫回，後寫的覆蓋了先寫的，其中一次的扣減憑空消失。

四個隔離級別：在正確與速度之間選一個刻度

為了讓開發者依需求取捨，SQL 標準定義了四個隔離級別(isolation level)，由鬆到嚴，能擋掉的異常逐漸增加：

級別愈高，正確性愈強，但並行度（吞吐量）通常愈低。設定方式很直接：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
-- ... 你的查詢與更新 ...
COMMIT;

表中「可重複讀」那一格我標了星號，因為這裡藏著一個常見迷思值得拆解。SQL 標準說可重複讀「允許幻讀」，但 MySQL 的 InnoDB 引擎在可重複讀級別下，透過 next-key locking 實際上幾乎擋住了幻讀；而 PostgreSQL 的可重複讀（其實是快照隔離 snapshot isolation）也不會出現標準定義的幻讀。不同資料庫對同一個級別名稱的實作行為不同——這是工程師踩過無數次的雷。不要背「哪個級別擋哪個異常」的標準表格就以為通用，要查你實際用的那套資料庫的文件。

各家的預設級別也不同：MySQL/InnoDB 預設是可重複讀，PostgreSQL、SQL Server、Oracle 多半是讀已提交。預設值會直接影響你程式的正確性，這是上線前必須確認的一件事。

MVCC：不靠鎖也能讀到一致的世界

早期資料庫用鎖(lock)來實現隔離：讀的時候上共享鎖、寫的時候上排他鎖，互相排隊。問題是「讀」會擋住「寫」、「寫」會擋住「讀」，並行度被嚴重壓縮。現代主流資料庫（PostgreSQL、MySQL/InnoDB、Oracle）改用更聰明的設計：多版本並行控制(Multi-Version Concurrency Control, MVCC)。

核心想法是：不要覆蓋舊資料，而是保留多個版本。每一列都帶有版本資訊（例如建立它的交易 ID xmin 與刪除它的交易 ID xmax）。當交易 A 更新一列，資料庫不是直接改原地，而是產生一個新版本，舊版本仍保留。每個交易在開始時拿到一個快照(snapshot)，它只看得見「在我這個快照時間點之前已 COMMIT 的版本」。

這帶來一句金句般的性質：讀不擋寫，寫不擋讀(readers don't block writers, writers don't block readers)。交易 A 在讀某列的舊版本時，交易 B 完全可以同時寫出新版本，兩者井水不犯河水。讀操作幾乎永遠不必等待，這對「讀多寫少」的應用（絕大多數網站都是）是巨大的效能勝利。

當然天下沒有白吃的午餐。舊版本會堆積，必須有機制回收沒有任何交易再需要的「死版本」——在 PostgreSQL 叫 VACUUM，在 InnoDB 叫 purge。回收不及時會導致表膨脹(table bloat)，這是 MVCC 系統運維上的經典課題。

動手算一下：搶票為什麼還是會超賣

回到開頭的搶票。假設 tickets 表只剩 1 張，兩個交易這樣寫：

-- 交易 1 與交易 2 各自執行（讀已提交級別）
START TRANSACTION;
SELECT remaining FROM tickets WHERE event_id = 1;   -- 兩者都讀到 remaining = 1
-- 應用程式判斷 remaining > 0，決定可以賣
UPDATE tickets SET remaining = remaining - 1 WHERE event_id = 1;
COMMIT;

在讀已提交甚至可重複讀的快照下，兩個交易的 SELECT 都讀到 remaining = 1，應用層都判斷「還有票」。雖然兩次 UPDATE 因為用了 remaining - 1 的相對寫法不會把值寫成 0 兩次（最終會是 -1），但業務上你已經賣出兩張只剩一張的票了。問題不在 SQL 寫錯，而在「讀」與「決策」之間的窗口被另一個交易插隊。

修法有幾種。最直接的是在讀的當下就上鎖，讓第二個交易排隊：

START TRANSACTION;
-- FOR UPDATE 對選中的列加排他鎖，第二個交易會卡在這行直到第一個 COMMIT
SELECT remaining FROM tickets WHERE event_id = 1 FOR UPDATE;
UPDATE tickets SET remaining = remaining - 1 WHERE event_id = 1;
COMMIT;

SELECT ... FOR UPDATE 是悲觀鎖(pessimistic locking)：先假設一定會衝突，搶先上鎖。另一種是樂觀鎖(optimistic locking)：先不鎖，更新時用條件確保值沒被別人動過：

-- 只有在 remaining 仍 >= 1 時才扣，並回報實際影響的列數
UPDATE tickets SET remaining = remaining - 1
WHERE event_id = 1 AND remaining >= 1;
-- 若影響列數為 0，代表票被搶光了，應用層據此回報失敗

這個查詢把「檢查」和「扣減」合併成一個原子操作，靠的是資料庫對單一 UPDATE 的列級鎖。影響列數(affected rows)是否為 1，就是這次有沒有搶到的判準。哪種好？衝突頻繁用悲觀鎖、衝突罕見用樂觀鎖，這是並行設計的核心權衡。

進階 SQL：視窗函數讓你不必把表 JOIN 自己

並行之外，進階 SQL 本身也大有學問。入門篇的 GROUP BY 會「把每一組壓成一列」，但很多分析需求是「保留每一列，同時看到它在群組中的位置」——這時 GROUP BY 就力不從心。視窗函數(window function) 正是為此而生。

假設要為每個系所內的學生依成績排名，同時保留每位學生的原始資料：

SELECT
    name,
    department,
    score,
    RANK() OVER (PARTITION BY department ORDER BY score DESC) AS dept_rank
FROM students;

關鍵是 OVER (...) 子句，它定義了一個「視窗」：PARTITION BY department 把資料切成一個個系所群組，ORDER BY score DESC 在每組內排序，RANK() 給出排名。和 GROUP BY 最大的差別是：列數不變——每位學生都留著，只是多了一欄排名。常用的視窗函數還有 ROW_NUMBER()（不重複的流水號）、DENSE_RANK()（並列不跳號）、以及 LAG()／LEAD()（取前一列／後一列的值，做時序差分超好用）。

-- 計算每位學生比同系前一名（成績較高者）低多少分
SELECT
    name, department, score,
    score - LAG(score) OVER (PARTITION BY department ORDER BY score DESC) AS gap_to_next
FROM students;

看一個例子：用 CTE 把複雜查詢拆成可讀的步驟

當查詢愈來愈複雜，巢狀子查詢會讓人讀到頭痛。公共表達式(Common Table Expression, CTE) 用 WITH 把中間步驟命名，像寫程式定義變數一樣，讓查詢由上而下一步步推進。

假設要找出「每個系所中成績最高的那位學生」：

WITH ranked AS (
    SELECT
        name, department, score,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY score DESC) AS rn
    FROM students
)
SELECT name, department, score
FROM ranked
WHERE rn = 1;

第一步 ranked 為每位學生算出系內排名，第二步只留排名第一者。如果不用 CTE 與視窗函數，這個「每組取最大」的需求要寫成令人費解的相關子查詢(correlated subquery)。CTE 把邏輯攤平成可讀的階段，這對團隊維護是巨大的價值。

CTE 還能遞迴(recursive)，這讓 SQL 有能力處理樹狀與圖狀結構。例如查詢某主題下的所有子主題（包含子主題的子主題），這在純粹的 JOIN 裡是辦不到的：

WITH RECURSIVE subtree AS (
    SELECT id, name, parent_id FROM categories WHERE id = 10   -- 起點(錨點)
    UNION ALL
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    INNER JOIN subtree s ON c.parent_id = s.id                 -- 遞迴往下展開
)
SELECT * FROM subtree;

錨點查詢給出起點，遞迴部分不斷把「子節點」併入結果，直到沒有新節點為止。一句 SQL 就走完整棵子樹。

重點回顧

• 交易是要嘛全做要嘛全不做的單位，原子性與持久性靠預寫日誌(WAL)的 redo/undo 在當機後復原。
• 並行不足會出現髒讀、不可重複讀、幻讀、遺失更新四種異常；SQL 的四個隔離級別由鬆到嚴逐一擋掉它們，但各家資料庫對同名級別的實作行為不同，且預設級別不一，上線前務必確認。
• MVCC 用多版本與快照實現「讀不擋寫、寫不擋讀」，代價是死版本需靠 VACUUM／purge 回收，否則表膨脹。
• 並行衝突可用 FOR UPDATE（悲觀鎖）或條件式 UPDATE + 影響列數（樂觀鎖）解決，依衝突頻率選擇。
• 視窗函數保留列數做組內排名與時序差分，CTE（含遞迴）讓複雜與樹狀查詢變得可讀。

深入探討（研究所視角）

隔離級別的標準表格只是工程上的近似刻度，背後真正嚴謹的理論是可序列化(serializability)。給定一群並行交易的交錯執行（稱為一個 schedule），若其結果等價於「這些交易以某個順序逐一循序執行」的結果，這個 schedule 就是可序列化的——這是並行正確性的黃金標準。最常用的判定工具是衝突可序列化(conflict serializability)：建構一張優先圖(precedence graph)，節點是交易，當交易 $T_i$ 的某操作與 $T_j$ 的操作衝突（兩者存取同一資料項且至少一個是寫）且 $T_i$ 在前，就畫一條 $T_i \to T_j$ 的邊。該圖無環，當且僅當 schedule 是衝突可序列化的。判斷有無環只需一次拓樸排序，複雜度 $O(V + E)$。

實作可序列化有兩大流派。兩階段鎖定(Two-Phase Locking, 2PL) 規定每個交易分成「成長期」（只能取鎖）與「收縮期」（只能放鎖），一旦放了第一把鎖就不能再取——可以證明遵守 2PL 的 schedule 必為衝突可序列化。但 2PL 會引發死結(deadlock)：交易 A 等 B 的鎖、B 又等 A 的鎖，互相卡死。資料庫用「等待圖(wait-for graph)找環」偵測死結，再挑一個交易當犧牲者回滾打破僵局，或用逾時(timeout)粗暴解決。嚴格 2PL（鎖全部留到 COMMIT 才放）還能避免級聯回滾(cascading rollback)，是多數鎖式系統的實作選擇。

另一流派是 MVCC 衍生的快照隔離(Snapshot Isolation, SI)。SI 讓每個交易讀自己開始時的快照、寫時用「先寫者贏(first-committer-wins)」解決衝突，效能極佳，但它並非真正可序列化——它會放過一種微妙的異常叫寫偏斜(write skew)。經典例子：醫院規定「至少要有一位醫師值班」，兩位值班醫師 A、B 各開一個交易，都讀到「現在有兩人值班，我請假還剩一人」，於是各自把自己設為休假。兩個交易讀的是各自快照、寫的是不同列（沒有寫寫衝突），SI 全部放行，結果變成零人值班——一個任何循序執行都不可能產生的非法狀態。為了補上這個漏洞，PostgreSQL 實作了可序列化快照隔離(Serializable Snapshot Isolation, SSI)：在 SI 之上追蹤交易間的讀寫依賴，偵測到可能破壞可序列化的「危險結構」（兩條反向的 rw 依賴邊）時，主動中止其中一個交易。SSI 讓使用者享受 MVCC 的高並行，又拿回真正的可序列化保證，是過去二十年資料庫並行控制最漂亮的理論落地之一。

這些並行控制機制最終都服務於同一個目標：在不可避免的交錯執行中，給開發者一個「彷彿一切都是循序發生」的乾淨抽象。當系統規模再往上，單機的鎖與 MVCC 不夠用時，問題就延伸到分散式交易的兩階段提交(2PC)、共識協定(Raft/Paxos)，以及 CAP 定理下一致性與可用性的取捨——但無論架構多複雜，「可序列化」始終是衡量正確性的那把尺。理解了從異常、隔離級別、MVCC 到可序列化理論這條線，你看待任何資料庫的並行行為，都會從「背規則」升級為「懂原理」。