Comment by soontimes

2 hours ago

> 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

3 comments

soontimes

Reply
bijowo1676  1 hour ago

think about for a moment what that skip locked actually means, all these 1000 rows per SKU are logically equivalent to a Inventory table with a single row where available_units=1000 per SKU.

now let's think again, do we need to lock 900 rows to place order on 900 items? or can we insert a single row where order_quantity=900 ?

shopify's design relies on DB to lock rows for transaction as a way to "decrement the counter" of available units. What I am suggesting, is you can just decrement counter by updating a single row, no need to lock 900 rows. Shopify moved from one extreme (single global variable in redis) to another extreme (1000 rows in db) and forgot about the middle ground.

The dance with moving rows per each item between tables is completely unnecessary, it's like counting numbers one by one in a for loop, when you can just substract number directly.

if I were to solve the problem, I would have solved it differently, at the Checkout state, before user clicks PAY. This removes the race condition at the user UI level, before any request lands in backend/db:

  1. Have a table with active shopping carts (cart_id, cart_status, sku, quantity)
  2. when cart_status changes to 'Checkout' run inventory availability check
  3. If inventory availability check fails, show error to user (before he clicks Pay) and suggest replacement items.
  4. If inventory availability succeeds, proceed to charge cc

availability check is the SQL above: inventory-sum(active_carts.quantity)-current_order must be > 0

  • pas  1 hour ago

    assuming their "reserve item" function is just "update the table set N rows to reserved=true where reserved==false"

    more transactions can commit at the same time, but with one counter they would conflict (as it did in the Redis case)

    they should use CRDT (and trying to model that with this 1000 row workspace, no?)

    still, eventually at some point they need to do the math