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
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
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
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
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
In order to avoid races you need to insert reservation and decrement availability atomically. Your proposed approach is not atomic. For it to be atomic you will need to lock whole range, to make sure no new rows appeared between the points “check for availability” and “record reservation”. Actors will be effectively competing for the single aggregate row. This is the same as having a single inventory row with quantity field, which they rejected in the beginning of the article
> 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 ?
In the proposed schema nobody is waiting for these locks, they’re skipped by concurrent queries. In your schema actors would have to wait before they can insert without breaking invariants.
Still I think their solution is a bit weird. I'd want to commit the reservation transaction with inventory decrement along with a payment key and then use a different transaction to drop the reservation when the transaction completes. If the transaction does not complete in a timely manner you probably need to query external systems anyway to resolve whether the payment actually occurred or not.
They talk about lock contention in this case, but I also wonder about latch contention since these rows are adjacent. If it's a small transaction that's not interactive, does mysql resolve it with just the latches on the needed tables?
That’s a bold overconfident statement. Cart abandonment is real. People never clear their carts they just walk away
Shopify purposefully chooses to do it at payment time because doing it earlier results in lost sales as people “reserve” items and then walk away causing other to see out of stock and then also walk away
Whoever puts up the money first gets the item
That’s the design constraint they chose you can’t just say “their solution is wrong because they solved the wrong problem”. Each design is a different user experience and I think it’s safe to say they chose which experience they want consciously.
Ok, let's accept the design goal that whoever paid first wins. You can use the same metric (how many milliseconds ago did user click PAY) and impose a global monotonic non-decreasing counter to distribute the scarce inventory. This is how order matching engines work at stock exchanges with HFT orders (FIFO logic).
the goal is to know with 100% certainty, before sending payment request to payment processor, who will have item and who won't, and you dont need to move mountains of rows for that.
the payment processor should be just a binary answer: payment succeeded or not, but currently it combines Inventory availability check & payment processing, which is the root cause of confusion. For clarity it is better to make that stage of order processing an explicit separage stage, instead of coupling it with payment stage.
some stores split payment into two stages: Payment and Final order confirmation. at the Payment stage you can pre-authorize money at cc and do inventory availability, and at final confirmation you capture $$
https://docs.stripe.com/payments/place-a-hold-on-a-payment-m...
https://support.authorize.net/knowledgebase/Knowledgearticle...
Looking at your solution, if i understand it, is instead of decreasing the inventory count for each sku as orders are processed, you are comparing the current warehouse quantity against the sum of all carts to see if there's availbale quantity.
You'll have to also include the sum of all completed orders so far.
Honestly seems almost worse? Arn't you trading contention on a single counter (inventory) for a large read across all pending and completed orders? Even indexed you're ingesting a ton more data? And you'll still need a lock here as you have to ensure two orders do this check at the same time.
Naive design
- Single inventory row per warehouse sku - All orders compete on a lock for all inventory sku rows in their order to deduct/claim their items
Shopify design
- Unroll warehouse inventory to thousands of rows per sku - Order processing races to find sufficient unlocked rows for all items in order - If insufficient rows are found then orders block behind slower "restock" process that creates more rows
Your design
- Warehouse inventory row is static/read-only (restocking out of scope for now that's fine). - Order processing computes the sum of all completed orders to ensure there is sufficient quantity - This would have to be under a lock as well, otherwise two or more racing orders will think there is quantity left.
So sounds like in your solution, you still have a single point of contention for who is computing the sum of completed orders, and while holding that lock you are doing a sum of all completed orders for each sku in your order. That sounds... worse?
You reserve the product by creating an "active_cart" entry. Your solution has a problem, that when you run the check, it might say the product is available, but before you create an "active_cart" to reserve it from thread A, another thread B reserves it and you end up reserving a product that is not available anymore. You end up with SUM(active_cart.quantity) > inventory.available_units.
That is exactly why the database has locks - to prevent this situation. With locks, thread A decrements inventory.available_units and that row is locked until the end of transaction. Other threads (if they do SELECT FOR UPDATE instead of SELECT) cannot see the old, invalid value until thread A either commits and the value is updated or rollbacks. However, locks cause performance issues and that is why shopify uses the architecture from the article - instead of 100 users fighting for the lock on the same row with available amount, each user locks only rows with units they plan to buy.
Interestingly, MySQL docs has the documentation page with a similar case: https://dev.mysql.com/blog-archive/mysql-8-0-1-using-skip-lo...