ACID & isolation level trong PostgreSQL

Chương này dùng một project chạy xuyên suốt: shop-app trên PostgreSQL với 4 bảng orders, order_items, products, wallets. ACID là 4 tính chất mà một transaction của database hứa với bạn.

1. Bài toán

Khách đặt hàng trên shop-app. Một lần "đặt hàng" gồm nhiều câu SQL:

BEGIN;
UPDATE wallets  SET balance = balance - 300 WHERE user_id = 7;
UPDATE products SET stock   = stock - 1     WHERE id = 42;
INSERT INTO orders (id, user_id, total, status) VALUES (1001, 7, 300, 'PAID');
INSERT INTO order_items (order_id, product_id, qty, price) VALUES (1001, 42, 1, 300);
COMMIT;

2. Vấn đề

• Server crash sau câu thứ 1: ví bị trừ tiền nhưng không có order.
• Hai khách cùng mua sản phẩm cuối cùng: stock thành −1.
• Vừa COMMIT xong thì mất điện: order có còn không?
• Một báo cáo đang đọc giữa chừng: thấy tiền đã trừ nhưng chưa thấy order.

3. Giải pháp: 4 chữ ACID

Chú ý: chữ C phần lớn là trách nhiệm của bạn (khai báo constraint, viết logic đúng). Database chỉ đảm bảo không vi phạm các constraint đã khai báo.

4. Isolation level và các anomaly

Isolation "hoàn hảo" (như chạy lần lượt từng transaction) thì chậm. Nên SQL chuẩn định nghĩa nhiều mức, mỗi mức cho phép một số anomaly:

Bảng isolation level trong PostgreSQL (khác chuẩn một chút):

5. Code từng bước

Bước 1: Constraint cho chữ C

ALTER TABLE wallets  ADD CONSTRAINT wallet_balance_non_negative CHECK (balance >= 0);
ALTER TABLE products ADD CONSTRAINT product_stock_non_negative  CHECK (stock >= 0);

Bước 2: Thử anomaly bằng 2 cửa sổ psql (READ COMMITTED)

-- Session A                               -- Session B
BEGIN;
SELECT stock FROM products WHERE id=42; -- 5
                                           BEGIN;
                                           UPDATE products SET stock=3 WHERE id=42;
                                           COMMIT;
SELECT stock FROM products WHERE id=42; -- 3  ← non-repeatable read
COMMIT;

Mỗi câu lệnh trong READ COMMITTED lấy một snapshot mới. Đổi sang BEGIN ISOLATION LEVEL REPEATABLE READ; thì câu thứ hai vẫn ra 5, vì snapshot lấy một lần cho cả transaction.

Bước 3: Write skew. Luật: mỗi user tối đa 1 order PENDING.

-- A và B (REPEATABLE READ) cùng chạy, cùng user 7:
SELECT count(*) FROM orders WHERE user_id=7 AND status='PENDING';  -- 0, cả hai đều thấy 0
INSERT INTO orders(user_id, status, total) VALUES (7, 'PENDING', 100);
COMMIT;   -- cả hai cùng thành công → user 7 có 2 order PENDING

Không ai ghi đè ai, nên REPEATABLE READ không phát hiện. Cách chữa: SERIALIZABLE, hoặc đơn giản hơn là đưa luật vào constraint:

CREATE UNIQUE INDEX one_pending_order_per_user ON orders(user_id) WHERE status = 'PENDING';

Bước 4: Spring Boot chọn isolation + retry

@Service
@RequiredArgsConstructor
public class CheckoutService {
    private final CheckoutTx tx;

    public OrderDto checkout(CheckoutRequest req) {
        for (int attempt = 1; ; attempt++) {
            try {
                return tx.placeOrder(req);              // transaction mới mỗi lần
            } catch (CannotAcquireLockException | ConcurrencyFailureException e) {
                if (attempt == 3) throw e;              // SQLState 40001 / 40P01
            }
        }
    }
}

@Component
class CheckoutTx {
    @Transactional(isolation = Isolation.SERIALIZABLE)
    public OrderDto placeOrder(CheckoutRequest req) { /* đọc, kiểm tra luật, ghi */ }
}

Retry đặt ngoài method @Transactional (lý do giống bài 4.3.10). Spring dịch SQLState 40001 serialization_failure thành một subclass của ConcurrencyFailureException.

Bước 5: Test

curl -X POST localhost:8080/api/orders -H 'Content-Type: application/json' \
  -d '{"userId":7,"items":[{"productId":42,"qty":1}]}' &
curl -X POST localhost:8080/api/orders -H 'Content-Type: application/json' \
  -d '{"userId":7,"items":[{"productId":42,"qty":1}]}' &
wait
psql -c "SELECT count(*) FROM orders WHERE user_id=7 AND status='PENDING';"   -- phải là 1

6. Cách hoạt động của SERIALIZABLE trong PostgreSQL

PostgreSQL dùng SSI (Serializable Snapshot Isolation):
  - Không khóa khi đọc. Chỉ GHI NHẬN "A đã đọc cái B sắp ghi" (rw-dependency)
  - Thấy vòng phụ thuộc nguy hiểm A → B → A
  - Hủy 1 transaction lúc commit:
      ERROR: could not serialize access due to read/write dependencies
      SQLSTATE 40001
  → App PHẢI retry. Không retry = người dùng thấy lỗi 500.

7. Khi nào dùng level nào

• READ COMMITTED: mặc định cho hầu hết API. Kết hợp atomic UPDATE, FOR UPDATE, @Version, constraint.
• REPEATABLE READ: báo cáo/export cần một snapshot nhất quán nhiều câu query.
• SERIALIZABLE: luật nghiệp vụ phức tạp, khó biểu diễn bằng constraint hoặc lock (write skew). Phải có retry, transaction ngắn.
• Không nên tăng isolation "cho chắc" toàn hệ thống: nhiều lỗi 40001 hơn, nhiều retry hơn.

Bạn nhận ra đúng chỗ dễ nhầm. Chữ "atomic" mang hai nghĩa khác nhau tùy ngữ cảnh:

Vậy định nghĩa bạn học là atomic theo nghĩa concurrency, và đúng là nó gần với isolation trong ACID. Atomicity của ACID thì không nói gì về người khác nhìn thấy gì; nó nói về failure: crash, exception, ROLLBACK. Database cần cả hai: A lo "không nửa vời khi lỗi", I lo "không nửa vời khi chạy song song".

Atomicity (ACID):  [trừ tiền] [tạo order] ✗ crash  → rollback cả hai
Isolation (ACID):  A đang giữa chừng → B không thấy thay đổi của A trước khi A commit

Đủ, nếu bạn viết câu UPDATE đúng. READ COMMITTED không chặn lost update khi bạn "đọc vào Java rồi ghi lại". Nhưng PostgreSQL có một hành vi quan trọng: khi UPDATE gặp row đang bị transaction khác sửa, nó chờ, rồi đánh giá lại WHERE trên phiên bản mới nhất. Nên câu này an toàn ở READ COMMITTED:

UPDATE wallets SET balance = balance - 300
WHERE user_id = 7 AND balance >= 300;     -- 0 row = không đủ tiền

Còn ở REPEATABLE READ, cùng tình huống đó PostgreSQL ném 40001 could not serialize access due to concurrent update, bạn phải retry. Vì vậy cách phổ biến: giữ READ COMMITTED, dùng atomic UPDATE / FOR UPDATE / @Version cho từng chỗ cần thiết, chỉ nâng level ở chỗ thật sự cần.

Q1. ACID là gì?

🇻🇳 Tiếng Việt:

ACID là 4 đảm bảo của transaction. Atomicity: tất cả câu lệnh thành công hoặc rollback hết. Consistency: dữ liệu đi từ trạng thái hợp lệ sang trạng thái hợp lệ, nhờ constraint và logic ứng dụng. Isolation: các transaction song song không thấy trạng thái dở dang của nhau, mức độ tùy isolation level. Durability: đã commit thì không mất dù crash; PostgreSQL làm bằng WAL được fsync trước khi trả về commit.

🇬🇧 English:

ACID is the set of guarantees of a transaction. Atomicity: all statements succeed or everything is rolled back. Consistency: data moves from one valid state to another, enforced by constraints and application logic. Isolation: concurrent transactions do not see each other's intermediate state, to a degree set by the isolation level. Durability: once committed, data survives a crash; PostgreSQL does this by flushing the WAL before acknowledging the commit.

Q2. Các isolation level và anomaly? PostgreSQL mặc định là gì?

🇻🇳 Tiếng Việt:

Anomaly gồm dirty read, non-repeatable read, phantom read, lost update và write skew. PostgreSQL mặc định READ COMMITTED: mỗi câu lệnh thấy snapshot mới, không có dirty read nhưng có non-repeatable read và lost update nếu đọc-sửa-ghi. REPEATABLE READ dùng một snapshot cho cả transaction, chặn cả phantom nhưng còn write skew. SERIALIZABLE dùng SSI, chặn tất cả và hủy transaction bằng lỗi 40001, nên app phải retry.

🇬🇧 English:

The anomalies are dirty read, non-repeatable read, phantom read, lost update and write skew. PostgreSQL defaults to READ COMMITTED: each statement sees a fresh snapshot, so there is no dirty read, but non-repeatable reads and lost updates are possible with read-modify-write. REPEATABLE READ uses one snapshot for the whole transaction and also prevents phantoms, but write skew remains. SERIALIZABLE uses SSI, prevents all of them and aborts a transaction with error 40001, so the application must retry.

Q3. Write skew là gì, xử lý thế nào?

🇻🇳 Tiếng Việt:

Hai transaction cùng đọc một tập dữ liệu, mỗi bên ghi vào row khác nhau, riêng từng bên thì hợp lệ nhưng gộp lại vi phạm luật, ví dụ một user có hai order PENDING. Em xử lý bằng constraint như partial unique index nếu được, hoặc khóa một row chung bằng FOR UPDATE, hoặc dùng SERIALIZABLE kèm retry.

🇬🇧 English:

Two transactions read the same data, each writes a different row, and each is valid alone but together they break a rule, for example a user ending up with two PENDING orders. I fix it with a constraint such as a partial unique index when possible, or by locking a shared row with FOR UPDATE, or by using SERIALIZABLE with retry.

Cách nhớ ngắn gọn

• A = lỗi thì rollback hết; I = chạy song song không thấy dở dang.
• PostgreSQL mặc định READ COMMITTED: snapshot mỗi câu lệnh.
• REPEATABLE READ: snapshot cả transaction, còn write skew.
• SERIALIZABLE: lỗi 40001 → retry ngoài transaction.
• Luật nghiệp vụ đưa được vào constraint thì đưa vào constraint.