一致性#

關聯式資料庫的關鍵特性，是能確保資料一致性（consistency），也就是資料的正確性。

眾所周知，我們可以在資料庫層級建立完整性約束（integrity constraint），例如 NOT NULL 或 UNIQUE。資料庫系統保證這些約束永不被破壞，完整性因此不會受損。

但一致性比完整性更嚴格：

• 有些條件太過複雜，無法在資料庫層級表述（例如同時牽涉多張表格）
• 即使某個約束能在資料庫中定義，卻因故沒有定義，也不代表它可以被違反

資料庫系統其實不知道「一致性」是什麼意思。若應用程式在不破壞完整性的前提下破壞了一致性，資料庫無從察覺。因此，一致性的判準必須由應用程式訂定，我們只能相信它寫得正確、永不出錯。

那資料庫系統扮演什麼角色？#

首先，正確的操作序列可以暫時破壞一致性，而且這完全正常。

經典（雖然老套）的例子是帳戶轉帳。一致性規則可能是：轉帳絕不能改變相關帳戶的總餘額。這規則要寫成 SQL 完整性約束相當困難（雖非不可能），因此假設它定義在應用程式層級、對資料庫不透明。一筆轉帳包含兩個操作：第一個從某帳戶扣款（破壞一致性），第二個把款項加到另一帳戶（恢復一致性）。

若第一個操作成功、第二個因故失敗，一致性就被破壞了。這種情況不可接受，而要在應用程式層級偵測並處理它得花上大量心力。

幸好不必如此——只要資料庫系統知道這兩個操作構成不可分割的整體，也就是一筆交易（transaction），問題就能被完全解決。

並行帶來的微妙問題#

還有一個更微妙的面向：各自完全正確的交易，並行執行時可能開始出錯。原因是不同交易的操作經常彼此穿插。

如果資料庫系統先完整跑完一筆交易再跑下一筆，就不會有這些問題——但循序執行的效能會低到不可思議。

真正同時執行交易只能在合適硬體上達成（多核心處理器、磁碟陣列等）。但同樣的推理也適用於以分時模式循序執行指令的伺服器。為了一般化，這兩種情況有時統稱為並行執行（concurrent execution）。

各自正確、合在一起卻出錯的交易，會導致並行異常（concurrency anomaly），或稱現象（phenomenon）。

舉個簡單例子：應用程式要從資料庫取得一致的資料，最起碼不能看見其他未提交交易所做的變更。否則（若那些交易被回滾）它看到的會是從未存在過的資料庫狀態。這種異常稱為髒讀（dirty read）。其他還有許多更複雜的異常。

並行執行交易時，資料庫必須保證執行結果與某一種可能的循序執行結果相同。換言之，它必須把交易彼此隔離（isolate），從而處理掉所有可能的異常。

這個定義結合了 ACID 前三個字母所隱含的要求。它們彼此交織得如此緊密，一起討論才有意義。事實上持久性（durability）也很難切割開來：當機後系統中可能仍留有未提交交易所做的變更，你得處理它才能恢復一致性。

完整隔離難以實作，且會對效能造成負面影響。大多數真實系統使用較弱的隔離層級，只防止部分異常。這意味著維護一致性的工作有一部分落到應用程式身上——這正是為什麼你必須清楚知道系統使用哪個隔離層級、該層級保證什麼、不保證什麼，以及如何在這種條件下寫出正確的程式碼。

SQL 標準中的隔離層級與異常#

SQL 標準定義四種隔離層級。這些層級是由「並行執行時可能或不可能發生的異常清單」來定義的，因此談隔離層級必須從異常談起。

標準是一個理論建構：它影響實務，但實務在許多方面仍與它分歧。有趣的是，實際的資料庫理論也與標準分歧——理論是在標準通過之後才發展的，而當時實務已遠遠走在前面。

更新遺失#

更新遺失（lost update）發生在兩筆交易讀取同一表格列，其中一筆更新該列，另一筆接著在不考慮前者變更的情況下更新同一列。

例如兩筆交易都要把同一帳戶餘額增加 $100：

1. 第一筆讀到目前值 $1,000
2. 第二筆也讀到 $1,000
3. 第一筆把餘額加到 $1,100 並寫入
4. 第二筆同樣得到 $1,100 並寫入

結果客戶損失了 $100。

標準在所有隔離層級都禁止更新遺失。

髒讀與 Read Uncommitted#

髒讀（dirty read）發生在交易讀取了另一筆交易未提交的變更。

例如第一筆交易轉了 $100 到一個空帳戶但未提交。另一筆交易讀到該帳戶狀態（已更新但未提交），於是允許客戶提款——即使第一筆交易後來被中斷、變更被回滾，帳戶其實是空的。

標準允許髒讀出現在 Read Uncommitted 層級。

不可重複讀與 Read Committed#

不可重複讀（non-repeatable read）發生在交易兩次讀取同一列，而另一筆交易在兩次讀取之間更新（或刪除）該列並提交，導致第一筆交易得到不同結果。

例如有條一致性規則禁止帳戶餘額為負。第一筆交易要把餘額減少 $100，檢查後得到 $1,000，判定操作可行。同時另一筆交易把該帳戶的錢全部提走並提交。若第一筆交易此時再查一次餘額會得到 $0——但提款決策已經做出，該操作將導致透支。

標準允許不可重複讀出現在 Read Uncommitted 與 Read Committed 層級。

幻讀與 Repeatable Read#

幻讀（phantom read）發生在同一交易執行兩次相同查詢（回傳滿足特定條件的列集合），而另一筆交易在兩次查詢之間新增了其他滿足該條件的列並提交，導致第一筆交易得到兩組不同的列集合。

例如有條規則禁止客戶擁有超過三個帳戶。第一筆交易要開新帳戶，先檢查目前有幾個（假設兩個），判定可行。就在此刻，第二筆交易也為該客戶開了新帳戶並提交。若第一筆交易再檢查一次會得到三個——但它已經在開另一個帳戶了，客戶最終有四個。

標準允許幻讀出現在 Read Uncommitted、Read Committed 與 Repeatable Read 層級。

無異常與 Serializable#

標準也定義了 Serializable 層級，它不允許任何異常。

這不等同於「禁止更新遺失與髒讀、不可重複讀、幻讀」。事實上已知的異常數量遠多於標準所列，還有未知數量的尚未發現者。

Serializable 層級必須防止任何異常。這意味著應用程式開發者不必把隔離納入考量：只要交易單獨執行時的操作序列是正確的，並行執行也不會破壞一致性。

以下是標準提供的知名對照表（最後一欄為原書為求清楚而加入）：

PostgreSQL 的隔離層級#

隨著時間演進，基於鎖的交易管理協定被快照隔離（Snapshot Isolation，SI）協定取代。其核心想法是：每筆交易存取的是資料在某個特定時間點的一致快照，快照包含所有在拍攝之前已提交的變更。

快照隔離把所需的鎖降到最低。事實上，只有並行更新嘗試才會鎖住列；其他所有情況下操作都能並行執行：寫從不鎖讀，讀從不鎖任何東西。

PostgreSQL 採用 SI 協定的多版本變體。多版本並行控制意味著資料庫系統在任一時刻可能包含同一列的多個版本，因此 PostgreSQL 能把合適的版本納入快照，而不必中止那些試圖讀取過期資料的交易。

基於快照的 PostgreSQL 隔離與標準的要求不同——事實上更嚴格：

• 髒讀在設計上就被禁止。技術上你可以指定 Read Uncommitted 層級，但其行為與 Read Committed 完全相同，因此本書不再提及該層級。
• Repeatable Read 既不允許不可重複讀，也不允許幻讀（儘管它不保證完整隔離）。
• 但在 Read Committed 層級仍有遺失變更的風險。

以下範例使用的 accounts 表格——Alice 與 Bob 各有 $1,000，但 Bob 有兩個帳戶：

=> CREATE TABLE accounts(
   id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
   client text,
   amount numeric
);
=> INSERT INTO accounts VALUES
  (1, 'alice', 1000.00), (2, 'bob', 100.00), (3, 'bob', 900.00);

Read Committed#

沒有髒讀#

預設隔離層級即為 Read Committed（由 default_transaction_isolation 參數設定，可視需要更改）：

=> BEGIN;
=> SHOW transaction_isolation;
 transaction_isolation
−−−−−−−−−−−−−−−−−−−−−−−
 read committed
(1 row)

已開啟的交易從客戶帳戶提走一些款項但尚未提交。它看得見自己的變更（這永遠是被允許的）：

=> UPDATE accounts SET amount = amount - 200 WHERE id = 1;
=> SELECT * FROM accounts WHERE client = 'alice';
 id | client | amount
−−−−+−−−−−−−−+−−−−−−−−
  1 | alice | 800.00
(1 row)

第二個 session 中的交易看不到任何未提交的變更——髒讀被禁止：

    => BEGIN;
    => SELECT * FROM accounts WHERE client = 'alice';
     id | client | amount
    −−−−+−−−−−−−−+−−−−−−−−−
      1 | alice | 1000.00
    (1 row)

不可重複讀#

第一筆交易提交後，第二筆交易重複同一查詢，就會拿到更新後的版本——這正是 Read Committed 所允許的不可重複讀異常：

=> COMMIT;

    => SELECT * FROM accounts WHERE client = 'alice';
     id | client | amount
    −−−−+−−−−−−−−+−−−−−−−−
      1 | alice | 800.00
    (1 row)
    => COMMIT;

以下這種寫法在應用程式碼中頻繁出現，堪稱經典的反模式：

IF (SELECT amount FROM accounts WHERE id = 1) >= 1000 THEN
  UPDATE accounts SET amount = amount - 1000 WHERE id = 1;
END IF;

在檢查與更新之間的時間裡，其他交易可以自由改變帳戶狀態，所以這種「檢查」毫無用處。想像其他交易的隨機運算子被「楔入」目前交易的運算子之間：

IF (SELECT amount FROM accounts WHERE id = 1) >= 1000 THEN

    UPDATE accounts SET amount = amount - 200 WHERE id = 1;
    COMMIT;

  UPDATE accounts SET amount = amount - 1000 WHERE id = 1;
END IF;

如果運算子一經重排就全盤出錯，那程式碼就是不正確的。別自欺欺人以為自己不會遇到——凡是可能出錯的都會出錯。這類錯誤極難重現，因此修復起來是真正的挑戰。

三種修正方式：

1. 以宣告式取代程序式。本例中很容易把 IF 敘述轉成 CHECK 約束——之後程式碼裡完全不需要檢查，只要執行指令並處理違反完整性約束時拋出的例外：

   ALTER TABLE accounts
     ADD CHECK amount >= 0;

2. 使用單一 SQL 運算子。一致性受損是因為另一筆交易在運算子之間的時間縫隙中提交、改變了資料可見性；只有一個運算子就沒有這種縫隙。PostgreSQL 有足夠能力用單一 SQL 敘述解決複雜任務，特別是可包含 INSERT／UPDATE／DELETE 的共用表格運算式（CTE），以及實作「不存在則插入、否則更新」邏輯的 INSERT ON CONFLICT。

3. 使用明確鎖。最後手段是手動對所有必要的列設定排他鎖（SELECT FOR UPDATE），甚至鎖住整張表格（LOCK TABLE）。這種做法一定有效，但會抵銷 MVCC 的所有優勢：原本能並行執行的操作將變成循序執行。

讀取偏斜#

事情沒那麼簡單。PostgreSQL 的實作還允許其他較鮮為人知、標準未規範的異常。

假設第一筆交易開始在 Bob 的帳戶間轉帳；同時另一筆交易開始逐一走訪 Bob 的所有帳戶以計算總餘額：

=> BEGIN;
=> UPDATE accounts SET amount = amount - 100 WHERE id = 2;

    => BEGIN;
    => SELECT amount FROM accounts WHERE id = 2;   -- 讀到舊值 100.00

此刻第一筆交易成功完成，第二筆交易接著讀第二個帳戶（看到已更新的值）：

=> UPDATE accounts SET amount = amount + 100 WHERE id = 3;
=> COMMIT;

    => SELECT amount FROM accounts WHERE id = 3;   -- 讀到新值 1000.00
    => COMMIT;

結果第二筆交易算出 $1,100，因為它讀到了不正確的資料。這種異常稱為讀取偏斜（read skew）。

在 Read Committed 層級如何避免？答案顯而易見：用單一運算子：

SELECT sum(amount) FROM accounts WHERE client = 'bob';

單一運算子內的一致性——與 VOLATILE 陷阱#

前面說「資料可見性只在運算子之間改變」，但查詢跑很久時真的如此嗎？可以用 pg_sleep 加延遲來驗證：第一列立刻讀到，第二列要等兩秒。

=> SELECT amount, pg_sleep(2) -- two seconds
FROM accounts WHERE client = 'bob';

執行期間另一筆交易把錢轉回去。結果顯示該運算子看到的是執行開始那一刻的資料狀態，這當然是正確的：

 amount | pg_sleep
−−−−−−−−−+−−−−−−−−−−
    0.00 |
 1000.00 |
(2 rows)

但這裡仍有陷阱：若查詢中含有宣告為 VOLATILE 的函式，而該函式又執行另一個查詢，則巢狀查詢看到的資料不會與主查詢結果一致。

=> CREATE FUNCTION get_amount(id integer) RETURNS numeric
AS $$
  SELECT amount FROM accounts a WHERE a.id = get_amount.id;
$$ VOLATILE LANGUAGE sql;
=> SELECT get_amount(id), pg_sleep(2)
FROM accounts WHERE client = 'bob';

延遲查詢執行期間再轉一次帳，得到的就是不一致的資料——$100 憑空消失：

 get_amount | pg_sleep
−−−−−−−−−−−−+−−−−−−−−−−
     100.00 |
     800.00 |
(2 rows)

這個效應只可能發生在 Read Committed 隔離層級、且函式為 VOLATILE 時。麻煩的是——PostgreSQL 的預設值正好就是這個隔離層級與這個 volatility 類別。這個陷阱設得非常狡猾。

讀取偏斜取代了更新遺失#

讀取偏斜也可能在單一運算子的更新過程中發生，而且方式相當出人意料。

Bob 兩個帳戶共有 $1,000（$200 + $800）。第一筆交易減少 Bob 的餘額；同時另一筆交易為總餘額達 $1,000 以上的客戶計算利息：

=> BEGIN;
=> UPDATE accounts SET amount = amount - 100.00 WHERE id = 3;

    => UPDATE accounts SET amount = amount * 1.01
    WHERE client IN (
      SELECT client
      FROM accounts
      GROUP BY client
      HAVING sum(amount) >= 1000
    );

UPDATE 的執行實質上分兩階段：

1. 依條件選出待更新的列。第一筆交易尚未提交，第二筆看不到其結果，因此選列不受影響——Bob 的帳戶符合條件，完成後餘額應增加 $10。
2. 逐一更新選出的列。第二筆交易必須等待，因為 id = 3 的列正被第一筆交易更新而遭鎖住。

第一筆交易提交後：

=> COMMIT;
=> SELECT * FROM accounts WHERE client = 'bob';
 id | client | amount
−−−−+−−−−−−−−+−−−−−−−−−−
  2 | bob    | 202.0000
  3 | bob    | 707.0000
(2 rows)

一方面，UPDATE 不該看到第一筆交易的變更；另一方面，它也不該遺失任何已提交的變更。鎖釋放後，UPDATE 運算子會重新讀取待更新的那一列（而且只有那一列）。結果 Bob 依 $900 的總額拿到 $9 利息——但若他本來就只有 $900，他的帳戶壓根就不該被納入查詢結果。

交易回傳了不正確的資料：不同的列讀自不同的快照。我們看到的不是更新遺失，而是再一次的讀取偏斜。

真正的更新遺失#

「重讀被鎖住的列」這個技巧，對於跨不同 SQL 運算子的資料修改就無能為力了。

應用程式讀取並（在資料庫外）登記 Alice 的目前餘額 $800，另一筆交易做同樣的事。接著第一筆把登記的值加 $100 並提交，第二筆也照做：

=> UPDATE accounts SET amount = 800.00 + 100 WHERE id = 1
RETURNING amount;   -- 900.00
=> COMMIT;

    => UPDATE accounts SET amount = 800.00 + 100 WHERE id = 1
    RETURNING amount;   -- 900.00
    => COMMIT;

Alice 損失了 $100。資料庫系統不知道那個被登記的 $800 與 accounts.amount 有任何關聯，因此無法防止更新遺失。在 Read Committed 隔離層級，這段程式碼是不正確的。

Repeatable Read#

沒有不可重複讀與幻讀#

如其名所示，Repeatable Read 層級必須保證可重複讀取；同時幻讀也不會發生。

第一筆交易把 Bob 的帳戶還原並為 Charlie 建立新帳戶；第二個 session 明確以 Repeatable Read 開啟交易：

    => BEGIN ISOLATION LEVEL REPEATABLE READ;
    => SELECT * FROM accounts ORDER BY id;
     id | client | amount
    −−−−+−−−−−−−−+−−−−−−−−−−
      1 | alice |    900.00
      2 | bob    | 202.0000
      3 | bob    | 707.0000
    (3 rows)

第一筆交易提交後，第二筆重複同一查詢，得到的仍是完全相同的資料：既看不到新列，也看不到列的更新。

在這個隔離層級，你不必擔心運算子之間會有東西改變。

序列化失敗取代了更新遺失#

前面看到，Read Committed 層級下兩筆交易更新同一列會導致讀取偏斜：等待中的交易必須重讀被鎖住的列，因而看到該列在不同時間點的狀態。

這種異常在 Repeatable Read 層級不被允許；一旦發生，交易只能以序列化失敗（serialization failure）中止。重跑利息計算的情境：

=> BEGIN;
=> UPDATE accounts SET amount = amount - 100.00 WHERE id = 3;

    => BEGIN ISOLATION LEVEL REPEATABLE READ;
    => UPDATE accounts SET amount = amount * 1.01
    WHERE client IN (
       SELECT client FROM accounts
       GROUP BY client HAVING sum(amount) >= 1000
    );

=> COMMIT;

    ERROR:   could not serialize access due to concurrent update
    => ROLLBACK;

資料保持一致。任何並行的列更新都會引發同樣的錯誤，即使它們影響的是不同欄位。若試圖依先前儲存的值更新餘額，同樣會得到這個錯誤。

寫入偏斜#

PostgreSQL 的 Repeatable Read 實作防止了標準所描述的所有異常，但並非所有可能的異常——沒人知道到底有多少種。不過有一項事實已被確切證明：無論還有多少其他異常，快照隔離未能防止的只有兩種。

第一種是寫入偏斜（write skew）。

定義一致性規則：允許客戶部分帳戶餘額為負，只要總餘額非負。

Bob 總餘額 $900。兩筆 Repeatable Read 交易都讀到 $900，都合理地認為可以從某個帳戶扣 $600——但它們扣的是不同帳戶：

=> UPDATE accounts SET amount = amount - 600.00 WHERE id = 2;

    => UPDATE accounts SET amount = amount - 600.00 WHERE id = 3;
    => COMMIT;

=> COMMIT;
=> SELECT * FROM accounts WHERE client = 'bob';
 id | client | amount
−−−−+−−−−−−−−+−−−−−−−−−
  2 | bob    | −400.00
  3 | bob    | 100.00
(2 rows)

Bob 的總餘額變成負數——儘管兩筆交易分別執行時都是正確的。

唯讀交易異常#

唯讀交易異常（read-only transaction anomaly）是 Repeatable Read 允許的第二個、也是最後一個異常。要觀察它需要三筆交易：兩筆更新資料，第三筆唯讀。

=> BEGIN ISOLATION LEVEL REPEATABLE READ; -- 1
=> UPDATE accounts SET amount = amount + (
  SELECT sum(amount) FROM accounts WHERE client = 'bob'
) * 0.01
WHERE id = 2;

    => BEGIN ISOLATION LEVEL REPEATABLE READ; -- 2
    => UPDATE accounts SET amount = amount - 100.00 WHERE id = 3;
    => COMMIT;

    => BEGIN ISOLATION LEVEL REPEATABLE READ; -- 3
    => SELECT * FROM accounts WHERE client = 'alice';
     id | client | amount
    −−−−+−−−−−−−−+−−−−−−−−−
      1 | alice | 1000.00
    (1 row)

=> COMMIT;

    => SELECT * FROM accounts WHERE client = 'bob';
     id | client | amount
    −−−−+−−−−−−−−+−−−−−−−−
      2 | bob    | 900.00
      3 | bob    |   0.00
    (2 rows)
    => COMMIT;

Serializable#

Serializable 層級防止所有可能的異常。它實質上建構在快照隔離之上：

• 在 Repeatable Read 不會發生的異常（髒讀、不可重複讀、幻讀），在 Serializable 同樣不會發生
• 那兩個仍會發生的異常（寫入偏斜與唯讀交易異常）會以特殊方式被偵測，並中止交易，造成我們已經熟悉的序列化失敗

重跑寫入偏斜情境，最終會以錯誤收場：

=> COMMIT;
ERROR: could not serialize access due to read/write dependencies
among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during
commit attempt.
HINT: The transaction might succeed if retried.

唯讀交易異常的情境也會導致同樣的錯誤。

延後唯讀交易#

為了避免唯讀交易造成破壞一致性的異常，PostgreSQL 提供一個有趣的解法：把該交易延後到執行變得安全為止。

這是唯一一種 SELECT 敘述會被列更新阻塞的情況。

只要把第三筆交易明確宣告為 READ ONLY DEFERRABLE：

    => BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; -- 3
    => SELECT * FROM accounts WHERE client = 'alice';

執行查詢的嘗試會阻塞該交易（否則就會造成異常）。只有當第一筆交易提交後，第三筆才能繼續執行，並讀到一致的結果。

代價與限制#

若應用程式使用 Serializable 層級，它必須準備好重試以序列化失敗結束的交易。（Repeatable Read 層級同樣需要這種做法，除非應用程式僅限唯讀交易。）

Serializable 帶來程式撰寫上的輕鬆，代價是異常偵測的開銷，以及一定比例的交易被強制終止。你可以在宣告唯讀交易時明確加上 READ ONLY 子句來降低影響。

但主要問題當然是：被中止的交易佔多大比例——因為這些交易都得重試。

目前的實作允許偽陽性：PostgreSQL 可能中止一些絕對安全、只是運氣不好的交易。它們的「運氣」取決於許多因素，例如是否有合適的索引、可用的記憶體多寡，因此實際行為很難事先預測。

若要用 Serializable，應用程式的所有交易都必須遵守它。與其他層級混用時，Serializable 會毫無提示地表現得像 Repeatable Read。因此若決定採用，最好相應修改 default_transaction_isolation 參數——儘管仍有人可能明確指定不同層級來覆寫它。

還有其他限制，例如 Serializable 層級的查詢無法在備援節點上執行。儘管這個層級的功能持續在改進，目前的限制與開銷仍使它較不吸引人。

該用哪個隔離層級？#

Read Committed 最棘手的地方在於：與資料不一致相關的錯誤極難測試。這類錯誤會以不可預測、幾乎無法重現的方式出現，因此也極難修復。