Hai đơn cùng mua sản phẩm cuối cùng: cách tránh bán quá tồn kho trong MySQL

Đọc tồn kho bằng một câu lệnh rồi trừ bằng câu lệnh khác thì hai đơn đồng thời đều thấy còn 1 sản phẩm và cùng bán nó. Trên MySQL 8.4.8 (InnoDB, mức cô lập mặc định REPEATABLE READ), 300 trên 300 vòng thử bán quá tồn khi hai phiên bắt đầu cùng lúc, tồn kho cuối vòng là -1. Một câu UPDATE ... WHERE stock >= 1 kèm kiểm ROW_COUNT() cho 0 lần bán quá trong 600 vòng cùng điều kiện.

Đã đo thêm SELECT ... FOR UPDATE (0 lần bán quá), SELECT ... FOR SHARE (0 lần bán quá nhưng deadlock ở 298 trên 300 vòng) và ràng buộc CHECK (chặn tồn kho âm, trả lỗi 3819 cho đơn thua).

Bảng và cách chạy thử trên MySQL 8.4.8

Mọi kết quả trong bài chạy trên MySQL 8.4.8, mức cô lập REPEATABLE-READ, innodb_lock_wait_timeout = 50 giây, client mysql 8.4.8 và Python 3.9.6, ngày 26/10/2026. Hai bảng viết bằng SQL mô phỏng tồn kho dùng chung của hệ thống POS (máy bán hàng tại quầy), WMS (quản lý kho) và bán hàng đa kênh, tồn kho ban đầu là 1:

CREATE TABLE products (
  id    INT PRIMARY KEY,
  stock INT NOT NULL
) ENGINE=InnoDB;

CREATE TABLE orders (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL
) ENGINE=InnoDB;

INSERT INTO products (id, stock) VALUES (1, 1);

Script mở hai tiến trình mysql riêng (hai kết nối) và cho hai luồng Python xuất phát cùng lúc qua một rào chắn, mỗi luồng chạy một giao dịch mua. Sau mỗi vòng script đọc lại tồn kho, đếm số đơn đã ghi rồi đặt tồn kho về 1. Tham số jitter làm phiên B xuất phát trễ ngẫu nhiên từ 0 đến số mili giây đã cho, để thấy cuộc đua phụ thuộc thời điểm. Mã đầy đủ nằm ở mục “Mã thử nghiệm đầy đủ” cuối bài; các đoạn SQL của từng cách nằm ngay ở mục của nó.

Độ trễ thật giữa hai kết nối trong script là cỡ mili giây trên một máy, vì vậy tỉ lệ bán quá ở bảng dưới chỉ để so sánh giữa các cách sửa, không phải tỉ lệ ngoài hệ thống thật. Mỗi vòng mất từ 1,2 đến 5,3 ms trong 10 lần chạy chính, và 300 vòng xong trong chưa đến 2 giây.

Đọc tồn kho rồi trừ ở hai bước làm hai đơn cùng thấy còn hàng

Cách viết gây lỗi là mẫu đọc, kiểm, trừ quen thuộc: đọc tồn kho, kiểm trong ứng dụng, rồi trừ. Hai phiên chạy xen kẽ như sau (cùng mẫu với ví dụ cửa hàng còn một món của bài trên HackerNoon, đọc ngày 26/10/2026):

-- phiên A và phiên B cùng chạy
START TRANSACTION;
SELECT stock FROM products WHERE id = 1;          -- cả hai đọc được 1
-- ứng dụng kiểm: stock > 0, tiếp tục
UPDATE products SET stock = stock - 1 WHERE id = 1;
INSERT INTO orders (product_id) VALUES (1);
COMMIT;

Mức cô lập không cứu được ở đây. Theo sách hướng dẫn MySQL 8.4, SELECT thường (không khoá) trong REPEATABLE READ đọc ảnh chụp lập ra từ lần đọc đầu của transaction, còn UPDATE và các lệnh khoá dùng trạng thái mới nhất của dữ liệu. Phiên B đọc 1 từ ảnh chụp cũ, chờ khoá dòng của A, rồi chạy stock = stock - 1 trên giá trị mới nhất là 0 và ra -1.

Kết quả đo ngày 26/10/2026, 300 vòng cho mỗi lần chạy, ba lần chạy cho mỗi dòng. Mỗi vòng bán quá có đúng một đơn thừa và tồn kho cuối là -1 (số vòng tồn kho âm bằng số vòng bán quá ở mọi dòng):

Ba lần chạy cùng cấu hình 0 đến 2 ms cho 32, 50 và 64 vòng bán quá, còn lần chạy ở bảng so sánh cuối bài cho 76, nên một lần chạy chỉ là một mẫu và con số dao động gấp đôi. Khoảng trễ càng lớn thì số vòng bán quá càng giảm, từ 299 đến 300 ở 0 ms xuống 6 đến 14 ở 0 đến 20 ms, nhưng ở mọi độ lệch đã đo vẫn lớn hơn 0. Vì hiếm, lỗi dễ lọt qua kiểm thử bằng một người dùng, vì cần hai đơn đến trong cùng vài mili giây.

Gộp kiểm tra và trừ vào một câu UPDATE có điều kiện

Cách sửa ngắn nhất là đưa điều kiện vào chính câu UPDATE và hỏi cơ sở dữ liệu xem có dòng nào bị đổi không. Nếu ROW_COUNT() bằng 1 thì giữ được hàng và ghi đơn, nếu bằng 0 thì báo hết hàng:

START TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock >= 1;
SELECT ROW_COUNT();   -- 1: giữ được hàng, 0: hết hàng
-- nếu 1:
INSERT INTO orders (product_id) VALUES (1);
COMMIT;
-- nếu 0: ROLLBACK và trả "hết hàng" cho người mua

Phiên đến sau chờ khoá dòng của phiên đầu, rồi đánh giá lại stock >= 1 trên giá trị đã commit là 0 và không đổi dòng nào. Đo trên 600 vòng (300 vòng ở độ lệch 0 ms, 300 vòng ở 0 đến 2 ms): 0 vòng bán quá, 0 vòng tồn kho âm, 0 lỗi. Lệnh ghi đơn chỉ chạy khi ROW_COUNT() bằng 1, còn đơn thua không chạm bảng orders.

variant=cond delay=0ms jitter=0ms rounds=300 oversold_rounds=0 negative_stock_rounds=0 errors={} ms_per_round=1.2
variant=cond delay=0ms jitter=2ms rounds=300 oversold_rounds=0 negative_stock_rounds=0 errors={} ms_per_round=3.8

Một chỗ dễ hỏng nằm ở chính ROW_COUNT(). Theo sách hướng dẫn (đọc ngày 26/10/2026), kết nối đặt cờ CLIENT_FOUND_ROWS sẽ nhận số dòng khớp WHERE thay vì số dòng bị đổi. Với stock = stock - 1 hai số này bằng nhau vì dòng nào khớp cũng bị đổi giá trị, nên ví dụ trên an toàn. Câu kiểu SET reserved = 1 WHERE reserved = 0 hay SET stock = 0 thì hai chế độ cho kết quả khác nhau (suy ra từ sách, chưa chạy với cờ này). Khi bán nhiều đơn vị một lúc, đổi cả hai chỗ: stock = stock - 3 ... AND stock >= 3.

Khoá dòng trước khi đọc bằng SELECT … FOR UPDATE

Khi giữa đọc và ghi còn logic mà một câu UPDATE không diễn tả được (chọn lô nhập trước xuất trước, kiểm hạn mức công nợ của khách), khoá dòng ngay lúc đọc. Câu FOR UPDATE khoá dòng tìm thấy như một UPDATE, phiên khác phải chờ đến khi commit hoặc rollback:

START TRANSACTION;
SELECT stock FROM products WHERE id = 1 FOR UPDATE;
-- ứng dụng kiểm stock > 0, rồi:
UPDATE products SET stock = stock - 1 WHERE id = 1;
INSERT INTO orders (product_id) VALUES (1);
COMMIT;

Đo trên 600 vòng: 0 vòng bán quá, 0 vòng tồn kho âm, 0 lỗi. Điều kiện để khoá chỉ đúng một dòng là tìm theo khoá chính, như WHERE id = 1 ở đây; sách hướng dẫn nói với điều kiện khác, InnoDB khoá cả khoảng chỉ mục đã quét bằng gap lock hoặc next-key lock (bài chưa đo trường hợp này). Với WHERE id = 1 hai phiên chỉ tranh một dòng của products, và 600 vòng không có lỗi chờ khoá nào dù ngưỡng chờ mặc định là 50 giây.

variant=update delay=0ms jitter=0ms rounds=300 oversold_rounds=0 negative_stock_rounds=0 errors={} ms_per_round=2.2
variant=update delay=0ms jitter=2ms rounds=300 oversold_rounds=0 negative_stock_rounds=0 errors={} ms_per_round=4.5

Thay FOR UPDATE bằng FOR SHARE là lỗi dễ mắc vì nghe “an toàn hơn”. Hai phiên cùng giữ khoá chia sẻ rồi cùng muốn nâng lên khoá ghi, và InnoDB phải huỷ một phiên. Sách hướng dẫn nêu đúng tình huống này cho bộ đếm: đọc cùng lúc bằng FOR SHARE thì ít nhất một phiên có thể bị deadlock khi cập nhật. Kết quả đo:

variant=forshare delay=0ms jitter=0ms rounds=300 oversold_rounds=0 negative_stock_rounds=0 errors={'err1213': 298} ms_per_round=1.4
variant=forshare delay=0ms jitter=2ms rounds=300 oversold_rounds=0 negative_stock_rounds=0 errors={'err1213': 13} ms_per_round=3.2

Ở độ lệch 0 ms, 298 trên 300 vòng có một phiên bị rollback với lỗi 1213. Phiên còn lại bán được hàng nên không bán quá, nhưng người mua của phiên bị huỷ nhận lỗi thay vì câu “hết hàng”. Ở độ lệch 0 đến 2 ms số vòng deadlock giảm từ 298 xuống 13 trên 300, nghĩa là lệch vài mili giây giảm lỗi mà không xoá được nó. Chạy riêng hai phiên bằng tay cho thấy lỗi nguyên văn:

ERROR 1213 (40001) at line 2: Deadlock found when trying to get lock; try restarting transaction

Khi tồn kho còn 2 mà hai đơn đụng nhau, phiên bị huỷ phải chạy lại để bán được đơn thứ hai, nên code dùng khoá cần vòng thử lại cho lỗi 1213 (cùng hướng với bài HackerNoon; trường hợp tồn kho 2 chưa đo). Một chi phí nữa của khoá: phiên đến sau chờ hết giao dịch của phiên đầu, thời gian chờ bằng thời gian giao dịch đó, xem số đo ở mục so sánh.

Thêm ràng buộc CHECK làm lớp chặn cuối cùng

Mọi cách trên đều dựa vào việc mọi đường code trừ tồn kho đều viết đúng. Một ràng buộc ở bảng chặn tồn kho âm kể cả khi một job nhập liệu hay một kênh bán quên điều kiện:

ALTER TABLE products
  ADD CONSTRAINT stock_not_negative CHECK (stock >= 0);

UPDATE products SET stock = stock - 1 WHERE id = 1;   -- khi stock đang là 0
-- ERROR 3819 (HY000): Check constraint 'stock_not_negative' is violated.
-- stock vẫn là 0

MySQL chỉ thực thi CHECK từ 8.0.16, theo ghi chú phát hành 8.0.16 (đọc ngày 26/10/2026); các bản trước đọc cú pháp rồi bỏ qua, nghĩa là bảng cũ có CHECK trong câu CREATE TABLE vẫn có thể ghi số âm. MariaDB bài chưa chạy, cần tự kiểm trước khi dựa vào. Điều kiện CHECK cho kết quả NULL cũng được coi là đạt, vì thế cột stock khai báo NOT NULL như ở bảng trên.

Chạy cùng code đọc-rồi-trừ ban đầu, nhưng trên bảng đã có CHECK, 600 vòng cho 0 vòng bán quá và 0 vòng tồn kho âm. Phiên thua nhận lỗi 3819 thay vì thông báo hết hàng, nên ứng dụng phải bắt lỗi này và trả “hết hàng”. Mã 3819 ở đây là tín hiệu hết hàng, không phải lỗi hệ thống, nên không nên ghi vào log lỗi như sự cố. Tỉ lệ lỗi theo độ lệch là 299 trên 300 vòng ở 0 ms và 37 trên 300 ở 0 đến 2 ms:

variant=check delay=0ms jitter=0ms rounds=300 oversold_rounds=0 negative_stock_rounds=0 errors={'err3819': 299} ms_per_round=4.2
variant=check delay=0ms jitter=2ms rounds=300 oversold_rounds=0 negative_stock_rounds=0 errors={'err3819': 37} ms_per_round=2.8

So sánh các cách và chỗ mỗi cách hỏng

Bảng dưới tổng hợp số đo của 600 vòng cho mỗi cách (300 vòng ở độ lệch 0 ms và 300 vòng ở 0 đến 2 ms), cùng một lần đo chi phí chờ:

Chi phí chờ đo bằng cách cho cả hai phiên ngủ 50 ms bên trong transaction sau khi ghi, 40 vòng, rồi so thời gian mỗi vòng. Nếu hai phiên chạy song song thì mỗi vòng cỡ 50 ms; kết quả cho thấy chúng chạy nối đuôi vì phiên sau chờ khoá của phiên trước:

variant=cond delay=50ms jitter=0ms rounds=40 oversold_rounds=0 negative_stock_rounds=0 errors={} ms_per_round=138.9
variant=update delay=50ms jitter=0ms rounds=40 oversold_rounds=0 negative_stock_rounds=0 errors={} ms_per_round=123.4

Mỗi vòng mất 138,9 ms với UPDATE có điều kiện và 123,4 ms với FOR UPDATE, gần gấp đôi 50 ms, nên cả hai cách giữ khoá dòng đến khi commit. Một lần chạy 40 vòng cho mỗi cách chưa đủ để nói cách nào chờ lâu hơn. Gọi dịch vụ thanh toán hay gửi tin nhắn bên trong transaction làm mọi đơn của cùng sản phẩm xếp hàng; gọi chúng sau COMMIT.

Nếu chỉ cần trừ số lượng, dùng UPDATE có điều kiện và giữ transaction ngắn. Nếu còn logic giữa đọc và ghi, dùng FOR UPDATE theo khoá chính và có vòng thử lại cho deadlock. Cả hai trường hợp đều thêm CHECK để một đường code quên điều kiện không làm tồn kho âm. Bài chỉ đo một dòng tồn kho với hai phiên; đơn gồm hai dòng sản phẩm trở lên và hai kênh trở lên cùng ghi chưa được đo.

Mã thử nghiệm đầy đủ

Đường dẫn /Applications/ServBay/bin/mysql trong script chỉ có trên máy thử (cài bằng ServBay); đổi sang đường dẫn client mysql của máy khác và dùng tài khoản riêng thay cho root. Chạy bằng python3 oversell_lab.py <naive|cond|update|forshare|check> <số vòng> [delay_ms] [jitter_ms], mật khẩu truyền qua biến môi trường MYSQL_PWD:

#!/usr/bin/env python3
"""Two sessions buy the last unit at the same time. Usage: oversell_lab.py <variant> <rounds> [delay_ms]"""
import os, random, re, subprocess, sys, threading, time

CMD = ["/Applications/ServBay/bin/mysql", "-h127.0.0.1", "-uroot", "--batch", "-N", "-n", "-f", "blog_lab_stock"]
ENV = dict(os.environ)  # đặt MYSQL_PWD ngoài shell, không ghi mật khẩu vào file

class Sess:
    def __init__(self):
        self.p = subprocess.Popen(CMD, stdin=subprocess.PIPE, stdout=subprocess.PIPE,
                                  stderr=subprocess.STDOUT, text=True, bufsize=1, env=ENV)
        self.n = 0
    def q(self, sql):
        """Run statements; return (output lines, error code or None)."""
        self.n += 1
        mark = "MK%d" % self.n
        self.p.stdin.write(sql + ";\nSELECT '%s';\n" % mark)
        self.p.stdin.flush()
        rows, err = [], None
        while True:
            line = self.p.stdout.readline().rstrip("\n")
            if line == mark:
                return rows, err
            m = re.match(r"ERROR (\d+)", line)
            if m: err = int(m.group(1))
            elif line: rows.append(line)

def buy(s, variant, delay):
    sleep = lambda: time.sleep(delay / 1000.0) if delay else None
    s.q("START TRANSACTION")
    if variant in ("naive", "check"):
        stock = int(s.q("SELECT stock FROM products WHERE id = 1")[0][0]); sleep()
        ok = stock > 0
        if ok:
            _, err = s.q("UPDATE products SET stock = stock - 1 WHERE id = 1")
            if err: s.q("ROLLBACK"); return "err%d" % err
    elif variant in ("update", "forshare"):
        clause = " FOR UPDATE" if variant == "update" else " FOR SHARE"
        stock = int(s.q("SELECT stock FROM products WHERE id = 1" + clause)[0][0]); sleep()
        ok = stock > 0
        if ok:
            _, err = s.q("UPDATE products SET stock = stock - 1 WHERE id = 1")
            if err: s.q("ROLLBACK"); return "err%d" % err
    elif variant == "cond":
        _, err = s.q("UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock >= 1")
        if err: s.q("ROLLBACK"); return "err%d" % err
        ok = s.q("SELECT ROW_COUNT()")[0][0] == "1"; sleep()
    else:
        raise SystemExit("unknown variant")
    if not ok:
        s.q("ROLLBACK"); return "soldout"
    s.q("INSERT INTO orders (product_id) VALUES (1)")
    s.q("COMMIT")
    return "sold"

def main():
    variant, rounds = sys.argv[1], int(sys.argv[2])
    delay = int(sys.argv[3]) if len(sys.argv) > 3 else 0
    jitter = int(sys.argv[4]) if len(sys.argv) > 4 else 0   # session b starts 0..jitter ms later
    admin, a, b = Sess(), Sess(), Sess()
    if variant == "check":
        admin.q("ALTER TABLE products ADD CONSTRAINT stock_not_negative CHECK (stock >= 0)")
    stats = {"rounds": rounds, "oversold": 0, "negative": 0, "errors": {}, "sold_total": 0}
    t0 = time.time()
    for _ in range(rounds):
        admin.q("UPDATE products SET stock = 1 WHERE id = 1; DELETE FROM orders")
        bar, res = threading.Barrier(2), {}
        def run(name, s):
            bar.wait()
            if name == "b" and jitter: time.sleep(random.uniform(0, jitter) / 1000.0)
            res[name] = buy(s, variant, delay)
        th = [threading.Thread(target=run, args=(n, s)) for n, s in (("a", a), ("b", b))]
        [t.start() for t in th]; [t.join() for t in th]
        stock = int(admin.q("SELECT stock FROM products WHERE id = 1")[0][0])
        orders = int(admin.q("SELECT COUNT(*) FROM orders")[0][0])
        stats["sold_total"] += orders
        if orders > 1: stats["oversold"] += 1
        if stock < 0: stats["negative"] += 1
        for r in res.values():
            if r.startswith("err"): stats["errors"][r] = stats["errors"].get(r, 0) + 1
    ms = (time.time() - t0) / rounds * 1000
    print("variant=%s delay=%dms jitter=%dms rounds=%d oversold_rounds=%d negative_stock_rounds=%d errors=%s ms_per_round=%.1f" %
          (variant, delay, jitter, rounds, stats["oversold"], stats["negative"], stats["errors"] or "{}", ms))
    if variant == "check":
        admin.q("ALTER TABLE products DROP CHECK stock_not_negative")

main()

Hỏi đáp

Đổi mức cô lập sang SERIALIZABLE có hết lỗi này không?

Bài chưa đo. Sách hướng dẫn MySQL, đọc ngày 26/10/2026, nói SERIALIZABLE chặt hơn REPEATABLE READ và dùng chủ yếu cho các tình huống chuyên biệt; trong khi đó cách sửa bằng UPDATE có điều kiện chạy được ở mức mặc định mà không cần đổi cấu hình cho cả hệ thống.

UPDATE có điều kiện có cần transaction không?

Một câu UPDATE tự khoá dòng nó sửa, nên việc trừ đúng không cần transaction bao ngoài. Transaction cần khi phải ghi đơn cùng lúc với việc trừ, để hai việc cùng thành công hoặc cùng huỷ; bài chỉ đo trong dạng transaction này.

Vì sao ROW_COUNT() trả 0 dù câu UPDATE chạy không lỗi?

Không dòng nào khớp stock >= 1, tức là hết hàng, và đó là tín hiệu cần để trả lời người mua. Câu lệnh vẫn thành công, chỉ là không đổi dòng nào.

Gặp lỗi 1213 deadlock thì làm gì?

InnoDB đã rollback transaction bị chọn, nên chạy lại từ START TRANSACTION với số lần giới hạn. Bài đo chỉ cho thấy lỗi xảy ra khi dùng FOR SHARE; chuyển sang FOR UPDATE hoặc UPDATE có điều kiện loại bỏ nguyên nhân này trong thử nghiệm.

Số liệu 300 trên 300 có phải tỉ lệ ngoài thực tế không?

Không. Hai phiên của script xuất phát cùng lúc qua rào chắn để cuộc đua xảy ra tối đa, và bảng độ lệch ở trên cho thấy tỉ lệ rơi từ 299 đến 300 xuống 6 đến 14 trên 300 khi phiên B trễ ngẫu nhiên đến 20 ms. Mỗi độ lệch đã đo, kể cả 20 ms, vẫn còn vòng bán quá. Ngoài hệ thống thật, tỉ lệ phụ thuộc khoảng cách giữa hai đơn, và bài không đo con số đó.

Hệ thống bán hàng đa kênh dùng chung một kho cần luật trừ tồn này ở từng kênh. MONA mô tả các phân hệ trong bài phần mềm quản lý bán hàng omnichannel, hotline 1900 636 648.