Trừ tồn kho trên hàng nóng: ba cách, ba cái giá

TL;DR: Trên một row tồn kho Postgres ở Read Committed, ba cách trừ đều không bán quá nếu điều kiện stock > 0 nằm trong câu UPDATE. Chúng khác nhau ở cái giá: FOR UPDATE giữ khoá qua cả khoảng giữa hai câu, còn optimistic version (lạc quan: không khoá lúc đọc, chỉ kiểm lại cột version khi ghi) bắt app làm lại mỗi lần đụng nhau. Cả ba vẫn xếp hàng trên cùng một row, nên thêm kết nối không thêm thông lượng.

Bài 02 cho thấy đếm đơn rồi ghi bán quá cả khi đã bật transaction; hướng thoát là một row tồn kho với câu ghi có điều kiện. Nhưng "một row, ghi có điều kiện" còn ít nhất ba cách viết, và cả ba đều qua bài kiểm tra "không bán quá". Chọn cách nào là chuyện giá. Thuật ngữ: "round-trip" là một lượt gửi câu lệnh từ ứng dụng sang database rồi nhận kết quả về; "row" là một dòng trong bảng; "hàng nóng (hot row)" là row tồn kho mà mọi người cùng ghi vào. Bài này bắt bạn chọn trước, rồi đo từng cái giá trên chính Postgres của bạn.

1. Ba cách trừ tồn kho, cái giá nào ràng buộc này gánh được?

Ràng buộc là bảng chuẩn đã công bố ở bài 01, giữ nguyên số: 1 SKU, tồn kho 1.000, tải đỉnh khoảng 50.000 lượt/giây, không được bán quá dù chỉ một đơn vị. Bài này chỉ dùng một row trên Postgres: không Redis, không chia nhỏ tồn kho (chia nhỏ để bài 05 nói).

Ba cách viết, chỉ mô tả việc làm, chưa nói giá:

-- A: mot cau UPDATE co dieu kien
UPDATE items SET stock = stock - 1 WHERE id = 1 AND stock > 0;

-- B: khoa hang lai, roi UPDATE giong A, roi COMMIT
BEGIN;
SELECT stock FROM items WHERE id = 1 FOR UPDATE;
UPDATE items SET stock = stock - 1 WHERE id = 1 AND stock > 0;
COMMIT;

-- C: doc stock va version (cot dem so lan hang bi sua)
SELECT stock, version FROM items WHERE id = 1;
UPDATE items SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5 AND stock > 0;  -- 5 la version vua doc
-- khong hang nao bi sua = co nguoi sua truoc, lam lai tu dau

Hãy chọn một phương án trước khi đọc tiếp. Trần đã đo ở bài 01: một row ở 50 kết nối đạt khoảng 1.200 lượt/giây trên máy tác giả (máy khác thì tự đo), tải đỉnh gấp khoảng 40 lần.

2. Vì sao UPDATE có điều kiện không bán quá dù không FOR UPDATE?

Nhiều người nghi: không FOR UPDATE thì sao hai lượt cùng thấy stock = 1 rồi cùng trừ? Câu trả lời nằm ở cách Read Committed xử lý một UPDATE đang chờ.

Khi một UPDATE tìm thấy row mà transaction khác đang sửa, nó chờ. Nếu transaction kia commit, UPDATE đang chờ lấy bản mới nhất và đánh giá lại mệnh đề WHERE trên bản đó; chỉ khi vẫn khớp, nó mới ghi. Postgres nói đúng điều này trong tài liệu về Read Committed (Transaction Isolation).

Áp vào tồn kho: hai lượt cùng nhắm stock = 1. Lượt thứ nhất lấy khoá row và trừ về 0. Lượt thứ hai chờ, thấy lượt thứ nhất commit, đánh giá lại stock > 0 trên bản mới (stock = 0), không còn khớp, nên trả 0 row. Điều kiện nằm trong câu ghi, và việc kiểm lại diễn ra sau khi đã có khoá, nên không có khe hở giữa kiểm tra và ghi.

Hệ quả: A không cần FOR UPDATE để đúng; nó chỉ có ích khi bạn phải đọc giá trị ra để quyết định chuyện phức tạp hơn một phép trừ.

Thử ngẫmnếu UPDATE đang chờ không đánh giá lại WHERE mà dùng bản row cũ, hai lượt cuối cùng của tồn kho sẽ cho kết quả gì?

Hàng đợi khoá row trông thế nào

Cái giá của A, B, C nằm ở một chỗ chung: chỉ một transaction được sửa một row tại một thời điểm, những cái còn lại xếp hàng. Hình dung một siêu thị chỉ có một quầy thu ngân:

Mở thêm cửa ra vào không làm quầy nhanh hơn. Analogy gãy ở một chỗ: người ở quầy thật không "kiểm lại còn hàng", còn UPDATE thì đánh giá lại WHERE sau khi lấy được khoá. Mô hình hàng đợi trên một row:

Ở 50 kết nối, một đang ghi và gần như tất cả còn lại đang chờ. Trong pg_stat_activity: wait_event_type = Lock là đang chờ khoá; transactionid là chờ transaction giữ row kết thúc, tuple là xếp sau một kết nối khác cũng đang chờ cùng row (bảng Lock wait events).

Tác giả chạy A ở 50 kết nối và hỏi pg_stat_activity hai lần: mẫu đầu Lock/transactionid 39 và LWLock/LockManager 10, mẫu sau Lock/tuple 36 và Lock/transactionid 13. Cả hai cho cùng một bức tranh: khoảng 39 đến 49 trong 50 kết nối chờ khoá row; 10 kết nối còn lại ở mẫu đầu đang tranh LWLock của lock manager (bảng băm dùng chung quản lý các khoá) trên đường đi lấy khoá.

3. Tự đo cả ba trên máy bạn

Số tuyệt đối khác máy nên bạn so tỉ lệ:

CREATE TABLE items (id int PRIMARY KEY, stock int, version int);
INSERT INTO items VALUES (1, 1000000000, 0);

Tồn kho 10^9 để mọi lượt trong 10 giây đều mua được: bài này đo cơ chế khoá, chưa đo chuyện hết hàng.

-- a.sql: conditional UPDATE
BEGIN;
UPDATE items SET stock = stock - 1 WHERE id = 1 AND stock > 0;
COMMIT;

-- b.sql: FOR UPDATE then UPDATE (lock held from SELECT to COMMIT)
BEGIN;
SELECT stock FROM items WHERE id = 1 FOR UPDATE;
UPDATE items SET stock = stock - 1 WHERE id = 1 AND stock > 0;
COMMIT;

-- b-cham.sql: B voi viec chen giua (gia lap goi dich vu ngoai 20 ms)
BEGIN;
SELECT stock FROM items WHERE id = 1 FOR UPDATE;
\sleep 20 ms
UPDATE items SET stock = stock - 1 WHERE id = 1 AND stock > 0;
COMMIT;

-- c.sql: optimistic version
BEGIN;
SELECT stock, version AS v FROM items WHERE id = 1 \gset
UPDATE items SET stock = stock - 1, version = version + 1 WHERE id = 1 AND version = :v AND stock > 0;
COMMIT;

Chạy từng script ở 1, 10 và 50 kết nối (pgbench), đặt lại bảng giữa các lần:

psql -c "UPDATE items SET stock = 1000000000, version = 0" mydb
pgbench -n -c 50 -j 5 -T 10 -f a.sql mydb      # vary -c: 1, 10, 50; vary -f: b.sql, b-cham.sql, c.sql

Trong lúc A chạy, ở terminal thứ hai:

SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE datname = current_database() AND pid <> pg_backend_pid()
GROUP BY 1, 2 ORDER BY 3 DESC;

pgbench không báo "0 row", nên với C bạn tự đếm: mỗi lượt thành công trừ đúng 1 tồn kho. Sau khi chạy xong:

SELECT 1000000000 - stock AS thanh_cong FROM items WHERE id = 1;
-- zero_row_rate = 1 - thanh_cong / transactions reported by pgbench

4. Số đo, và lời giải cho phép thử đoán

Điền vào bảng của bạn trước, rồi so với bảng của tác giả (Postgres 16.14, Apple M-series 10 nhân, Docker; lượt/giây, một lần chạy). Mỗi lần chạy ra một số khác, nên bảng này chỉ để đọc tỉ lệ:

Hai điều đọc ra. Thông lượng ở 50 kết nối không cao hơn ở 1 kết nối: thêm kết nối chỉ thêm thời gian chờ. Và khi khoá row là nút cổ chai, thông lượng xấp xỉ 1 chia cho thời gian giữ khoá. B giữ khoá từ SELECT ... FOR UPDATE, qua một round-trip, tới COMMIT, nên dài hơn A đúng một round-trip nằm trong vùng khoá. Số đo không tách được chênh lệch B so với A khỏi nhiễu, nên đừng kết luận từ riêng dòng đó; b-cham.sql mới tách được. Cái giá thật của B lộ khi có việc khác chen vào giữa hai câu (bẫy ở mục 5): mỗi lượt giữ khoá thêm 20 ms thì trần rơi từ cỡ nghìn xuống cỡ chục lượt/giây.

Lời giải: qua các lần chạy của tác giả, 83 đến 91% lượt ghi trả 0 row (một lần đo: 7.783 transaction, chỉ 740 lượt thành công, tức 90,5%). Kết nối nào lấy được khoá trước thì commit và tăng version lên v+1; chỉ những lượt đã đọc v trước lúc commit đó mới chờ, đánh giá lại WHERE version = v trên bản mới, thấy không khớp và trả 0 row. Các lượt đọc sau commit thì thấy v+1 và có cơ hội thắng vòng sau. Kết quả là mỗi lượt mua thật tốn khoảng 6 đến 11 lần thử.

Ví dụ nhỏ: version đang là 5, ba kết nối cùng đọc.

Ba lượt, chỉ một lượt mua thật, hai lượt làm lại; ở 50 kết nối, phần lớn trong 49 người còn lại thất bại mỗi vòng.

Version không tránh được hàng đợi: nó vẫn chờ khoá row, rồi trả 0 row và bắt app làm lại từ đầu. Thứ nó tránh được là giữ khoá trong lúc app nghĩ: không có khoá nào mở qua suy nghĩ của app giữa lần đọc và lần ghi.

Thử ngẫmtrong code bạn đang viết, chỗ nào giữa SELECT và UPDATE bạn đã để việc khác chen vào?

Kết luận dưới ràng buộc này

Dưới ràng buộc chuẩn, app không làm gì giữa hai bước nên A gánh được: giữ khoá từ UPDATE tới COMMIT, ít hơn B một round-trip, và đúng mà không cần FOR UPDATE. B và C chỉ đáng khi bạn cần thứ A không có, và ở hàng nóng của flash sale, C đốt phần lớn lượt ghi vào việc làm lại.

Cái gì vỡ tiếp ở tải khoảng 40 lần trần là chuyện của bài 05.

5. Hai cái bẫy đã đo được

❌ Quên AND stock > 0 ở bước UPDATE của B. FOR UPDATE khoá row nhưng không ngăn bạn trừ xuống âm: nó chỉ bảo đảm không ai chen vào giữa. Chạy B không có điều kiện, stock về -11.810. ✅ Điều kiện bất biến nằm ngay trong câu ghi, và app đọc số row bị ảnh hưởng: 0 nghĩa là hết hàng. Cùng lý do, C với tồn kho thật cũng phải có AND stock > 0, như c.sql ở trên.

-- sai: khong co dieu kien, stock co the am
UPDATE items SET stock = stock - 1 WHERE id = 1;
-- dung: dieu kien nam ngay trong cau ghi
UPDATE items SET stock = stock - 1 WHERE id = 1 AND stock > 0;

❌ Giữ FOR UPDATE qua lời gọi thanh toán. Cổng thanh toán mất 100 đến 500 ms. Trong khoảng đó khoá row vẫn nằm trong tay bạn, và cả hàng đợi đứng theo: khoá cấp tuần tự nên mỗi lượt giữ khoá 0,5 giây thì một giây chỉ làm được 1 ÷ 0,5 = 2 lượt (so với cỡ 1.200 khi giữ chưa tới 1 ms). 50 người xếp hàng thì người đứng cuối chờ 50 × 0,5 = 25 giây. ✅ Trừ (hoặc giữ chỗ) trước trong transaction ngắn, gọi thanh toán ngoài transaction. Nhưng khi đó có chuyện tồn kho bị giữ mà không ai trả tiền, đó là bài 04.

-- sai: khoa van giu trong luc goi cong thanh toan
BEGIN;
SELECT stock FROM items WHERE id = 1 FOR UPDATE;
-- goi cong thanh toan o day: 100-500 ms, khoa van dang giu
UPDATE items SET stock = stock - 1 WHERE id = 1 AND stock > 0;
COMMIT;

-- dung: tru nhanh trong transaction ngan
BEGIN;
UPDATE items SET stock = stock - 1 WHERE id = 1 AND stock > 0;
COMMIT;
-- goi cong thanh toan SAU COMMIT, khong con khoa

6. Đào sâu

Tài liệu chính thức của PostgreSQL 16:

• Transaction Isolation: Read Committed: đoạn mô tả UPDATE chờ rồi đánh giá lại WHERE trên bản row mới nhất.
• The Locking Clause: ngữ nghĩa FOR UPDATE, kèm NOWAIT và SKIP LOCKED (bài 04 dùng SKIP LOCKED).

Tóm tắt

• Việc cần làm: viết điều kiện stock > 0 trong câu UPDATE, mặc định chọn A, và chỉ chuyển sang B hoặc C khi cần thứ A không có.

Liên hệ các bài khác

• Bài 01: nguồn của bảng ràng buộc và trần một row mà bài này đo lại.
• Bài 02: phương án ngây thơ bán quá, và là lý do cần ghi có điều kiện trên một row.
• Bài 04: đặt câu trừ của A vào transaction giữ chỗ, và xử lý tồn kho bị giữ mà không ai trả tiền.
• Bài 05: khi một row không đủ cho tải đỉnh, chia nhỏ tồn kho.

7. Tự kiểm tra

Bài tiếp theo: Giữ chỗ có hạn xử lý chỗ giữ bị bỏ không trả tiền; sau đó bài 05 bàn tải vượt trần một row khoảng 40 lần, kể cả chia nhỏ tồn kho.