not the best design to have 1000 rows for each shop*SKU combination. If a candidate proposed this solution during Shopify's System Design interview, i doubt he would be vetted for Senior+ position.

Instead of having 1000 rows per shop*SKU, why not just have one row per shopping cart*SKU?

That way a single row would represent a single cart, and will hold info of multiple items of the same SKU.

No need a cludge with 1000 rows limit and replenishment process. Instead of dealing with N rows, you always deal with a single row.

▲ atomicnumber3 4 minutes ago | parent | next [-]

I have never worked anywhere where describing how their system actually works would pass the company's own system design interview

▲ soontimes an hour ago | parent | prev [-]

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

At what point that row is inserted?

▲ bijowo1676 an hour ago | parent [-]

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 an hour ago | parent [-]

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 40 minutes ago | parent [-]

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

▲

soontimes 27 minutes ago | parent [-]

I don’t understand how this should prevent oversold. You have a check that reports empty or oversold inventory. But how does that check prevent 2 concurrent actors fighting for the last item from inserting 2 rows?

▲

bijowo1676 21 minutes ago | parent [-]

how does current design resolve concurrent actors fighting for the last item ?

there is ultimately needs to be some global mechanism resolving this conflict. Currently it is an order in which db engine processes transactions by locking rows for a transaction, whoever got the first lock, wins the last remaining items.

my design is the same, except it does not need this dance with moving rows between tables, locking them, and the cludge with replenishment process.

in the simplest form, run the sum() over active non-finished orders and compare to inventory. you get the same result: whoever got the first to run sum() and get positive answer will get the last remaining items.

but the problem as formulated, imho, is not even correctly defined.

Shopify incorrectly formulated the very problem they are trying to solve.

Trying to solve it at the payment time is too late, its better to resolve it earlier, before the checkout.

the "PAY" button should only do one thing: deduct money from cc and that's it. Resolving inventory availability must be solved way earlier, the moment user clicks Checkout, not when user clicks Pay.

So ideally, the error for oversold items should be shown to a user when he clicks Checkout, not when he click PAY

	▲	soontimes 4 minutes ago \| parent [-]
		> how does current design resolve concurrent actors fighting for the last item ? It resolves with skip locked. Assuming we have only 1 item left. First query scans the buffer table, locks as many rows as needed (1 in our case), and moves rows to another table. Second query scans the table, finds no rows (even if first one hasn’t finished yet, the row is locked and ignored), checks if it can increase buffer, finds out that it’s fully sold and aborts. Db guarantees that you can’t oversold. > my design is the same, except it does not need this dance with moving rows between tables, locking them, and the cludge with replenishment process. I can’t evaluate whether it’s the same or not, because you still haven’t clarified when exactly you’re going to insert the row. In the article they’re inserting in the same transaction. Would you also do it in the transaction? Because if you’ll introduce a separate global mechanism to resolve conflicts, on a high level it would be the same as their approach with redis (you need to have 2 systems) EDIT: wording