Transaction là gì, isolation level là gì? Chạy thử trên PostgreSQL, MySQL
Transaction là gì mà trừ kho vẫn sai? Chạy thử 4 mức isolation level trên PostgreSQL, MySQL để hiểu dirty read, phantom read, MVCC và cách chặn lost update.
Bạn có thể đã gặp chuyện hai khách cùng đặt được hũ nến cuối cùng trên shop, hoặc báo cáo cuối ngày cộng ra số liệu lệch nhau dù đọc lại code không thấy bug rõ ràng. Phần lớn những ca khó hiểu này nằm ở cách database gom nhóm câu lệnh trong transaction và chọn isolation level.
Lý thuyết nền đã có trong bài ACID là gì, nên bài này mình chạy thử từng lỗi đọc trên PostgreSQL và MySQL để bạn thấy tận mắt kết quả.
Transaction là gì? BEGIN, COMMIT và ROLLBACK qua ví dụ trừ kho
Transaction là một nhóm câu lệnh SQL được database coi như một khối thống nhất. Bạn mở khối bằng BEGIN, lưu tất cả bằng COMMIT, hoặc gọi ROLLBACK để hủy sạch nếu có lỗi giữa chừng.
Giả sử mình làm backend cho một shop bán nến thơm. Khi khách bấm mua, server phải vừa tạo đơn hàng vừa trừ tồn kho; hai câu lệnh này phải cùng thành công hoặc cùng hủy.
BEGIN;
INSERT INTO orders (product_id, quantity) VALUES (1, 1);
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;
-- If either statement fails, run ROLLBACK; to undo both the order and the stock change
Nếu lệnh trừ kho lỗi mà đơn vẫn lưu, shop rất dễ bán quá số hàng đang có. Cách trên giải quyết được trong một database đơn lẻ, còn khi nghiệp vụ đi qua nhiều database hoặc nhiều service thì bạn cần xem thêm về distributed transaction.
Isolation level là gì và vì sao cần nó
Khi app có nhiều người dùng cùng lúc, hàng chục transaction sẽ chạy song song trên server. Isolation level là cấu hình quyết định một transaction đang chạy được nhìn thấy thay đổi của các transaction khác tới mức nào.
Chuẩn SQL chia ra 4 mức từ thấp đến cao gồm Read Uncommitted, Read Committed, Repeatable Read và Serializable. Bạn chọn mức càng cao thì dữ liệu đọc ra càng ít sai lệch, đổi lại database phải bắt nhiều transaction chờ nhau hoặc hủy bớt, nên lúc đông khách app dễ phải chờ hay chạy lại hơn.
Dirty read, non-repeatable read và phantom read khác nhau thế nào
Khi hai transaction chạy đè lên nhau, app có thể vướng ba kiểu lỗi đọc dữ liệu:
- Dirty read xảy ra khi bạn đọc phải số tồn kho vừa bị một transaction khác sửa nhưng chưa
COMMITvà có thể hủy ngay sau đó. - Non-repeatable read là lúc bạn đọc tồn kho của một hũ nến hai lần trong cùng một transaction nhưng nhận về hai giá trị khác nhau vì phiên khác vừa
COMMITlệnh sửa ở giữa. - Phantom read xuất hiện khi bạn chạy lại cùng một câu query đếm các loại nến còn hàng nhưng số row trả về thay đổi do có người vừa thêm hoặc xóa sản phẩm.
Mình đã chạy thử trên máy Linux cài PostgreSQL 17.11 và MySQL 9.7.4 với hai phiên A và B chạy xen kẽ như mở hai cửa sổ psql hay mysql cạnh nhau, trong đó phiên B để autocommit tự lưu trừ khi gõ BEGIN. Nếu chưa có môi trường, bạn xem bài PostgreSQL là gì để cài đặt trước khi tạo bảng products cùng hai dòng mẫu nen oai huong và nen sa.
CREATE TABLE products (
id int PRIMARY KEY,
name text,
stock int
);
CREATE TABLE orders (
id serial PRIMARY KEY,
product_id int REFERENCES products(id),
quantity int
);
INSERT INTO products VALUES
(1, 'nen oai huong', 10),
(2, 'nen sa', 5);
4 mức isolation level và mức mặc định của PostgreSQL, MySQL
Mỗi mức cô lập chặn được một số lỗi đọc nhất định theo bảng isolation level của PostgreSQL:
| Mức | Dirty read | Non-repeatable read | Phantom read |
|---|---|---|---|
| Read Uncommitted | Có thể theo chuẩn, PostgreSQL không có | Có thể | Có thể |
| Read Committed | Không | Có thể | Có thể |
| Repeatable Read | Không | Không | Có thể theo chuẩn, PostgreSQL không có |
| Serializable | Không | Không | Không |
PostgreSQL thực chất chỉ cài đặt 3 mức vì Read Uncommitted của nó chạy y hệt Read Committed, còn mức Serializable chặn luôn cả serialization anomaly, tức kết quả không khớp với bất kỳ thứ tự chạy lần lượt nào của các transaction.
Khi mình gõ SHOW default_transaction_isolation trên PostgreSQL, kết quả trả về read committed. Ngược lại, MySQL InnoDB để mặc định ở mức Repeatable Read theo trang InnoDB isolation levels, và lúc mình chạy SELECT @@transaction_isolation thì màn hình cũng hiện REPEATABLE-READ.
Chạy thử Read Committed và Repeatable Read trên PostgreSQL
Ở mức mặc định Read Committed của PostgreSQL, phiên A đọc stock của sản phẩm id 1 ra 10, rồi phiên B UPDATE thành 7 và tự commit. Khi phiên A đọc lại ngay trong transaction đó thì thấy 7, tức đã gặp non-repeatable read.
-- Session A
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT stock FROM products WHERE id = 1; -- 10
-- Session B (autocommit)
UPDATE products SET stock = 7 WHERE id = 1;
-- Session A, still in the same transaction
SELECT stock FROM products WHERE id = 1; -- 7
COMMIT;
Mình chuyển phiên A sang mức Repeatable Read rồi lặp lại. Lần này phiên A đọc lại vẫn thấy 10 vì nó chỉ nhìn vào snapshot cố định chụp từ câu query đầu tiên, mãi tới khi COMMIT xong rồi đọc mới thấy 7.
-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT stock FROM products WHERE id = 1; -- 10
-- Session B (autocommit)
UPDATE products SET stock = 7 WHERE id = 1;
-- Session A
SELECT stock FROM products WHERE id = 1; -- still 10
COMMIT;
SELECT stock FROM products WHERE id = 1; -- 7
Với lỗi phantom read, phiên A đếm count(*) các dòng stock > 0 ra 2, sau đó phiên B INSERT thêm nến quế có stock bằng 4. Lúc đếm lại, phiên A ở mức Read Committed nhảy lên 3, còn ở mức Repeatable Read trên PostgreSQL vẫn giữ nguyên 2.
-- Session A
BEGIN ISOLATION LEVEL READ COMMITTED; -- second run: REPEATABLE READ
SELECT count(*) FROM products WHERE stock > 0; -- 2
-- Session B (autocommit)
INSERT INTO products VALUES (3, 'nen que', 4);
-- Session A
SELECT count(*) FROM products WHERE stock > 0; -- Read Committed: 3, Repeatable Read: 2
COMMIT;
Dirty read trên MySQL và MVCC trong PostgreSQL
Mình cho phiên B mở BEGIN rồi UPDATE cột stock về 0 nhưng chưa commit. Trên PostgreSQL, dù phiên A hạ xuống READ UNCOMMITTED thì đọc ra vẫn là 10; còn trên MySQL, phiên A đặt READ UNCOMMITTED đọc trúng ngay số 0 chưa lưu, và sau khi B gọi ROLLBACK thì A đọc lại mới về 10.
-- MySQL, session B
BEGIN;
UPDATE products SET stock = 0 WHERE id = 1; -- not committed yet
-- MySQL, session A
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
SELECT stock FROM products WHERE id = 1; -- 0 (reads the uncommitted value)
-- Session B
ROLLBACK;
-- Session A
SELECT stock FROM products WHERE id = 1; -- 10
COMMIT;
Nếu backend dựa vào số 0 đó để trả response báo hết hàng thì khách sẽ nhận thông tin sai. PostgreSQL không bị dirty read nhờ kiến trúc MVCC (multiversion concurrency control - kiểm soát đồng thời đa phiên bản).
Khi chạy UPDATE, database tạo một phiên bản mới cho row thay vì ghi đè lên chỗ cũ. Mỗi transaction chỉ đọc phiên bản khớp với snapshot của nó nên đọc không chờ ghi và ghi không chờ đọc. Cho Read Uncommitted chạy như Read Committed cũng là cách hợp lý duy nhất để ánh xạ chuẩn SQL vào MVCC của PostgreSQL.
Cơ chế này thể hiện qua cột hệ thống xmin lưu mã transaction tạo ra phiên bản row trên PostgreSQL. Ban đầu row id 1 có xmin là 769 kèm stock 10; sau khi UPDATE hạ stock xuống 8 thì xmin đổi thành 770, còn bản cũ sẽ được VACUUM dọn sau.
-- your xmin values will differ; just notice that xmin changes after UPDATE
SELECT xmin, xmax, id, stock FROM products WHERE id = 1;
-- xmin | xmax | id | stock
-- 769 | 0 | 1 | 10
UPDATE products SET stock = 8 WHERE id = 1;
SELECT xmin, xmax, id, stock FROM products WHERE id = 1;
-- xmin | xmax | id | stock
-- 770 | 0 | 1 | 8
Lost update: lỗi Read Committed không tự chặn
Mức mặc định Read Committed vẫn để lọt lỗi lost update (mất cập nhật) khi trừ kho. Phiên A và B cùng mở BEGIN, cùng đọc stock ra 10, rồi code tự lấy 10 trừ 1 và cả hai lần lượt chạy UPDATE ghi stock = 9 rồi COMMIT.
-- Session A
BEGIN;
SELECT stock FROM products WHERE id = 1; -- 10, the app computes 10 - 1 = 9
-- Session B
BEGIN;
SELECT stock FROM products WHERE id = 1; -- 10, the app also computes 10 - 1 = 9
-- Session A
UPDATE products SET stock = 9 WHERE id = 1;
COMMIT;
-- Session B
UPDATE products SET stock = 9 WHERE id = 1;
COMMIT;
SELECT stock FROM products WHERE id = 1; -- 9, even though 2 jars were sold
Kho nến lúc này còn 9 hũ dù đã bán 2 hũ và đúng ra phải còn 8, mà database không báo lỗi nào. Để chặn lỗi ghi đè này, bạn có ba cách:
- Giao phép trừ cho database kèm điều kiện chặn âm bằng câu
UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock > 0, mình chạy lại hai phiên như trên thì kho ra đúng 8. - Dùng
SELECT ... FOR UPDATElúc đọc để khóa row đó lại, bắt transaction đến sau phải chờ bên kia commit xong. - Nâng lên Repeatable Read hoặc Serializable và viết thêm logic chạy lại khi database báo lỗi đụng độ.
-- Option 1: let the database do the subtraction, run in both sessions
UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock > 0;
-- After both sessions commit: stock = 8
-- Option 2: lock the row on read, the other session waits until this one commits
BEGIN;
SELECT stock FROM products WHERE id = 1 FOR UPDATE;
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;
Repeatable Read trên PostgreSQL và MySQL xử lý update khác nhau
Khi hai transaction cùng sửa một row ở mức Repeatable Read, hai hệ quản trị xử lý rất khác nhau. Trên PostgreSQL, phiên A đọc thấy 10, phiên B UPDATE stock = stock - 1 và commit còn 9; lúc A chạy tiếp UPDATE stock = stock - 1 thì bị chặn với lỗi could not serialize access due to concurrent update mã SQLSTATE 40001, buộc A phải ROLLBACK để kho giữ ở 9.
-- PostgreSQL, session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT stock FROM products WHERE id = 1; -- 10
-- Session B (autocommit)
UPDATE products SET stock = stock - 1 WHERE id = 1; -- now 9
-- Session A
UPDATE products SET stock = stock - 1 WHERE id = 1;
-- ERROR: could not serialize access due to concurrent update (SQLSTATE 40001)
ROLLBACK;
SELECT stock FROM products WHERE id = 1; -- 9
Theo tài liệu PostgreSQL, khi app dùng Repeatable Read hoặc Serializable thì code phải có sẵn cơ chế retry (thử lại) toàn bộ transaction mỗi khi gặp mã lỗi 40001.
Chạy đúng kịch bản đó trên MySQL ở mức Repeatable Read thì không có lỗi nào xảy ra. Lệnh UPDATE stock = stock - 1 của phiên A lấy bản mới nhất đã commit là 9 để trừ tiếp ra 8, và khi A chạy SELECT lại trong cùng transaction cũng thấy luôn 8. Lý do là SELECT thường vẫn đọc snapshot từ lần đọc đầu tiên nhưng thấy được những row chính transaction đó vừa sửa.
-- MySQL, session A (default REPEATABLE READ)
BEGIN;
SELECT stock FROM products WHERE id = 1; -- 10
-- Session B (autocommit)
UPDATE products SET stock = stock - 1 WHERE id = 1; -- now 9
-- Session A
UPDATE products SET stock = stock - 1 WHERE id = 1; -- no error
SELECT stock FROM products WHERE id = 1; -- 8
COMMIT;
Để xử lý lỗi bên PostgreSQL, mình viết file retry.py bằng Python dùng psycopg 3 với hàm buy_one mở transaction Repeatable Read để đọc stock và trừ 1. Khi bắt trúng psycopg.errors.SerializationFailure, hàm in log, nghỉ một nhịp rồi thử lại tối đa 3 lần; mình cho phiên khác chen ngang ở lần đầu thì màn hình in attempt 1: 40001 conflict, retrying rồi lần hai báo stock updated, 5 left.
# retry.py: decrement stock at REPEATABLE READ, retry on error 40001
import time
import psycopg
from psycopg import errors
DSN = "host=localhost port=5432 user=postgres dbname=postgres"
def buy_one(conn, product_id, max_retries=3):
for attempt in range(1, max_retries + 1):
try:
with conn.transaction():
conn.execute("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ")
stock = conn.execute(
"SELECT stock FROM products WHERE id = %s", (product_id,)
).fetchone()[0]
if stock <= 0:
return "out of stock"
conn.execute(
"UPDATE products SET stock = %s WHERE id = %s",
(stock - 1, product_id),
)
return f"stock updated, {stock - 1} left"
except errors.SerializationFailure:
print(f"attempt {attempt}: 40001 conflict, retrying")
time.sleep(0.05 * attempt)
raise RuntimeError("too many retries")
if __name__ == "__main__":
with psycopg.connect(DSN) as conn:
print(buy_one(conn, 1))
Kết quả khi có một phiên khác trừ kho chen vào giữa lần thử đầu:
attempt 1: 40001 conflict, retrying
stock updated, 5 left
Theo mình, qua đợt chạy thử này thì ca lost update là khó phát hiện nhất vì cả hai transaction đều commit thành công, không có dòng log báo lỗi nào mà tồn kho lại sai. Chuyện MySQL và PostgreSQL hành xử khác nhau ở cùng mức Repeatable Read cũng nhắc mình phải tự chạy thử mỗi khi đổi loại database.
Chọn isolation level nào cho app của bạn
Tùy từng chỗ xử lý dữ liệu trong app mà bạn chọn mức cô lập vừa đủ:
- Phần lớn tính năng web cứ giữ mặc định Read Committed, riêng chỗ đụng tới tiền bạc hay tồn kho thì viết
UPDATEcó điều kiện hoặc thêmSELECT ... FOR UPDATE. - Trang xuất báo cáo cần chạy nhiều câu query liên tiếp và đòi số liệu khớp cùng một thời điểm thì mở Repeatable Read.
- Nghiệp vụ kiểm tra điều kiện trên nhiều row rồi mới ghi, như kiểm tra lịch trực để hai người không cùng xin nghỉ một ca, thì cân nhắc bật Serializable kèm vòng lặp retry.
- Mức Read Uncommitted gần như không có lý do để dùng trong thực tế.
Bạn có thể đổi mức cho từng giao dịch bằng câu BEGIN ISOLATION LEVEL REPEATABLE READ trên PostgreSQL, hoặc chạy SET TRANSACTION ISOLATION LEVEL ... trước BEGIN trên MySQL.
Checklist transaction và isolation level khi đưa lên production
Đặt mức cô lập trong config mới chỉ là nửa đường. Muốn dữ liệu tiền bạc và tồn kho không lệch khi tải cao, anh em rà lại code backend theo mấy điểm sau:
- Tìm chỗ đọc rồi mới ghi: Bất kỳ hàm nào
SELECTsố dư ra biến tạm, tự trừ trong code rồi mớiUPDATEngược vào bảng đều mở cửa cho lost update ở mức Read Committed. - Đẩy phép trừ xuống SQL: Đổi sang
UPDATE ... SET stock = stock - 1 WHERE id = ... AND stock > 0, hoặc khóa dòng bằngSELECT ... FOR UPDATEngay từ lúc đọc. - Bắt lỗi và retry khi nâng mức cô lập: Đã bật Repeatable Read hay Serializable trên PostgreSQL thì bắt buộc bọc vòng lặp bắt mã
40001để chạy lại cả transaction từ đầu. - Đổi mức theo từng giao dịch: Giữ nguyên cấu hình mặc định cho toàn app, chỉ bật mức cao ở đúng transaction cần nó, như trang xuất báo cáo hay chỗ kiểm tra điều kiện trên nhiều row.
- Chạy thử lại khi đổi database: Cùng mang tên Repeatable Read nhưng PostgreSQL trả về lỗi
40001khi đụng độ ghi, còn MySQL vẫn lấy bản mới nhất để trừ tiếp, nên đừng áp dụng nguyên giả định từ hệ này sang hệ kia.
Bug concurrency khó thấy vì database không báo lỗi; thay vì chỉ chạy thử một phiên trên máy mình, bạn nên mở hai terminal chạy xen kẽ hai phiên để kiểm tra dữ liệu có còn chính xác không.
Vì transaction chỉ bảo vệ dữ liệu trong một database nên khi cần gọi service bên ngoài qua outbox hay idempotency key, bạn tham khảo khóa học bên dưới tại 200Lab nhé.
Chúc các bạn COMMIT phát nào chắc phát đó, khỏi phải ROLLBACK lúc nửa đêm!