Comment by soontimes

3 hours ago

> Instead of having 1000 rows per shopSKU, why not just have one row per shopping cartSKU?

At what point that row is inserted?

13 comments

soontimes

Reply
bijowo1676  2 hours ago

per my reading of the article, the protection is only needed for a few seconds, while payment is being processed by the payment system.

so the row is inserted when Payment is initiated, and row is deleted when Payment succeeds

  What is oversell protection?
  Reserve: When payment starts, we mark items as reserved (a short hold, e.g. several minutes).
  Claim: When payment succeeds, we permanently deduct quantity from the inventory ledger (source of truth).

but that system could be easily improved to reserve item when user Adds item to a cart, to prevent scenario when user adds item to a cart, goes through checkout, and after initiating payment gets "soldout error":

  1. Let user add item to a cart by default (happy path)
  2. Initiate async check in the background for SKU and quantity
  2a. The check sums up rows for all SKUs and compares to Inventory table (very cheap check since its done to only active shopping carts)
  3. After few seconds the check comes back, and we let user know that item is soldout, before/the moment user goes to Checkout.

  • soontimes  2 hours ago

    Ok, but before inserting you must ensure that inventory is not depleted, which means you need to know the count and you need to lock the row. So you still have contention on that item. Them having a 1k buffer allows not to take a lock on a single row every time, and only do it when buffer is empty

    • bijowo1676  2 hours ago

      there is no need to lock the row, since you a dealing with a shopping cart, not individual item piece. when you run aggregate functions, lock is no needed, it is actually better to run it with SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; for aggregation

      the check for oversold items is extremely cheap:

        with current_order as (
    select $SKU1, $q2 as quantity
    union
    select $SKU2, $q2 as quantity
  ),
  with carts as (
    select sku, sum(quantity) as reserved
    from active_carts
    group by sku
  ),
  with warehouse as (
    select sku, available_units
    from inventory
    group by sku
  )
  select * from current_order
  inner join carts using (sku)
  inner join warehouse using (sku)
  where warehouse.available_units - carts.reserved < current_order.quantity

      assuming there are indexes on sku field in both, results in efficient index seek and agg over 2 tables

      10 replies →