SQL: 從建構開始就錯誤？

一個轉帳的教科書案例——Alice 向 Bob 轉帳十元——看起來很直觀。你檢查 Alice 的餘額，確保她有足夠的資金，從她的帳戶中扣除該金額，然後將其添加到 Bob 的帳戶中。在大多數程式語言中，這種邏輯是直觀的。然而，在關聯式資料庫的世界中，這種「合理」的程式碼往往是併發錯誤（concurrency bugs）的雷區。

當開發人員將 SQL 視為簡單的腳本語言，而不是一個在併發情況下管理共享狀態的系統時，他們會遇到三個主要的陷阱：原子性失敗（atomicity failures）、檢查時與使用時（Time-of-Check to Time-of-Use, TOCTOU）錯誤，以及死結（deadlocks）。

併發失敗的解剖

考慮一個基本的 T-SQL 轉帳實作：

DECLARE @balance INT;

SET @balance = (  
    SELECT balance  
    FROM accounts
    WHERE owner = 'alice'
);

IF @balance >= 10  
BEGIN  
    UPDATE accounts  
    SET balance = balance - 10  
    WHERE owner = 'alice';  
  
    UPDATE accounts  
    SET balance = balance + 10  
    WHERE owner = 'bob';  
END

1. 原子性差距

如果系統在第一次 UPDATE 之後但在第二次之前崩潰或程序中止，資金會從 Alice 的帳戶中消失，卻從未到達 Bob。為了確保兩個更新操作要麼同時發生，要麼都不發生，該操作必須封裝在交易（BEGIN TRANSACTION 和 COMMIT TRANSACTION）中。

2. TOCTOU 競態條件

即使使用了交易，Time-of-Check to Time-of-Use (TOCTOU) 錯誤仍然存在。如果 Alice 同時發起兩次轉帳（T1 和 T2），兩者都可能在任何一方執行提款之前讀取她的餘額。兩個交易都看到足夠的餘額，兩者都繼續提款，結果 Alice 的帳戶最終變成透支狀態。

為了修復此問題，開發人員必須使用鎖定提示（例如 T-SQL 中的 UPDLOCK 或其他方言中的 SELECT FOR UPDATE）來確保在讀取該行時立即鎖定它，從而防止其他交易在當前交易完成之前修改它。

3. 死結陷阱

鎖定會引入一個新問題：死結。如果 Alice 向 Bob 轉帳，而 Bob 同時向 Alice 轉帳，T1 可能會鎖定 Alice 的帳戶並等待 Bob 的帳戶，而 T2 會鎖定 Bob 的帳戶並等待 Alice 的帳戶。兩者都無法繼續進行。

雖然作者建議預先獲取所有鎖，但更穩健的業界標準是按照一致且確定的順序獲取鎖（例如，按帳戶 ID 排序）。

SQL 是「從建構開始就錯誤」嗎？

核心論點是 SQL 的設計使得編寫錯誤的程式碼變得太容易。作者建議，對於正確性至關重要的系統（例如醫療劑量），我們需要一種類似於 Rust 的「無畏併發」（fearless concurrency）模型，其中正確的行為是預設值，而「不安全」的操作是顯式的。

然而，這項主張在經驗豐富的資料庫工程師中引起了激烈的辯論。許多人認為這些問題並非 SQL 的建構缺陷，而是「SQL 101」的概念。

反對論點

社群針對這些所謂的「缺陷」提出了幾個關鍵點：

  • 隔離層級： 許多描述的問題是弱預設隔離層級（如 READ COMMITTED）的產物。真正的 SNAPSHOT 隔離或 SERIALIZABLE 層級可以透過將衝突轉化為重試而非靜默失敗，來消除許多這類競態條件。
  • 引擎智慧： 現代資料庫引擎（MySQL, SQL Server）並非死結的被動觀察者。它們會主動檢測死結循環，選擇一個「受害者」交易來回滾，並返回一個特定的錯誤（例如 SQL Server 中的 Error 1205），並期望應用程式進行重試。
  • 慣用法模式： 經驗豐富的開發人員會完全避免「先檢查再執行」的模式。相反，他們使用條件式更新：
    UPDATE accounts SET balance = balance - 10 
WHERE owner = 'alice' AND balance >= 10;
    這將檢查和動作結合成引擎層級的一個單一原子操作。

架構替代方案

除了修復 T-SQL 語法之外，討論還強調了兩種用於財務數據的優越架構模式：

帳本模式 (Append-Only)

與其更新一個容易因競態條件而產生變動狀態的 balance 欄位——這是一個可變狀態——許多人建議使用僅限追加的帳本。在此模型中，你從不 UPDATE 餘額；你只 INSERT 交易行（例如，Alice: -10, Bob: +10）。目前的餘額則是所有帳本分錄的總和。

應用層併發控制

一些開發人員將併發控制移至應用層，或使用分散式鎖定機制來確保一次只有一個程序可以修改特定的資源，從而減少對資料庫內部鎖定機制的依賴。

結論

無論 SQL 是「從建構開始就錯誤」還是僅僅需要對 ACID 屬性的嚴謹理解，教訓仍然是：在併發系統中，「看起來合理的程式碼」與「正確的程式碼」之間的距離是非常巨大的。對於關鍵系統，僅依賴預設值是很少夠的；顯式的鎖定策略、嚴格的隔離層級或不可變的帳本架構對於維持數據完整性至關重要。

Sources