count(*) 是否曾經保證在任何事務級別的事務中返回相同的結果？

如果我創建一個表。

CREATE TABLE foo AS
SELECT CASE WHEN random() > 0.5 THEN x END AS x
FROM generate_series(1,10) AS x;

然後，我在事務中執行以下

BEGIN;
 SELECT count(*)
 FROM foo
 WHERE x IS NOT NULL;

 --time

 SELECT count(*)
 FROM foo
 WHERE x IS NOT NULL;
END;

在什麼交易級別下，我的交易結果可以保證在交易中保持不變？

我的十美分，並基於PostgreSQL 文件：

假設：您自己的事務不會更改表foo中的任何相關值。

在您的兩個查詢中具有相同count(*)的含義意味著：

1. 您不能擁有，因為另一個事務可能在第一個和第二個選擇之間將dirty reads更多行寫入您的*foo表中；*你不應該看到它們。
2. 您並沒有真正閱讀任何專欄，所以nonrepeatable reads這不是問題。
3. 您不能擁有phantom reads，也就是說，如果另一個事務將其x列為NULL非空值的任何行更改，您的事務不必注意到影響WHERE條件的那些更改。
4. 真不知道怎麼判斷serialization anomalies。我有根據的猜測是這不是必需的。但這真的很值得商榷。

在這些條件下，事務隔離級別表清楚地表明Repeatable read符合以下標準：

1. 沒有臟讀
2. 沒有不可重複讀
3. 沒有幻讀（在 PostgreSQL 中，但標準沒有要求）

…因此，它會count(*)在兩個 select 語句中為您提供相同的結果。

保證在“交易中”保持不變

這有兩個意思，這就是問題所在。您可以通過“快照”了解交易之外發生的事情以及內部發生的事情。的結果可能會發生兩件事count(*)：

• 計數可以更改
  事務級別READ COMMITTED（和READ UNCOMMITTED†）。
• 計數不能更改
  事務級別REPEATABLE READ和SERIALIZABLE。

改變和不改變並不是你需要知道的一切。REPEATABLE READ並SERIALIZABLE處理快照。也就是說，counts(*)不會改變，但這並不意味著數據庫中可能已經改變的內容。

讓我們簡化和回顧一些事情並呼叫上面的初始化程式碼REINIT。我們將只使用這兩個語句。

1. SELECT count(*) FROM foo WHERE x IS NULL;
2. UPDATE foo SET x = 1 WHERE x IS NULL;

現在，假設我們執行兩個會話

REINIT
1#     BEGIN; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
1#     SELECT count(*) FROM foo WHERE x IS NULL;
  2#  UPDATE foo SET x = 1 WHERE x IS NULL;
1#     SELECT count(*) FROM foo WHERE x IS NULL;

現在count(*)在1#’s 的交易中顯示什麼？在兩個事務都送出之後，#1然後#2？劇透警報：

在交易中，它會顯示相同的數字。在事務之外，它會顯示 0，因為UPDATE已經送出。

現在，在較低的事務級別中REPEATABLE READ，就像READ COMMITTED第二個一樣，SELECT通過#1查看已送出的行。並且，在更高的事務級別。第一個將僅在 where 的行上SELECT count(*)獲得謂詞鎖x IS NULL。那麼，當我們提升一個級別SERIALIZABLE並執行相同的序列時會發生什麼，現在使用謂詞鎖？

REINIT
1#      BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
1#      SELECT count(*) FROM foo WHERE x IS NULL;
  2#   UPDATE foo SET x = 1 WHERE x IS NULL;
1#      SELECT count(*) FROM foo WHERE x IS NULL;

劇透警報：

沒有。一樣。

為什麼？並發模型不關心UPDATEs ，除非某些東西也試圖修改這些行並且都打算送出。兩者都在修改它們的快照SELECT而不是修改它們的快照。所以通過擴展，

if (SELECT count(*) 
     FROM foo
    WHERE x IS NULL
  ) < arg THEN
   RETURN 0 ;
end if ;

• 然後在預設事務級別中，READ COMMITTED它可能會看到一個與事務的其餘部分不同的數字——一個並發事務可能已經在第二個之間送出SELECT。這使得它沒有用REPEATABLE READor SERIALIZABLE。
• 但是在其他事務模式中，您可能會面臨鎖定問題，或者如果其他任何事情從事務外部觸及表，則REPEATABLE READ在送出時會失敗。SERIALIZABLE再次，有一個有限的點。如果count(*)查詢返回行，然後，例如，其他程式碼可能會嘗試更新這些行，只是為了發現它們已經被修改或者當工作快照送出時它們不再存在。

所以不要有條件地在交易中做事。做事，然後處理。

---

† SQL 標準提供了一個級別READ UNCOMMITTED。在 PostgreSQL中，該級別將其別名為更嚴格的隔離級別READ COMMITTED。

引用自：https://dba.stackexchange.com/questions/160231