Isolation levels và 4 anomalies — dirty read, non-repeatable read, phantom, write skew

Mở 2 terminal, kết nối vào cùng một database PostgreSQL, rồi cùng chạy lệnh sau trên cả hai session:

-- Session A va B cung chay dong thoi, khong BEGIN, khong LOCK
UPDATE accounts SET balance = balance - 10 WHERE id = 1;

Không có lỗi. Không có cảnh báo. Cả hai session commit thành công. Nhưng khi kiểm tra kết quả, số dư chỉ giảm 10, không phải 20. Một trong hai lần trừ tiền bị mất — đây là lost update, một trong 4 anomaly mà hệ quản trị cơ sở dữ liệu phải đối mặt khi nhiều transaction chạy đồng thời.

Vì sao xảy ra? Vì cả hai session đọc balance = 100, tính 100 - 10 = 90, rồi cùng ghi 90 vào row — không phải 80. Đây là bài toán race condition kinh điển. Và mỗi database, tùy vào isolation level được chọn, sẽ cho phép hoặc cấm các anomaly khác nhau. Hiểu đúng từng level và từng anomaly là điều kiện bắt buộc để viết code transaction an toàn trong môi trường production.

Bài này mổ xẻ 4 isolation level theo SQL standard, 4 anomaly được định nghĩa trong paper Berenson 1995, hành vi cụ thể của PostgreSQL (khác với SQL Server và Oracle ở một số điểm quan trọng), demo 2-session cho từng anomaly, và áp dụng vào TaskFlow với bài toán assign task race condition.

1. Analogy — Google Doc phòng họp nhiều tác giả

Hãy tưởng tượng một Google Doc dùng chung trong phòng họp, nhiều người cùng chỉnh sửa một lúc:

2. Tại sao cần isolation — lost update và dirty money

Concurrency không tự nhiên đúng. Khi nhiều transaction cùng đọc và ghi data, kết quả có thể sai theo nhiều cách không rõ ràng. Demo kinh điển nhất là read-modify-write pattern:

-- Schema accounts (tu M06.1, re-declare cho self-contained)
CREATE TABLE accounts (
  id      BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  balance NUMERIC(15,2) NOT NULL CHECK (balance >= 0)
);

-- Seed du lieu
INSERT INTO accounts(user_id, balance) VALUES (1, 100.00), (2, 500.00);

Sau đây là timeline của lost update khi 2 session chạy đồng thời ở Read Committed:

-- Session A                              -- Session B
BEGIN;
SELECT balance
  FROM accounts WHERE id = 1;
-- => 100

                                          BEGIN;
                                          SELECT balance
                                            FROM accounts WHERE id = 1;
                                          -- => 100 (cung snapshot)

UPDATE accounts
  SET balance = 90  -- tru 10
  WHERE id = 1;
COMMIT;
-- balance = 90

                                          UPDATE accounts
                                            SET balance = 80  -- B nghi balance=100, tru 20
                                            WHERE id = 1;
                                          COMMIT;
                                          -- balance = 80 (ghi de ket qua cua A)

-- Ket qua cuoi: 80 thay vi 80 dung, nhung mat logic:
-- A tru 10 (100->90), B tru 20 (100->80) -- tong tru 30 nhung balance chi giam 20
-- Lost update: 10 bien mat

Đây là lý do tại sao dùng pattern balance = balance - 10 (atomic expression) thay vì đọc ra app rồi ghi lại. Nhưng ngay cả vậy, vẫn tồn tại những anomaly phức tạp hơn mà chỉ level cao hơn mới ngăn được.

3. Bốn anomaly — Berenson 1995

Paper "A Critique of ANSI SQL Isolation Levels" (Berenson et al., 1995) định nghĩa chặt chẽ các anomaly mà SQL standard muốn ngăn. Đây là bộ từ vựng chuẩn của ngành:

Write skew tinh tế nhất: T1 và T2 không ghi vào cùng row (nên không có write conflict), nhưng kết quả hợp lại vi phạm một invariant của business. Chỉ Serializable mới ngăn được.

4. Bốn isolation level và ma trận anomaly

SQL standard định nghĩa 4 level. PostgreSQL implement theo cách riêng — ở một số điểm mạnh hơn standard yêu cầu:

Hai điểm đặc biệt của PostgreSQL:

Read Uncommitted = Read Committed trong PG: PG không có cơ chế để đọc dirty data — kiến trúc MVCC của PG đảm bảo mọi read đều thấy committed version. Nếu set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, PG xử lý y hệt Read Committed.

Repeatable Read trong PG là Snapshot Isolation: SQL standard cho phép phantom read ở Repeatable Read. Nhưng PG implement RR bằng Snapshot Isolation — snapshot chụp lúc transaction bắt đầu, nên phantom read không xảy ra. PG RR mạnh hơn ANSI RR.

-- Dat isolation level cho transaction hien tai
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;   -- mac dinh
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Hoac dung SET TRANSACTION sau BEGIN
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- ... statements ...
COMMIT;

-- Hoac dung default cua session
SET default_transaction_isolation = 'repeatable read';

5. Snapshot Isolation — PG Repeatable Read hoạt động thế nào

Hiểu Snapshot Isolation (SI) là hiểu tại sao PG RR mạnh hơn ANSI RR và tại sao nó vẫn không ngăn được write skew.

Snapshot là gì? Mỗi transaction trong PG được gán một txid (transaction ID). Khi transaction bắt đầu, PG ghi lại:

• txid hiện tại của transaction này.
• Danh sách tất cả txid đang active (chưa commit, chưa rollback) tại thời điểm đó.

Đây là snapshot. Mọi read trong transaction sẽ chỉ thấy data được ghi bởi các transaction có txid nhỏ hơn snapshot và đã committed — không thấy data của transaction nào trong danh sách active, không thấy data mới được commit sau khi snapshot được chụp.

txid timeline:
  100 (committed)  101 (active khi T103 bat dau)  102 (committed)  103 (T hien tai)

T103 snapshot: thay data tu txid <= 102, trong do txid=101 dang active -> khong thay
=> T103 thay: txid 100 + txid 102 (da commit, < 103, khong trong active list)
=> T103 KHONG thay: txid 101 (con active), txid 104+ (chua ton tai)

Read Committed vs Repeatable Read — khác biệt snapshot:

• Read Committed: snapshot chụp lại mỗi statement. Hai câu SELECT liên tiếp trong cùng transaction có thể thấy data khác nhau nếu có commit xảy ra giữa chúng.
• Repeatable Read: snapshot chụp một lần lúc statement đầu tiên của transaction. Mọi statement tiếp theo dùng cùng snapshot → cùng một view của data.

-- Demo: RC lay snapshot moi moi statement
-- Session A (READ COMMITTED -- mac dinh):
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- => 100 (snapshot tai thoi diem nay)

-- [Session B: UPDATE accounts SET balance = 200 WHERE id = 1; COMMIT;]

SELECT balance FROM accounts WHERE id = 1;
-- => 200 (snapshot moi, thay commit cua B)
COMMIT;

-- Demo: RR giu snapshot tu dau den cuoi transaction
-- Session A (REPEATABLE READ):
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- => 100 (snapshot chup luc nay)

-- [Session B: UPDATE accounts SET balance = 200 WHERE id = 1; COMMIT;]

SELECT balance FROM accounts WHERE id = 1;
-- => 100 (van la snapshot cu -- khong thay thay doi cua B)
COMMIT;

Write conflict ở Repeatable Read: Nếu T1 (RR) muốn UPDATE một row mà T2 đã UPDATE và committed sau khi T1 chụp snapshot, PG sẽ abort T1 với ERROR: could not serialize access due to concurrent update (SQLSTATE 40001). Application phải retry transaction.

6. Serializable SSI — Cahill 2008

Serializable Snapshot Isolation (SSI) là cơ chế PG dùng cho isolation level Serializable — không phải lock-based serialization như SQL Server hay Oracle, mà là snapshot + detection conflict.

Vấn đề của Snapshot Isolation: SI ngăn được nhiều anomaly nhưng không ngăn write skew. Lý do: 2 transaction có thể cùng đọc tập data không overlap, cùng ghi vào 2 row khác nhau — không có write conflict — nhưng hợp lại thì phá invariant.

SSI giải quyết thế nào? Thêm một lớp tracking trên SI:

1. SIREAD locks (predicate locks): Mỗi khi transaction đọc data (kể cả đọc theo điều kiện WHERE — predicate), PG ghi lại "transaction này đã đọc tập data X". Đây không phải lock thực — không block ai. Chỉ là dấu theo dõi.

2. Read-write dependency tracking: PG xây dựng đồ thị phụ thuộc giữa các transaction: T1 đọc data mà T2 sau đó ghi (rw-dependency T1→T2), T2 đọc data mà T3 sau đó ghi (rw-dependency T2→T3).

3. Cycle detection: Nếu đồ thị phụ thuộc có chu trình (ví dụ T1→T2→T1), PG biết rằng không thể sắp xếp các transaction này theo thứ tự serial nào đó mà kết quả tương đương. Một transaction bị abort với SQLSTATE 40001.

Write skew detection qua SSI:
T1: READ tasks WHERE assignee IS NULL  -- SIREAD lock tren tap nay
T2: READ tasks WHERE assignee IS NULL  -- SIREAD lock tren tap nay (overlap)
T1: WRITE tasks SET assignee=userA WHERE id=42  -- rw-dep T2->T1
T2: WRITE tasks SET assignee=userB WHERE id=42  -- write conflict! T2 abort

Hoac write skew khong cung row:
T1: READ oncall WHERE status='on'  -- SIREAD lock
T2: READ oncall WHERE status='on'  -- SIREAD lock (overlap)
T1: INSERT oncall (doctor_id=1, status='off')  -- rw-dep T2->T1
T2: INSERT oncall (doctor_id=2, status='off')  -- rw-dep T1->T2
-- Cycle: T1->T2->T1 -> PG abort 1 tx voi SQLSTATE 40001

Lưu ý quan trọng về false positive: SSI đôi khi abort transaction dù không có anomaly thực sự (false positive). Khi retry, transaction sẽ thành công. App phải implement retry logic cho SQLSTATE 40001.

-- Pattern retry cho Serializable
-- App code (pseudo):
-- for attempt := 1 to MAX_RETRY:
--   BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE
--   ... business logic ...
--   COMMIT
--   if success: break
--   if SQLSTATE 40001: continue (retry)
--   else: raise error

7. Demo từng anomaly với 2 psql session

7a. Dirty read — PG không bao giờ cho phép

-- Session A:
BEGIN;
UPDATE accounts SET balance = 9999 WHERE id = 1;
-- CHUA COMMIT

-- Session B (du o READ UNCOMMITTED, PG van khong thay):
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT balance FROM accounts WHERE id = 1;
-- => 100 (khong thay 9999 chua commit cua A)
COMMIT;

-- Session A:
ROLLBACK;
-- 9999 bien mat, khong ai doc duoc

PG không có dirty read ở bất kỳ isolation level nào — MVCC ngăn điều này ở tầng kiến trúc.

7b. Non-repeatable read — demo ở Read Committed

-- Session A (READ COMMITTED -- mac dinh):
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- => 100

-- [Dung o day, sang Session B]

-- Session B:
BEGIN;
UPDATE accounts SET balance = 200 WHERE id = 1;
COMMIT;
-- Session B ket thuc

-- Session A tiep tuc (van con trong tx):
SELECT balance FROM accounts WHERE id = 1;
-- => 200 (KHAC lan dau -- non-repeatable read xay ra)
COMMIT;

-- Fix: dung Repeatable Read
-- Session A (REPEATABLE READ):
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- => 100
-- [Session B commit UPDATE balance=200]
SELECT balance FROM accounts WHERE id = 1;
-- => 100 (van la snapshot cu -- non-repeatable read KHONG xay ra)
COMMIT;

7c. Phantom read — demo ở Read Committed

-- Gia su co bang tasks (schema goc cua TaskFlow)
-- Session A (READ COMMITTED):
BEGIN;
SELECT COUNT(*) FROM tasks WHERE status = 'todo';
-- => 5

-- Session B:
BEGIN;
INSERT INTO tasks(project_id, assignee_id, title, status)
  VALUES (1, NULL, 'Task moi', 'todo');
COMMIT;

-- Session A tiep tuc:
SELECT COUNT(*) FROM tasks WHERE status = 'todo';
-- => 6 (THEM 1 -- phantom read xay ra)
COMMIT;

-- Fix: Repeatable Read trong PG (Snapshot Isolation) can duoc phantom:
-- Session A (REPEATABLE READ):
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*) FROM tasks WHERE status = 'todo';
-- => 5
-- [Session B INSERT + COMMIT]
SELECT COUNT(*) FROM tasks WHERE status = 'todo';
-- => 5 (snapshot cu -- phantom read KHONG xay ra)
COMMIT;

Nhớ: đây là điểm PG mạnh hơn SQL standard — ANSI Repeatable Read cho phép phantom, nhưng PG RR (SI) cấm phantom.

7d. Write skew — demo ở Repeatable Read và Serializable

Scenario: TaskFlow có invariant "mỗi task chỉ được assign cho 1 user". Hai user cùng assign cùng một task đang trống:

-- Setup: task 42 chua duoc assign
UPDATE tasks SET assignee_id = NULL WHERE id = 42;

-- Session A (REPEATABLE READ):
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT assignee_id FROM tasks WHERE id = 42;
-- => NULL (task trong)
-- A quyet dinh: se assign cho user 1

-- Session B (REPEATABLE READ) -- bat dau SAU A:
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT assignee_id FROM tasks WHERE id = 42;
-- => NULL (van la NULL trong snapshot cua B)
-- B quyet dinh: se assign cho user 2

-- Session A:
UPDATE tasks SET assignee_id = 1 WHERE id = 42;
COMMIT;
-- Thanh cong -- assignee_id = 1

-- Session B:
UPDATE tasks SET assignee_id = 2 WHERE id = 42;
COMMIT;
-- Thanh cong (!!) -- assignee_id = 2 (ghi de A)
-- Write skew: ca A va B deu nghi task trong, ca 2 assign, ket qua cuoi sai

-- Fix: dung Serializable (SSI detect conflict, abort 1 tx)
-- Session A (SERIALIZABLE):
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT assignee_id FROM tasks WHERE id = 42;  -- SIREAD lock
-- => NULL

-- Session B (SERIALIZABLE):
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT assignee_id FROM tasks WHERE id = 42;  -- SIREAD lock (overlap)
-- => NULL

-- Session A:
UPDATE tasks SET assignee_id = 1 WHERE id = 42;
COMMIT;
-- Thanh cong

-- Session B:
UPDATE tasks SET assignee_id = 2 WHERE id = 42;
COMMIT;
-- ERROR: could not serialize access due to concurrent update
-- SQLSTATE: 40001
-- App retry -> lan nay thay assignee_id = 1 -> tu choi hoac xu ly khac

Ở Serializable, SSI phát hiện read-write dependency cycle và abort một transaction. App nhận SQLSTATE 40001 và phải retry.

8. Jepsen-aware — distributed SQL và isolation bugs

Isolation anomaly không chỉ là lý thuyết — chúng xuất hiện trong sản phẩm production khi implementation sai.

Jepsen là framework kiểm thử chaos cho distributed database — inject network partition, clock skew, node crash, và kiểm tra xem database có vi phạm isolation guarantee của nó không. Kyle Kingsbury (aphyr) đã phát hiện nhiều bug nghiêm trọng:

• Jepsen — CockroachDB beta-20160829: CockroachDB claim Serializable, nhưng Jepsen tìm thấy lost update và write skew ở thời điểm đó. Đội ngũ CockroachDB đã sửa và cải thiện isolation guarantee sau report này.

• Jepsen — TiDB 2.1.7: TiDB (MySQL-compatible distributed SQL) có anomaly ở Snapshot Isolation implementation — stale read, lost update, và write skew xuất hiện trong các điều kiện concurrent nhất định.

Bài học từ Jepsen: distributed SQL khó hơn single-node rất nhiều. Network partition, clock drift giữa các node, và distributed commit protocol tạo ra vô số corner case. Nếu bạn chạy multi-region hoặc distributed SQL cluster, đừng tin mù quáng vào isolation claim của vendor — hỏi xem họ có Jepsen report không, và report đó từ version nào.

Với PostgreSQL single-node, isolation guarantee đã được kiểm chứng rất kỹ. Nhưng nếu dùng Citus, Aurora, hoặc Neon (distributed PG variants), cần kiểm tra documentation về isolation behavior ở distributed transactions.

9. Pitfall — Read Committed mặc định không an toàn cho counter và money

// ANTI-PATTERN: read-modify-write o RC
const result = await client.query(
  'SELECT balance FROM accounts WHERE id = $1',
  [accountId]
);
const currentBalance = result.rows[0].balance;
const newBalance = currentBalance - withdrawAmount;

// Race condition o day: 2 request dong thoi doc cung balance,
// ca 2 tinh newBalance, ca 2 ghi -> 1 lan rut tien bi mat
await client.query(
  'UPDATE accounts SET balance = $1 WHERE id = $2',
  [newBalance, accountId]
);

-- Atomic: expression duoc evaluate trong cung statement, khong race
UPDATE accounts
  SET balance = balance - 100
  WHERE id = 1
    AND balance >= 100;  -- check du tien cung trong 1 statement
-- Kiem tra rows_affected: neu = 0 thi khong du tien

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- Row bi lock, tx khac phai cho den khi tx nay COMMIT hoac ROLLBACK
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
-- Chi tot khi can doc gia tri truoc de tinh toan phuc tap
-- Chi tiet o bai 6 cua module nay (Locks va Deadlock)

-- Dung khi co write skew risk -- tx phuc tap, nhieu bang
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... business logic phuc tap ...
COMMIT;
-- App retry neu nhan 40001

10. Applied — TaskFlow: 2 user assign cùng 1 task đồng thời

TaskFlow có feature "assign task": user mở task dashboard, thấy task chưa có assignee, bấm "Assign to me". Nếu 2 user làm điều này trong cùng khoảnh khắc — và app dùng Read Committed — cả 2 sẽ thành công, nhưng task chỉ thuộc về 1 người (người commit sau).

Đây là write skew: cả 2 đọc assignee_id IS NULL, cả 2 quyết định assign, cả 2 ghi. Kết quả cuối sai.

-- Schema tasks (goc TaskFlow):
-- tasks(id, project_id, assignee_id, title, status, ...)
-- assignee_id REFERENCES users(id) -- co the NULL

-- Pattern SAI (Read Committed, race condition):
BEGIN;
SELECT assignee_id FROM tasks WHERE id = $1;
-- App check: neu NULL thi assign
UPDATE tasks SET assignee_id = $current_user WHERE id = $1;
COMMIT;
-- 2 user cung lam buoc nay dong thoi: ca 2 deu update thanh cong

-- Pattern DUNG -- Option A: Serializable + retry
-- App code:
-- MAX_RETRY = 3
-- for attempt in 1..MAX_RETRY:
--   try:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT assignee_id FROM tasks WHERE id = $1;
-- Neu assignee_id IS NOT NULL: raise "da duoc assign", ROLLBACK
UPDATE tasks SET assignee_id = $current_user
  WHERE id = $1 AND assignee_id IS NULL;
-- Kiem tra affected rows
COMMIT;
--   catch SQLSTATE 40001: retry
--   catch other: raise

-- Pattern DUNG -- Option B: Atomic UPDATE + check affected rows (RC, don gian hon)
BEGIN;
UPDATE tasks
  SET assignee_id = $current_user
  WHERE id = $1
    AND assignee_id IS NULL;  -- chi update neu con trong
-- rows_affected = 0: task da duoc assign boi nguoi khac
-- rows_affected = 1: assign thanh cong
COMMIT;
-- Khong can Serializable vi WHERE assignee IS NULL la dieu kien atomic trong UPDATE

Option B đơn giản hơn và đúng trong trường hợp này — điều kiện AND assignee_id IS NULL trong UPDATE là atomic ở mọi isolation level. Option A cần thiết khi business logic phức tạp hơn và cần đọc nhiều bảng trước khi quyết định.

Retry pattern trong Node.js:

// Serializable + retry cho workflow phuc tap
async function assignTaskSafe(client, taskId, userId) {
  const MAX_RETRY = 3;

  for (let attempt = 1; attempt <= MAX_RETRY; attempt++) {
    try {
      await client.query(
        'BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE'
      );

      const { rows } = await client.query(
        'SELECT assignee_id FROM tasks WHERE id = $1 FOR UPDATE',
        [taskId]
      );

      if (rows[0].assignee_id !== null) {
        await client.query('ROLLBACK');
        return { success: false, reason: 'already_assigned' };
      }

      await client.query(
        'UPDATE tasks SET assignee_id = $1 WHERE id = $2',
        [userId, taskId]
      );

      await client.query('COMMIT');
      return { success: true };
    } catch (err) {
      await client.query('ROLLBACK');
      // SQLSTATE 40001: serialization failure -- retry
      if (err.code === '40001' && attempt < MAX_RETRY) {
        continue;
      }
      throw err;
    }
  }
}

Pattern này sẽ được mở rộng trong bài 7 của module này (mini-challenge TaskFlow end-to-end với Serializable).

11. Deep Dive

12. Tóm tắt

• 4 isolation level SQL standard: Read Uncommitted → Read Committed (PG default) → Repeatable Read → Serializable. PG không thực sự implement Read Uncommitted — xử lý như Read Committed.
• 4 anomaly (Berenson 1995): Dirty read (đọc chưa commit), Non-repeatable read (cùng row khác giá trị), Phantom read (cùng range khác tập kết quả), Write skew (cùng đọc + cùng ghi khác row → phá invariant).
• PG Repeatable Read = Snapshot Isolation: snapshot chụp lúc tx bắt đầu, mạnh hơn ANSI RR — cấm cả phantom read. ANSI RR chỉ cấm non-repeatable read.
• PG Serializable = SSI (Cahill 2008): SIREAD lock theo dõi read set, detect dependency cycle, abort 1 tx với SQLSTATE 40001. App phải retry.
• Write skew chỉ Serializable cấm: 2 tx cùng đọc tập data, cùng ghi vào 2 row khác nhau → không có write conflict nhưng phá invariant. Invisible với locking thông thường.
• Đọc ra app rồi ghi lại ở RC là anti-pattern: dùng atomic UPDATE expression (SET col = col - x) hoặc SELECT FOR UPDATE hoặc Serializable + retry.
• Distributed SQL cần audit thực tế: isolation claim trên giấy và implementation thực tế có thể khác nhau — Jepsen report là bằng chứng. Single-node PG an toàn hơn distributed variants.
• TaskFlow assign task: dùng atomic UPDATE + WHERE assignee_id IS NULL cho case đơn giản; Serializable + retry cho business logic phức tạp nhiều bảng.

13. Tự kiểm tra

Bài tiếp theo: MVCC internals — xmin/xmax, snapshot, visibility check, và so sánh với MySQL InnoDB