Comment by bijowo1676

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