PostgreSQL 的 MVCC：同一筆資料為什麼可以同時有好幾個版本

寫給耀達 Lite 雲端進銷存（2026-11-03）。補充 postgres-design-sql.html 第 2 節的「脾氣一」。
前提：AWS 東京、RDS PostgreSQL，所有店家共用資料表、用 RLS 隔開。PostgreSQL 版本還沒定，內容以 PostgreSQL 18 文件為準。資料表名稱都是示範，資料模型還沒定。

1. 先講結論

• 它保證的：一般的 SELECT 和寫入不會因為列鎖互相等待，也讀不到別人還沒提交的資料。
• 它沒有保證的：同時改同一列仍然要排隊；「先讀再判斷、然後寫」的邏輯，MVCC 不會替你防止超賣（第 7、8 節）。
• 不互相擋不等於不互相影響：大報表不會因為鎖卡住開單，但一樣會吃 CPU 和磁碟。這也是報表可能要放到讀取副本的原因。
• 它的代價：舊版本要靠 VACUUM 清理；開太久的交易會讓舊版本清不掉，資料表因此膨脹（第 9、10 節）。
• 不用另外開：這是 PostgreSQL 的基本運作方式，沒有要設定的開關。要做決定的是怎麼寫交易和查詢。

2. 沒有 MVCC 會怎樣

假設資料庫裡每筆資料只有一份。店員正在改商品 A 的庫存，還沒提交，老闆這時要看庫存。只有一份資料的話，資料庫只能選下面其中一種做法：

PostgreSQL 文件的說法是：查詢用的鎖和寫入用的鎖不衝突，所以「讀不會擋寫，寫也不會擋讀」。即使在最嚴格的 Serializable 隔離層級也維持這個保證。

3. 一筆資料怎麼變成好幾個版本

你在 SQL 裡看到的「一列」，在磁碟上可能是好幾個列版本（row version）。每個版本都帶著兩個交易編號，寫在每列的表頭裡：

三種寫入指令實際做的事：

4. 怎麼決定你看到哪一版：快照

快照（snapshot）不是把資料複製一份，只是一張很小的清單，記下「拍的當下，哪些交易已經結束、哪些還在進行」。文字格式是 xmin:xmax:進行中的編號。

例如快照 205:207:205，代表拍的當下 205 還在進行、206 已經結束。判斷方式是：

有了這張清單，檢查每個列版本就只要問兩件事（簡化版）：

1. 建立它的交易，對我的快照來說算數嗎？（看 xmin）不算數就是還不存在。
2. 刪除它的交易，對我的快照來說算數嗎？（看 xmax）算數就是已經不存在了。

兩關都過，這個版本才看得到。自己交易改的資料例外，自己看得到（實際上還會比對同一交易裡的指令順序，cmin、cmax 就是做這個的）。

文件也寫明：xmax 不是 0 的版本仍然可能看得到，通常表示刪除它的交易還沒提交，或已經復原。

5. 完整走一遍：店員改庫存，老闆看報表

商品 A 的庫存原本是 10，用預設的 Read Committed。交易編號是示意。

如果店員取消呢

第 2 步改成 ROLLBACK：205 的狀態變成已復原。版本 1 的 xmax 雖然還寫著 205，但 205 不算數，所以版本 1 繼續有效，所有人都看到 10。版本 2 的 xmin 不算數，沒有人看得到。

所以取消交易也會留下垃圾：版本 2 一樣佔著空間，要等 VACUUM。這一點來自 Claude 的知識，這次沒有逐字對過文件，但可以從上面的可見性規則推出來。未實測

想親眼看的話

開兩個 psql 視窗，用測試資料庫跑。xmin、xmax、ctid 是每張表都有的系統欄位，要明確寫出來才查得到。未實測

-- 準備
CREATE TABLE demo_stock (id int PRIMARY KEY, qty int);
INSERT INTO demo_stock VALUES (1, 10);

-- 視窗一：店員
BEGIN;
UPDATE demo_stock SET qty = 7 WHERE id = 1;
SELECT pg_current_xact_id(), ctid, xmin, xmax, qty FROM demo_stock;   -- 看到 7

-- 視窗二：老闆（不會卡住）
SELECT ctid, xmin, xmax, qty FROM demo_stock;   -- 看到 10，xmax 是視窗一的交易編號
SELECT pg_current_snapshot();                   -- 看目前的快照

-- 視窗一
ROLLBACK;

-- 視窗二
SELECT ctid, xmin, xmax, qty FROM demo_stock;   -- 還是 10，xmax 仍然不是 0

6. 快照什麼時候拍：隔離層級

隔離層級最主要的差別，就是快照拍的時機，以及遇到衝突時怎麼處理。PostgreSQL 的預設是 Read Committed。

報表最容易碰到的差異

老闆的報表先查「今天的單數」，再查「今天的金額合計」，店員剛好在兩句中間提交了一張單：

同一句 SQL 只會用同一張快照，所以能用一句查完的報表，在 Read Committed 下也是一致的。只有跨好幾句、又要求彼此對得起來時，才需要 Repeatable Read。唯讀交易不會發生更新衝突，不用為了這個重試。

Serializable 擋的是哪一種問題

下面這個例子的規則只是示範，Lite 還沒有信用額度的設計。假設規則是「客戶未收款合計不能超過額度」，兩個店員同時替同一位客戶開單：

1. 兩筆交易各自讀未收款單據，合計都沒超過，都判斷可以開。
2. 各自 INSERT 一張新單。兩筆交易寫的是不同的列，沒有衝突，也不用排隊。
3. 都提交成功，合計超過額度。

這在 Repeatable Read 下也攔不住，因為每筆交易都看不到對方新增的那張單。Serializable 能偵測出來，讓其中一筆失敗。另一種做法是在 Read Committed 下先 SELECT ... FOR UPDATE 鎖住這位客戶的那一列，讓兩筆交易排隊。

Claude 的看法：跟 postgres-design-sql.html 第 9.3 節一致，第一版用預設的 Read Committed，不要全站開 Serializable；需要時才在特定地方用鎖，或用 Repeatable Read 跑多句的報表。

7. MVCC 沒有替你擋的事

順帶一提，這也有好處：UPDATE 沒有改到外鍵參照的唯一鍵時，取的是較弱的 FOR NO KEY UPDATE，跟外鍵檢查用的 FOR KEY SHARE 不衝突。所以改商品名稱，不會擋到別人開一張參照這個商品的單。

8. 庫存：進銷存最要緊的一題

MVCC 的「各看各的」用在庫存上會出事：兩個人都看到庫存 10，都覺得沒問題。

實測對照（postgres-design-sql.html 第 9.1 節）：8 條連線各扣 1,000 次，應該少 8,000。先讀再寫只少 995，一句 UPDATE 少了剛好 8,000；庫存 100、共搶 800 次時，WHERE qty >= $1 最後剛好是 0。已實測

負庫存要不要擋還沒決定。無論答案是什麼，扣的方式都不該是先讀再寫。

Claude 的看法：一句 UPDATE 的做法要有一列「庫存餘額」可以更新。如果之後的資料模型改成每次從異動明細加總算庫存，就沒有那一列可以排隊，會變成第 6 節信用額度那種情況。這點要留到談資料模型時一起看。

9. 代價一：死資料列與 VACUUM

舊版本不會自己消失。一個版本的生命週期是這樣：

9.1 VACUUM 做什麼

• 回收空間：把沒有人需要的舊版本標成可以重複使用。
• 更新 visibility map：記下哪些資料頁對所有交易都看得到。Index-only scan 靠它才能少讀資料表。
• 凍結舊資料：防止交易編號回捲（第 11 節）。
• 更新統計資料：ANALYZE 給查詢規劃器用的統計。

平常由 autovacuum 自動跑。一般的 VACUUM 可以跟正常讀寫同時進行。

9.2 一般 VACUUM 跟 VACUUM FULL

9.3 HOT 更新：少寫一點索引

每個新版本在資料表裡的位置不同，所以一般的 UPDATE 連索引也要多寫。同時符合兩個條件時，PostgreSQL 可以做 HOT 更新（Heap-Only Tuple）：

1. 改的欄位沒有出現在任何索引裡（BRIN 這類摘要型索引除外）。
2. 舊版本所在的資料頁還有空位放新版本。

HOT 更新不用新增索引項目，中間的舊版本在一般操作中（包括 SELECT）就可能被順手清掉，不必等 VACUUM。所以常被改的欄位（例如庫存量）不要隨便建索引。圖和相關數字見 postgres-design-sql.html 第 2 節。

9.4 膨脹跟帳單的關係

膨脹是指死資料列和空位佔掉太多空間。對 RDS 來說，這不只是查詢變慢：

• RDS 儲存按配置量計費，只能加不能減（rds.html，gp3 單機每 GB 每月 0.138 美元）。
• 膨脹觸發自動擴充後，就算之後把空間清回來，配置量和帳單也不會降回去。自動擴充上限設 100 GiB，最多約每月 13.80 美元。
• 因為所有店家共用資料表，一家大店大量修改造成的膨脹，別家也會一起受影響。

autovacuum 的門檻和調整方式，見 postgres-design-sql.html 第 11.1 節。

10. 代價二：長交易讓舊版本清不掉

實測（postgres-design-sql.html 第 11.2 節）：開著一個 Repeatable Read 交易時更新 1 萬列，VACUUM 回報 0 removed, 10000 are dead but not yet removable；交易結束後再跑一次，1 萬列都回收了。已實測

會讓舊版本清不掉的東西

怎麼防

先找出開最久、還佔著快照的連線：未實測

SELECT pid, state, now() - xact_start AS 開了多久, backend_xmin, left(query, 80) AS 最後的查詢
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL OR backend_xid IS NOT NULL
ORDER BY xact_start;

下面這些逾時預設都是 0（不限制）。設定方式和起始值見 postgres-design-sql.html 第 9.6 節：

11. 代價三：交易編號會用完

MVCC 靠交易編號判斷先後，而編號只有 32 位元，用完會繞回去。比較編號時，每個編號之後約 20 億個算「未來」、之前約 20 億個算「過去」。如果一列存活超過約 20 億筆交易，它會突然變成「未來才出現」，看起來就像資料消失了。

解法是凍結（freeze）：VACUUM 把夠舊的版本標成「對所有交易來說都是過去」，它就不再受編號影響。

長交易也會讓這件事更危險，因為凍結一樣要等舊快照結束。文件的處理步驟是先處理舊的 prepared transaction、結束舊交易、清掉過期的複寫槽，再跑一般的 VACUUM，不要跑 VACUUM FULL。

另外，交易要到真正需要編號時才會配發：pg_current_xact_id() 的文件寫，還沒改過資料的交易可能還沒有編號。所以消耗編號的主要是寫入。

對應的 CloudWatch 指標是 MaximumUsedTransactionIDs，門檻留到第 8 題（postgres-design-sql.html 第 11.5 節）。

12. 讀取副本上的 MVCC

rds.html 把讀取副本放在「報表拖慢開單時才加」。到那時會碰到一個 MVCC 帶來的問題：

1. 老闆在副本上跑一張要 5 分鐘的報表，需要舊版本。
2. 主機不知道副本上有人在讀，VACUUM 把舊版本清掉，這個清理動作透過 WAL 傳到副本。
3. 副本要套用這筆清理，就會跟那張報表衝突。
4. 副本等 max_standby_streaming_delay（PostgreSQL 預設 30 秒）後，取消那張報表，回傳類似 canceling statement due to conflict with recovery 的錯誤。

還有兩點：副本上最高只支援 Repeatable Read，不支援 Serializable；而且 db-scaling.html 第 8 節講「報表交給副本」時沒提到這個取消問題。這三個參數在 RDS 上的預設值和能不能改，這次沒有查（第 16 節）。

13. 跟其他資料庫比

SQL Server 兩列來自 Microsoft 文件；MySQL、MariaDB、Oracle 這一列來自 Claude 的知識，這次沒有查文件。

PostgreSQL 把舊版本放在資料表本身，所以復原和讀舊版本都不用另外去 undo 找，但膨脹和 VACUUM 的問題也就留在資料表裡。這是取捨，不是誰比較好。

14. C# 程式裡怎麼寫

一般的單據交易用預設層級，直接開交易就好。下面是需要 Repeatable Read 的唯讀報表，dataSource 是 NpgsqlDataSource。未實測

await using var conn = await dataSource.OpenConnectionAsync();
await using var tx = await conn.BeginTransactionAsync(IsolationLevel.RepeatableRead);

await using (var cmd = new NpgsqlCommand("SET TRANSACTION READ ONLY", conn, tx))
    await cmd.ExecuteNonQueryAsync();

await using (var cmd = new NpgsqlCommand("SELECT set_config('app.store_id', $1, true)", conn, tx))
{
    cmd.Parameters.AddWithValue(storeId.ToString());
    await cmd.ExecuteNonQueryAsync();
}

// 這裡開始的每一句都看同一張快照
var count = await QueryOrderCountAsync(conn, tx, date);
var total = await QueryOrderTotalAsync(conn, tx, date);

await tx.CommitAsync();

• 快照從哪一句開始：文件寫的是第一句非交易控制指令。依這個說法，上面的快照會在 set_config 那一句拍，這是推論。未實測
• Repeatable Read 的寫入交易，以及所有 Serializable 交易（唯讀的也可能失敗，除非用 SERIALIZABLE READ ONLY DEFERRABLE）要準備重試：收到 40001 時重來整筆交易，包含 set_config。可以用 PostgresException.IsTransient 判斷（postgres-design-sql.html 第 9.3 節）。
• 交易不要開在等待外部服務的時候：電子發票、金流、寄信都放到交易外（同文件第 9.4 節）。

15. 對耀達 Lite 的意義

Claude 的看法，都還沒拍板：

會碰到的還沒定的題目：

• 負庫存要不要擋：決定 UPDATE 要不要加 qty >= $1，沒有改變「不要先讀再寫」。
• 資料模型：庫存有沒有餘額列，會決定第 8 節哪些寫法能用。
• 第 8 題告警：死資料列數量、最長交易時間、MaximumUsedTransactionIDs。
• RDS Proxy（第 7 題）：跟 MVCC 本身無關，但交易開得短，連線也比較容易重複使用。

16. 還沒查證的事

• 這份文件所有 SQL 和 C# 這次都沒跑過。第 5 節和第 10 節的語法看文件應該沒問題，但沒有實際輸出。
• RDS 上的副本參數：hot_standby_feedback、max_standby_streaming_delay 的預設值和能否修改，這次沒查。本文寫的是社群版 PostgreSQL 的預設。Aurora 的行為更不確定，postgres-design-sql.html 第 17 節也列著。
• RDS 主帳號看 pg_stat_activity 的範圍：能不能看到其他帳號的 query，這次沒查。
• 取消交易會留下死資料列：來自 Claude 的知識，加上可見性規則的推論，沒有找到文件裡直接這樣寫的句子。
• MySQL、MariaDB、Oracle 的 undo 做法：來自 Claude 的知識，只用於比較。
• Aurora：儲存架構不同。本文的 MVCC 規則是 PostgreSQL 引擎層的行為，但「空間怎麼算錢」不能照第 9.4 節套用（aurora-serverless.html 第 7 節）。

17. 名詞對照

18. 來源

PostgreSQL 官方文件，2026-11-03 查閱，版本 18：

• Concurrency Control: Introduction（定義、讀寫不互擋）
• Transaction Isolation（隔離層級、快照時機、重新判斷、40001、重試）
• Explicit Locking（資料表鎖、列鎖、死結）
• Caveats（TRUNCATE、重寫資料表的 ALTER TABLE、副本不支援 Serializable）
• System Columns（xmin、xmax、ctid）
• Database Page Layout（資料頁大小、列表頭）
• System Information Functions（快照格式、pg_current_xact_id）
• Routine Vacuuming（清理、VACUUM FULL、visibility map、回捲與凍結）
• Heap-Only Tuples（HOT 更新）
• Index Uniqueness Checks（唯一鍵衝突時等待）
• Client Connection Defaults（各種逾時、預設隔離層級）
• Hot Standby、Replication 設定（副本查詢衝突、預設值）

Microsoft 文件：

• Transaction locking and row versioning guide（READ_COMMITTED_SNAPSHOT 的預設值與行為）

同目錄文件：postgres-design-sql.html（第 2、9、11、12 節與實測數字）、rds.html（儲存費用、讀取副本）、db-scaling.html。