簡介

之前工作需要開發使用者下訂單時可以使用優惠活動的功能，但這個優惠活動有兩個限制：

有庫存量
每個使用者有使用次數限制

一張訂單使用一次優惠活動，下單時需要考慮是否還有庫存，以及使用者是否還能使用，並且考慮併發問題

DB Schema

考慮以下三張 table：

 1-- 優惠 table
 2CREATE TABLE IF NOT EXISTS discounts (
 3  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
 4  user_limit INT NOT NULL, -- 使用者次數限制
 5  inventory INT NOT NULL -- 當前庫存量
 6);
 7-- 訂單 table
 8CREATE TABLE IF NOT EXISTS orders (
 9  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
10  discount_id BIGINT, -- 優惠活動 id
11  user_id BIGINT, -- 使用者 id
12  created_at TIMESTAMP NOT NULL DEFAULT NOW()
13);
14-- 使用者-優惠關聯表，表示一個使用者用了幾次某優惠
15CREATE TABLE IF NOT EXISTS user_order_limits (
16  discount_id BIGINT, -- 優惠活動 id
17  user_id BIGINT, -- 使用者 id
18  quantity INT NOT NULL DEFAULT 0, -- 當前使用者使用此優惠的次數
19  PRIMARY KEY (discount_id, user_id)
20);

然後建立一筆 id = 1 的優惠活動，庫存 1000，每個使用者限制使用 60 次

1INSERT INTO discounts (id, user_limit, inventory) VALUES (1, 60, 1000);

接下來考慮下單
首先，因為下單時要考慮庫存限制，以及有多個更新 table 的操作，所以整個操作流程應該使用 transaction
再來需要利用 DB 的 lock 機制，防止併發時發生超賣問題。關於 DB 的 lock 機制，PostgreSQL 可以參考文件 Explicit Locking 章節，MySQL 可以參考 innoDB Locking 章節。本篇都使用 PostgreSQL

開始 transaction

1BEGIN:

再來扣庫存，當前庫存 > 0 時，庫存減 1

1UPDATE discounts SET inventory = inventory - 1 WHERE id = 優惠 id AND inventory > 0;

如果執行結果的受影響行數 = 0，表示已經沒庫存，則 ROLLBACK

然後更新使用者使用次數。這邊要考慮第一次使用，資料表需要 insert 的情況，因此需要依靠 INSERT INTO ... ON CONFLICT ... DO UPDATE ... 語句。另外，為了模擬有 20 位 user，每次 iteration 都會隨機設定 0 - 19 為使用者 id

1INSERT INTO user_order_limits (discount_id, user_id, quantity)
2  VALUES (優惠 id, 使用者 id, 1)
3  ON CONFLICT (discount_id, user_id)
4  DO UPDATE SET quantity = user_order_limits.quantity + 1
5  WHERE user_order_limits.quantity < (SELECT user_limit FROM discounts WHERE id = 優惠 id);

同樣，如果執行結果的受影響行數 = 0，表示使用者額度已滿，無法使用優惠，則 ROLLBACK

最後 insert 建立訂單，然後 commit

1INSERT INTO orders (discount_id, user_id) VALUES (優惠 id, 使用者 id);
2COMMIT;

以上的操作流程，都用 UPDATE 或 INSERT 語句，這些語句都使用 FOR UPDATE lock，可以防止其他交易在目前交易結束前鎖定、修改或刪除這些 row。如果必須使用 SELECT 語句判斷一些商業邏輯，需要注意 non-repeatable read 問題

K6 & xk6-sql

設計完 DB Schema 以及操作語句。接下來我使用 K6 及 xk6-sql 測試併發情況下是否會發生超賣問題
因為這是我第一次使用 K6，看了文件也是似懂非懂的狀態，所以以下內容可能有錯誤，請讀者海涵

K6

K6 是 Grafana 家族的成員，可以用 JS 寫腳本然後測試各種場景下服務的狀態
JS script 基本結構如下：

1export function setup() {}
2export function teardown() {}
3export default function () {
4    // 要測試的項目寫在此
5}
6export const options = {
7  // K6 配置
8};

我們可以將上述的 DB Schema 放在 setup()，下單流程放在 default function，檢查庫存是否正確和印出下單結果放在 teardown()，測試場景放在 options

測試場景 Scenario

Grafana 關於場景的文件詳述每種 executor 需要的 option 和圖表解釋執行結果，建議先閱讀後再繼續

本次測試場景較簡單，只是要測試併發的庫存狀況，因此忽略各種流量 stage 測試，executor 選擇 constant-arrival-rate

 1export const options = {
 2  scenarios: {
 3    discount_purchase_load: {
 4      executor: "constant-arrival-rate",
 5      // 每秒 300 iterations
 6      rate: 300,
 7      timeUnit: "1s",
 8      duration: "10s",
 9      // Pre-allocate 100 VUs before starting the test
10      preAllocatedVUs: 100,
11      // 最多啟動 500 個 VU 以維持規定的 arrival-rate。
12      maxVUs: 500,
13    },
14  },
15};

iteration：執行一次測試 script 稱為一個 iteration
VU：即併發數。可以當作執行測試 script 的使用者。這裡一開始設定了 100，然後 K6 會自動調整 VU 數量以達到每秒 300 iteration，最多 500 個 VU
duration：因為我的測試庫存只有 1000，又每秒 300 iteration，很快就會賣光，因此測試持續 10 秒即可。但不代表 10 秒測試就會立刻結束，預設有 30 秒 gracefulStop，讓剩餘的 VU 執行完成

xk6-sql

文件寫得簡單易懂，不知道某功能怎麼寫可以先從文件看看。這裡分享幾個常見的功能

一定要在 teardown db.close();，否則會佔用連線
exec、execWithTimeout 會回傳結果，query、queryWithTimeout 不會
回傳的結果會是 object array

1for (const row of orders) {
2    console.log(`${row.user_id}, ${row.id}`);
3}

寫好 try catch 方便檢查錯誤

1try {
2    // db.query()
3} catch (e) {
4    console.error(e);
5}

傳變數給 SQL

1db.exec('INSERT INTO orders (inventory_id, user_id) VALUES (1, $1);', userId); // PostgreSQL
2db.exec('INSERT INTO orders (inventory_id, user_id) VALUES (1, ?);', userId); // MySQL

測試結果

 1time="2026-04-04T15:22:19Z" level=info msg="初始庫存量 1000，最後庫存量 0，總單量 1000" source=console
 2time="2026-04-04T15:22:19Z" level=info msg="使用者 0，下單數 51，優惠使用次數 51" source=console
 3time="2026-04-04T15:22:19Z" level=info msg="使用者 1，下單數 56，優惠使用次數 56" source=console
 4time="2026-04-04T15:22:19Z" level=info msg="使用者 2，下單數 55，優惠使用次數 55" source=console
 5time="2026-04-04T15:22:19Z" level=info msg="使用者 3，下單數 45，優惠使用次數 45" source=console
 6time="2026-04-04T15:22:19Z" level=info msg="使用者 4，下單數 58，優惠使用次數 58" source=console
 7time="2026-04-04T15:22:19Z" level=info msg="使用者 5，下單數 46，優惠使用次數 46" source=console
 8time="2026-04-04T15:22:19Z" level=info msg="使用者 6，下單數 56，優惠使用次數 56" source=console
 9time="2026-04-04T15:22:19Z" level=info msg="使用者 7，下單數 60，優惠使用次數 60" source=console
10time="2026-04-04T15:22:19Z" level=info msg="使用者 8，下單數 46，優惠使用次數 46" source=console
11time="2026-04-04T15:22:19Z" level=info msg="使用者 9，下單數 40，優惠使用次數 40" source=console
12time="2026-04-04T15:22:19Z" level=info msg="使用者 10，下單數 47，優惠使用次數 47" source=console
13time="2026-04-04T15:22:19Z" level=info msg="使用者 11，下單數 43，優惠使用次數 43" source=console
14time="2026-04-04T15:22:19Z" level=info msg="使用者 12，下單數 60，優惠使用次數 60" source=console
15time="2026-04-04T15:22:19Z" level=info msg="使用者 13，下單數 48，優惠使用次數 48" source=console
16time="2026-04-04T15:22:19Z" level=info msg="使用者 14，下單數 52，優惠使用次數 52" source=console
17time="2026-04-04T15:22:19Z" level=info msg="使用者 15，下單數 39，優惠使用次數 39" source=console
18time="2026-04-04T15:22:19Z" level=info msg="使用者 16，下單數 40，優惠使用次數 40" source=console
19time="2026-04-04T15:22:19Z" level=info msg="使用者 17，下單數 53，優惠使用次數 53" source=console
20time="2026-04-04T15:22:19Z" level=info msg="使用者 18，下單數 45，優惠使用次數 45" source=console
21time="2026-04-04T15:22:19Z" level=info msg="使用者 19，下單數 60，優惠使用次數 60" source=console

可以看到，每個使用者下單量不超過 60，與優惠使用次數相符，總單量與最後庫存量相加也等同初始庫存量

歸還庫存

寫到這裡才發現，本篇沒提到到取消訂單、歸還使用次數和庫存的情況，筆記在此，就不用 K6 測試了歸還庫存

1UPDATE discounts SET inventory = inventory + 1 WHERE id = 優惠;

歸還使用次數

1UPDATE user_order_limits SET quantity = quantity - 1 WHERE discount_id = 優惠 id AND user_id = 使用者 id;

後記

可以參考我的 repository 範例 code。如果使用 MySQL，可以參考 mysql branch。因為只是測試，所以 DB 的連線數量開非常大。有開另一個 branch pgbouncer，讓 K6 連 pgbouncer，如果哪天研究完，會再寫一篇文章分享。

利用 xk6-sql 測試防止超賣的 DB Schema 設計

簡介