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 không nói gì thêm, chỉ 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

Ba dòng đó nằm trên cùng một tờ giấy và không cộng được với nhau. Không phải báo cáo lệch so với hệ thống khác — nó tự mâu thuẫn với chính nó. 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. 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

Transaction đảm bảo cho bạn hai thứ rất rõ ràng: các thay đổi của bạn hoặc vào hết hoặc không vào gì (atomicity), và khi đã commit thì không mất (durability). Cả hai đều nói về thứ bạn ghi. Không cái nào hứa hẹn gì về thứ bạn đọc.

Cái quyết định thứ bạn đọc 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 không phải là transaction dài hơn, mà là một ảnh chụp duy nhất dùng chung cho cả báo cáo. Đó chính là thứ REPEATABLE READ cấp cho bạn.

Cột phải của bảng cố ý ghi rõ "của PostgreSQL", vì đó là phạm vi của cách sửa này. PostgreSQL cài đặt REPEATABLE READ bằng snapshot isolation, nên nó chặn luôn cả dòng mới INSERT vào — bút toán của session 2 không lọt vào ảnh chụp dù nó là một dòng chưa từng tồn tại lúc chụp. Hai engine còn lại không đi theo đường đó. Oracle không có mức REPEATABLE READ — chỉ READ COMMITTED, SERIALIZABLE và READ ONLY; báo cáo chỉ đọc thì dùng READ ONLY. Còn REPEATABLE READ của SQL Server là lock-based: nó giữ khoá trên các dòng đã đọc nhưng không ngăn dòng mới xuất hiện, nên INSERT INTO postings vẫn lọt vào SELECT ... WHERE posted_on = CURRENT_DATE và bug này không được sửa — ở đó phải bật SNAPSHOT isolation.

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, chứ không đọc "chuẩn" hơn. 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. 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.

Chú ý đúng thứ nó in ra, vì ở đây có một cái bẫy nhỏ đi kèm chính cách sửa. 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 — chỉ là giá trị NULL chứ không phải 0. Nên sau khi sửa, ô "phát sinh trong ngày" trên báo cáo sẽ trống trơn thay vì hiện số 0, và người vận hành đọc ô trống thành "hệ thống lỗi". Bọc COALESCE(SUM(amount), 0) là xong.

Cái bẫy thứ hai: ả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 câu hỏi mình đã bị hỏi trong phòng họp hôm đó, cho bạn: 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.