PostgreSQL — MVCC & Isolation Levels

1. Snapshot Isolation

PostgreSQL không đọc trực tiếp từ table mà đọc từ snapshot — một "ảnh chụp" trạng thái data tại thời điểm nhất định.

Quan trọng: Snapshot không phải bản copy vật lý của table. PostgreSQL chỉ ghi nhớ "XID nào đã commit tại thời điểm này" rồi dùng xmin/xmax để lọc row phù hợp → rất nhẹ, không tốn storage.

Thời điểm snapshot được chụp phụ thuộc vào isolation level:

• READ COMMITTED → chụp mới mỗi câu SELECT
• REPEATABLE READ / SERIALIZABLE → chụp 1 lần lúc BEGIN, dùng xuyên suốt transaction

---

2. xmin và xmax

Mỗi row trong PostgreSQL có 2 hidden column:

Column	Ý nghĩa
xmin	Transaction ID đã tạo row này
xmax	Transaction ID đã xóa/update row này. Mặc định = 0 (row còn sống)

PostgreSQL không sửa row cũ — chỉ tạo row mới

UPDATE:

row cũ: xmin=100, xmax=200, tier='silver'   ← xmax được set = xid của UPDATE
row mới: xmin=200, xmax=0,  tier='gold'     ← row mới được tạo

DELETE:

row cũ: xmin=100, xmax=200, tier='silver'   ← chỉ set xmax, KHÔNG tạo row mới

xmax và rollback

xmax được set ngay khi UPDATE/DELETE thực thi, kể cả chưa commit. Nếu sau đó rollback → xmax vẫn còn nhưng transaction chưa commit → các transaction khác check thấy "chưa commit" → coi row vẫn sống. Không cần xóa xmax đi.

Cách PostgreSQL quyết định row có visible không

xmax = 0                          → row còn sống → đọc được
xmax != 0, chưa commit            → coi như row vẫn sống
xmax != 0, đã commit trước BEGIN  → row đã chết → không đọc
xmax != 0, đã commit sau BEGIN    → tùy isolation level

Dead rows và VACUUM

Các row có xmax != 0 và không còn transaction nào cần đọc → dead rows. VACUUM dọn dẹp định kỳ, đánh dấu lại thành free space để tái sử dụng (không xóa khỏi disk ngay).

Nếu VACUUM không chạy kịp → dead rows tích tụ → table bloat.

---

3. Transaction ID (XID) và Wraparound

XID là gì

Mỗi transaction được cấp 1 số định danh tăng dần — gọi là XID. Chính là giá trị trong xmin/xmax.

SELECT txid_current();  -- xem XID của transaction hiện tại

Vấn đề Wraparound

XID là số 32-bit → max ≈ 4 tỷ. Khi đếm đến 4 tỷ, quay về 0.

PostgreSQL so sánh XID theo vòng tròn (modular arithmetic), không phải tuyến tính:

XID hiện tại = 50 (sau wraparound)
Row cũ có xmin = 100

→ 100 nằm ở phía "tương lai" của 50
→ PostgreSQL nghĩ row này chưa được tạo
→ không cho đọc → DATA MẤT

Giải pháp: FREEZE

VACUUM định kỳ freeze các row cũ:

xmin = 100  →  đổi thành FrozenXID (= 2)

FrozenXID là giá trị đặc biệt có nghĩa: "row này luôn visible với mọi transaction, không cần so sánh XID".

Kiểm tra nguy cơ wraparound

SELECT datname, age(datfrozenxid) FROM pg_database;
-- age > 1,500,000,000 → cần chạy VACUUM gấp
-- age > 2,000,000,000 → PostgreSQL tự chuyển read-only để bảo vệ data

Không bao giờ tắt autovacuum trên production — autovacuum có 2 nhiệm vụ chính:

1. Dọn dead rows
2. Freeze row cũ trước khi XID kịp wraparound

---

4. Isolation Levels

READ COMMITTED (default)

Mỗi câu SELECT chụp snapshot mới nhất tại thời điểm câu đó chạy.

BEGIN
  SELECT tier  → snapshot tại T1 → 'silver'
  -- transaction khác UPDATE + COMMIT
  SELECT tier  → snapshot tại T2 → 'gold'   ← khác lần trước!
COMMIT

Non-repeatable read — cùng 1 transaction đọc 2 lần ra 2 giá trị khác nhau.

UPDATE/DELETE: sau khi chờ row lock release, re-fetch row mới nhất rồi ghi đè → có thể lost update âm thầm nếu không có guard.

REPEATABLE READ

Snapshot chụp 1 lần lúc BEGIN, dùng xuyên suốt transaction.

BEGIN ISOLATION LEVEL REPEATABLE READ
  SELECT tier  → snapshot tại BEGIN → 'silver'
  -- transaction khác UPDATE + COMMIT
  SELECT tier  → vẫn dùng snapshot cũ → 'silver'  ← không đổi
COMMIT

UPDATE/DELETE conflict: nếu transaction khác đã UPDATE + COMMIT cùng row → ERROR 40001, buộc app retry.

SERIALIZABLE

Cao nhất. Đảm bảo kết quả y hệt như chạy tuần tự từng transaction một.

Thêm predicate locking — track "transaction A đã đọc tập data nào":

Session A: SELECT COUNT(*) WHERE tier = 'gold'
           → predicate lock: "A đọc WHERE tier='gold'"

Session B: UPDATE tier = 'gold' WHERE id = 2 → COMMIT
           → row này thỏa predicate lock của A → đánh dấu conflict

Session A: COMMIT → ERROR 40001
           → dù A và B update 2 row khác nhau!

Không phải lúc nào cũng lỗi — chỉ lỗi khi có dependency (đọc rồi ghi dựa trên data đã đọc). Nếu 2 transaction độc lập nhau → cho qua bình thường.

So sánh

	READ COMMITTED	REPEATABLE READ	SERIALIZABLE
Snapshot	mỗi SELECT	lúc BEGIN	lúc BEGIN
Row lock	✅	✅	✅
Predicate lock	❌	❌	✅
Non-repeatable read	❌ xảy ra	✅ ngăn được	✅ ngăn được
Lost update	❌ có thể xảy ra	✅ báo lỗi 40001	✅ báo lỗi 40001
Write skew	❌	❌	✅ ngăn được
Performance	cao nhất	trung bình	thấp nhất

---

5. Thực tế chọn isolation level

Bài toán	Nên dùng	Lý do
Kho hàng, balance đơn giản	READ COMMITTED + guard trong WHERE	Row lock + re-fetch tự xử lý
Report, thống kê	REPEATABLE READ	Không muốn data thay đổi giữa chừng
Logic nhiều bước phụ thuộc nhau	REPEATABLE READ + retry	Đảm bảo consistency
Tuyệt đối không được sai	SERIALIZABLE + retry	Ngăn mọi anomaly

Pattern an toàn cho kho hàng

-- 1 câu SQL duy nhất = implicit transaction = atomic
UPDATE products
SET stock = stock - 1
WHERE id = 1 AND stock >= 1;

-- Check rows affected trong app
-- 0 rows → out of stock

Không cần REPEATABLE READ, không cần pessimistic lock — row lock mặc định của PostgreSQL đã đủ.

---

6. Lỗi hay gặp

Error code	Nghĩa	Xử lý
40001	Serialization failure — conflict với transaction khác	ROLLBACK + retry
55P03	Lock timeout — chờ row lock quá lâu	ROLLBACK + retry hoặc tăng timeout

-- Set timeout để tránh treo vô thời hạn
SET lock_timeout = '3s';