Bọc transaction vẫn lệch số: bẫy isolation level READ COMMITTED

Buổi đối soát UAT hôm ấy tôi là người phải trả lời.

Báo cáo tổng hợp cuối ngày là thứ tôi viết, sắp bật cho bộ phận vận hành dùng. Bên QC có bảng đối chiếu độc lập của họ, dựng bằng tay từ dump dữ liệu, và hai con số không khớp: bảng của họ ra 1.001.250.000, báo cáo của tôi ra 1.000.000.000. Lệch 1.250.000 đồng, đúng bằng một bút toán ghi Có trong ngày.

Chị QC xoay màn hình cho tôi xem chính tờ kết xuất của mình. Ba dòng, in cạnh nhau:

Tong dau ngay      1.000.000.000
Phat sinh trong ngay  +1.250.000
Tong cuoi ngay     1.000.000.000

Chị chỉ hỏi đúng một câu: "Ba dòng này lấy ra cùng một lúc không anh?" Tôi bảo có, cùng một lần chạy.

Ba dòng đó nằm trên cùng một tờ giấy và không cộng được với nhau, nghĩa là báo cáo tự mâu thuẫn với chính nó, chưa cần đem so với bảng nào khác. Tôi mở laptop chạy lại ngay tại phòng họp: khớp. Chạy lần nữa: vẫn khớp. Và tôi ngồi đó với thứ khó chống đỡ nhất trong một buổi đối soát, là một con số sai mà mình không tái hiện được.

Ba giả thuyết đầu đều sai

Về bàn, tôi đi theo thứ tự mà chắc ai cũng đi.

Giả thuyết một: sai công thức. Báo cáo JOIN bảng tài khoản với bảng bút toán, mà JOIN sai thì nhân bản hoặc rơi mất dòng là chuyện thường. Tôi dò lại từng nhánh, chạy tay từng câu trên bộ dữ liệu tĩnh. Đúng cả. Bộ dữ liệu tĩnh thì lúc nào cũng đúng, và đó là chi tiết đáng lẽ tôi phải để ý sớm hơn.

Giả thuyết hai: sai mốc cắt ngày. Nghiệp vụ ngân hàng có giờ cut-off, và một bút toán rơi vào vùng tranh chấp giữa hai ngày làm việc là ứng viên sáng giá. Tôi kiểm tra posted_on của bút toán 1.250.000 kia: nằm gọn trong ngày, cách mốc cut-off nhiều giờ. Ngõ cụt thứ hai.

Giả thuyết ba: thiếu transaction. Đây mới là chỗ tôi mất nhiều thời gian nhất, vì nó nghe đúng nhất. READ COMMITTED thì tôi thuộc lòng định nghĩa, nhưng chưa bao giờ nghĩ nó áp vào từng câu trong báo cáo của chính mình. Báo cáo chạy nhiều câu truy vấn liên tiếp; nếu chúng không nằm trong một transaction thì rõ ràng mỗi câu thấy một trạng thái khác nhau. Tôi bọc toàn bộ báo cáo vào BEGIN ... COMMIT, dựng lại kịch bản có batch bút toán chạy song song, và chạy lại.

Vẫn lệch. Vẫn đúng 1.250.000.

Đó là lúc tôi nhận ra mình đang tin vào một điều chưa bao giờ kiểm chứng: rằng bọc mấy câu lệnh vào một transaction thì chúng sẽ cùng nhìn một trạng thái dữ liệu. Câu đó nghe hiển nhiên tới mức tôi chưa từng hỏi nó đúng ở isolation level nào.

Một transaction không có nghĩa là một ảnh chụp

Trong bốn chữ ACID, A (atomicity: thay đổi hoặc vào hết hoặc không vào gì) và D (durability: commit rồi thì không mất) nói về thứ bạn ghi. Chữ I mới nói về thứ bạn đọc, và mức mặc định của nó hứa ít hơn bạn tưởng.

Chữ I đó là isolation level. Mặc định của PostgreSQL, SQL Server và Oracle đều là READ COMMITTED — mức chỉ hứa duy nhất một điều: bạn không đọc phải dữ liệu chưa commit của người khác. Nó không hứa rằng hai lần đọc của bạn thấy cùng một thế giới. (MySQL với InnoDB là ngoại lệ đáng nhớ: mặc định ở đó là REPEATABLE READ, nên cùng một đoạn code chuyển từ MySQL sang PostgreSQL có thể đổi hành vi đọc mà không ai sửa dòng nào.)

Cụ thể hơn, ở READ COMMITTED, database chụp một ảnh dữ liệu mới cho từng câu lệnh. Câu SELECT đầu chạy trên ảnh chụp lúc 14:03:07; ai đó commit lúc 14:03:08; câu SELECT sau chạy trên ảnh chụp lúc 14:03:09 và thấy đầy đủ thay đổi vừa rồi. Cả hai câu vẫn nằm trong một transaction của bạn, và điều đó chẳng ngăn được gì.

Nhìn sơ đồ thì rõ vì sao báo cáo tự mâu thuẫn. Câu 1 đọc số dư ở trạng thái trước bút toán. Câu 2 đọc phát sinh ở trạng thái sau bút toán. Bút toán vừa có mặt ở vế phải vừa vắng mặt ở vế trái, nên hai vế lệch nhau đúng bằng giá trị của nó. Đây là anomaly có tên riêng trong lý thuyết giao dịch: read skew, họ hàng gần của non-repeatable read.

Thứ tôi cần là một ảnh chụp dùng chung cho cả báo cáo, và REPEATABLE READ cấp đúng thứ đó.

Cột phải của bảng cố ý ghi rõ "của PostgreSQL", vì trên Oracle và SQL Server, cùng chữ REPEATABLE READ không sửa được bug này.

Và đây là chỗ đáng nhớ: với một báo cáo chỉ đọc, REPEATABLE READ là đủ. Không cần lên tới SERIALIZABLE. Mức cao nhất ấy sinh ra để chặn thêm các anomaly khi ghi đồng thời; phần đọc của nó chẳng "chuẩn" hơn chút nào. Cái giá phải trả cũng hay bị gán nhầm cho riêng nó: ở PostgreSQL, REPEATABLE READ cũng ném SQLSTATE 40001 khi transaction của bạn có ghi xung đột, nên "phải viết vòng retry" là điều kiện của cả hai mức — chỉ có báo cáo read-only như của tôi mới miễn, vì nó không ghi gì. Ranh giới giữa các mức, và anomaly nào bị chặn ở mức nào, được mổ đầy đủ trong bài Isolation levels và anomalies.

Tự dựng lại trong ba phút

Khỏi cần tin tôi, cứ mở hai cửa sổ psql nối vào cùng một database rồi chạy (chưa có Postgres local thì dựng theo bài cài Postgres local). Tôi dựng lại trên PostgreSQL 16.14, ba tài khoản chi nhánh với tổng đầu ngày tròn 1.000.000.000:

CREATE TABLE accounts (
  id      BIGSERIAL PRIMARY KEY,
  name    TEXT NOT NULL,
  balance NUMERIC(18,2) NOT NULL
);
CREATE TABLE postings (
  id         BIGSERIAL PRIMARY KEY,
  account_id BIGINT NOT NULL REFERENCES accounts(id),
  amount     NUMERIC(18,2) NOT NULL,
  posted_on  DATE NOT NULL DEFAULT CURRENT_DATE
);

INSERT INTO accounts(name, balance)
VALUES ('CN Ha Noi', 400000000), ('CN Da Nang', 250000000), ('CN Can Tho', 350000000);

Session 1 đóng vai báo cáo. Chạy tới câu pg_sleep rồi để đó:

BEGIN;                                    -- mac dinh READ COMMITTED
SELECT SUM(balance) AS tong_cuoi_ngay FROM accounts;
-- => 1000000000.00

SELECT pg_sleep(15);                      -- sang session 2 trong luc cho

SELECT SUM(amount) AS phat_sinh FROM postings WHERE posted_on = CURRENT_DATE;
-- => 1250000.00   <-- but toan xuat hien, du van trong cung transaction
COMMIT;

Session 2 đóng vai batch bút toán, chạy trong lúc session 1 đang chờ:

BEGIN;
UPDATE accounts SET balance = balance + 1250000 WHERE id = 2;
INSERT INTO postings(account_id, amount) VALUES (2, 1250000);
COMMIT;

Kết quả là đúng ba dòng không cộng được của tôi: tổng cuối ngày 1.000.000.000 đứng cạnh phát sinh 1.250.000, trong khi tổng đầu ngày cũng là 1.000.000.000.

Trước khi chạy lại, phải reset dữ liệu — lần chạy vừa rồi đã để lại số dư ở 1.001.250.000 và một dòng postings mang ngày hôm nay. Bỏ qua bước này thì câu SELECT thứ hai vẫn đọc được bút toán cũ, và bạn sẽ kết luận ngược hẳn: rằng REPEATABLE READ chẳng sửa được gì.

-- Chay o mot session bat ky, khi ca hai session kia da COMMIT xong
TRUNCATE postings;
UPDATE accounts SET balance = 400000000 WHERE id = 1;
UPDATE accounts SET balance = 250000000 WHERE id = 2;
UPDATE accounts SET balance = 350000000 WHERE id = 3;

Giờ đổi đúng một chữ ở dòng đầu của session 1 và chạy lại toàn bộ hai session:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Lần này câu SELECT thứ hai không còn thấy bút toán: bút toán của session 2 commit sau ảnh chụp, nên báo cáo không thấy nó ở bất kỳ đâu. Không thấy ở cả hai vế mới là nhất quán. Báo cáo phản ánh trung thực trạng thái database tại một thời điểm, và một báo cáo đối soát cần đúng như vậy.

Một chú thích nhỏ về thứ câu SELECT thứ hai in ra lúc này: NULL. SUM(amount) không có GROUP BY thì luôn trả về đúng một dòng, kể cả khi không khớp dòng nào, và giá trị của dòng đó là NULL. Ta gặp nó ở đây vì vừa TRUNCATE postings; trên dữ liệu thật thì chỉ ngày chưa có bút toán nào (ngày nghỉ, chi nhánh mới mở) mới ra ô "phát sinh trong ngày" trống trơn, đủ để người vận hành đọc thành "hệ thống lỗi". Bọc COALESCE(SUM(amount), 0) là xong.

Ảnh chụp không bắt đầu ở BEGIN

Biết dùng REPEATABLE READ rồi vẫn còn chỗ hụt chân, và tôi kiểm lại chỗ này vì đã đọc được cách diễn đạt "ảnh chụp được lấy khi transaction bắt đầu" ở nhiều nơi.

Cách diễn đạt đó không chính xác. Ảnh chụp được lấy ở câu lệnh đầu tiên, không phải ở BEGIN. Phép thử tách bạch được hai khả năng: mở transaction REPEATABLE READ, chờ mà không chạy câu SQL nào, để session khác commit trong lúc chờ, rồi mới SELECT.

Phép thử này cũng cần dữ liệu ở mốc đầu ngày, nên reset lần nữa trước khi chạy — nếu không, con số in ra sẽ mang theo bút toán của lượt trước và chẳng chứng minh được gì:

-- Reset lai y het lan truoc
TRUNCATE postings;
UPDATE accounts SET balance = 400000000 WHERE id = 1;
UPDATE accounts SET balance = 250000000 WHERE id = 2;
UPDATE accounts SET balance = 350000000 WHERE id = 3;

Rồi session 1 chạy đoạn dưới, còn session 2 chạy lại đúng batch bút toán ở trên trong lúc session 1 đang sleep:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
\! sleep 15                               -- cho o phia client, KHONG phai cau SQL
SELECT SUM(balance) FROM accounts;
-- => 1001250000.00

Con số trả về đã bao gồm bút toán commit sau BEGIN. Nếu ảnh chụp lấy ở BEGIN thì nó phải là 1.000.000.000. Nói cách khác, mở transaction sớm không giữ chỗ cho bạn: đồng hồ chỉ bắt đầu chạy khi câu lệnh đầu tiên chạm database.

// SAI: moi lan goi service mo mot transaction rieng
BaoCao lap() {
    var tongSoDu  = accountService.tongSoDu();     // transaction 1
    var phatSinh  = postingService.phatSinhHomNay(); // transaction 2
    return new BaoCao(tongSoDu, phatSinh);
}

// DUNG: mot transaction bao ca bao cao, dat isolation o dung bien ngoai
@Transactional(readOnly = true, isolation = Isolation.REPEATABLE_READ)
BaoCao lap() {
    var tongSoDu = accountService.tongSoDu();
    var phatSinh = postingService.phatSinhHomNay();
    return new BaoCao(tongSoDu, phatSinh);
}

Bút toán 1.250.000 hôm ấy không sai một đồng nào. Sổ sách đúng, batch đúng, cả bảng đối chiếu của chị QC cũng đúng. Chỉ có báo cáo của tôi là hỏi database hai lần rồi tin rằng hai câu trả lời nói về cùng một khoảnh khắc.

Nên tôi trả lại cho bạn câu chị QC hỏi tôi trong phòng họp hôm đó, ba dòng này có lấy ra cùng một lúc không, nói bằng ngôn ngữ của code: báo cáo tổng hợp trong repo bạn đang chạy ở isolation level nào, và nó có thật sự là một transaction không? Nếu phải mở code lên mới trả lời được thì Isolation levels và anomalies là bài trả lời giúp bạn phần còn lại, và nó nằm trong khoá SQL & Database cùng bài ACID deep dive đứng ngay trước.