問題反饋
一個簡單嘅投訴:「冇辦法結帳——庫存不足」。產品喺後台管理面板顯示有貨,庫存數量睇落都冇問題。但每一次嘗試落單都因為庫存校驗錯誤而失敗。
庫存預留嘅運作原理
呢套系統使用兩階段嘅庫存模型:
- available_quantity —— 手頭持有嘅總庫存
- reserved_quantity —— 被活躍訂單佔用嘅數量
- purchasable(可購買數量) =
available_quantity - reserved_quantity
顧客一落單,reserved_quantity就會即刻增加。只有喺訂單被取消或履約(發貨)時先會減少。呢個係刻意嘅設計,可以防止多個顧客同時結帳時出現超賣。
結帳時嘅校驗好簡單:
const purchasable = stock.available_quantity - stock.reserved_quantity;
return purchasable >= quantity;一旦purchasable歸零,就算實體庫存就擺喺倉庫入面,都冇人買得到。
根本原因:預留洩漏
查詢數據庫即刻就暴露咗問題:
SELECT v.variant_name, s.available_quantity, s.reserved_quantity,
(s.available_quantity - s.reserved_quantity) AS purchasable
FROM inventory_stocks s
JOIN product_variants v ON v.id = s.variant_id
WHERE (s.available_quantity - s.reserved_quantity) <= 0;有幾個規格變體儘管有實體庫存,purchasable卻係0。已預留數量同可用數量完全持平,甚至超出。
深入查邊啲訂單佔用咗呢啲預留庫存,揭露咗元兇:19個狀態為pending、payment_status = 'awaiting_payment'嘅訂單,有啲甚至超過15日。呢啲都係顧客打開咗支付網關頁面、卻從未完成支付嘅被放棄結帳訂單。
系統冇令佢哋過期嘅機制,所以呢啲庫存預留就永遠被鎖住。
點解庫存從未被釋放
releaseStock()函數係存在嘅,而且調用得都冇問題,但只存在於一個地方:明確嘅訂單取消流程。嗰個流程需要用戶(管理員或顧客)主動去取消訂單。
對於被放棄嘅支付嚟講:
- 顧客關咗瀏覽器分頁
- 支付網關悄無聲息咁逾時咗
- 冇webhook被觸發(冇支付=冇webhook)
- 冇任何用戶撳過「取消」
- 訂單永遠停留喺
pending + awaiting_payment - 預留庫存:永久鎖死
呢個係一個典型嘅預留洩漏——一次兩階段提交,而第二階段(釋放)取決於一個可能永遠唔會發生嘅用戶操作。
修復方案:兩個部分
第一部分:即刻數據修復
已經洩漏嘅預留需要直接喺數據庫入面修正:
BEGIN;
-- Release reserved stock from abandoned orders
UPDATE inventory_stocks s
SET reserved_quantity = GREATEST(0, s.reserved_quantity - sub.qty_to_release)
FROM (
SELECT oi.variant_id, SUM(oi.quantity) AS qty_to_release
FROM sales_orders o
JOIN sales_order_items oi ON oi.order_id = o.id
WHERE o.status = 'pending'
AND o.payment_status = 'awaiting_payment'
AND o.created_at < NOW() - INTERVAL '24 hours'
AND o.deleted_at IS NULL
AND oi.item_type = 'product'
GROUP BY oi.variant_id
) sub
WHERE s.variant_id = sub.variant_id;
-- Cancel the abandoned orders
UPDATE sales_orders
SET
status = 'cancelled',
cancellation_reason = 'Payment not completed within 24 hours',
cancelled_at = NOW()
WHERE status = 'pending'
AND payment_status = 'awaiting_payment'
AND created_at < NOW() - INTERVAL '24 hours'
AND deleted_at IS NULL;
COMMIT;留意GREATEST(0, ...)呢個保護措施——佢可以防止喺數據已經唔一致嘅情況下,reserved_quantity變成負數。
第二部分:永久修復,自動過期排程器
現有項目入面已經有一個NestJS排程器服務,用嚟自動完成已送達嘅訂單。自動過期任務就跟住佢一齊加咗入去:
@Cron(CronExpression.EVERY_HOUR)
async autoExpireAbandonedPayments(): Promise<void> {
const cutoffDate = new Date();
cutoffDate.setHours(cutoffDate.getHours() - 24);
// payment_status isn't mapped on the TypeORM entity, so raw query is needed
const expiredOrders = await this.orderRepository.query(
`SELECT id, order_number FROM sales_orders
WHERE status = $1
AND payment_status = 'awaiting_payment'
AND created_at < $2
AND deleted_at IS NULL`,
[OrderStatusEnum.PENDING, cutoffDate],
);
for (const order of expiredOrders) {
const items = await this.orderItemRepository.find({
where: { order: { id: order.id } },
});
// Release reserved stock before cancelling
for (const item of items) {
if (item.item_type === CartItemTypeEnum.PRODUCT && item.variant_id) {
await this.inventoryStocksService.releaseStock(
item.variant_id,
item.quantity,
null, // system-initiated, no causer user
);
}
}
await this.orderRepository.update(order.id, {
status: OrderStatusEnum.CANCELLED,
cancellation_reason: 'Payment not completed within 24 hours',
cancelled_at: new Date(),
});
await this.orderTrackingService.createEvent(
order.id,
OrderEventTypeEnum.CANCELLED,
'Order auto-cancelled: payment not completed within 24 hours',
);
}
}一個關鍵嘅坑:未映射嘅欄
payment_status呢一欄喺數據庫入面係存在嘅,但喺TypeORM實體上並冇以@Column()嘅形式映射。TypeORM嘅find()同findOne()只可以對已映射嘅欄位做過濾。對於未映射嘅欄,透過repository.query()做原始查詢係一條退路,唔需要為咗一個排程任務嘅查詢專門去修改實體。
需要留意嘅模式
任何喺結帳時增加計數、卻只喺明確用戶操作時先減少計數嘅系統,都存在呢種脆弱性。修復方案嘅形態總係一樣:
- 搵出預留洩漏(佔住庫存超過其有效期嘅訂單)
- 寫一條帶安全防護(
GREATEST(0, ...))嘅一次性修正查詢 - 加一個定時任務,今後自動運行同樣嘅邏輯
- 喺改變訂單狀態之前釋放資源,而唔係之後
最後一點好關鍵:如果庫存釋放喺循環中途失敗,訂單會保持喺pending狀態,下一次每小時執行時會重新嘗試。但如果你先取消訂單、後釋放庫存,一旦呢兩步之間出現失敗,就會留低一個已取消、但庫存仍被預留嘅訂單。