US is running a more than a Trillion dollar trade deficit, what do you think happens when other countries will demand something real, like Gold, instead of freshly printed dollar?
does US have a trillion dollar worth of Gold reserves every year?
You’re confusing trade deficits with budget deficits. These are not the same thing. Bond sales pay for budget deficits. They have no bearing (heh) whatsoever on trade deficits.
You can end a budget deficit by simply not spending as much from the public purse. You cannot end a trade deficit without restricting freedom of trade.
Not so. We don’t have the capital investment or labor pool needed to replace China as a source of many goods, especially high tech goods, and it would take a very, very long time to catch up at this point, if we ever could.
The problem is that in this case you have to do splits/merges. And while there are products that are sold by 100 units at a time, I think in most cases people by 1-2 items so the hassle might be not worth it.
Also you might not understand the original problem. Imagine if 100 customers want to buy product A. One thread starts a transaction, searches for amount of product A and UPDATE's it and goes searching for other products. The database locks the row until the end of transaction and other 99 treads cannot continue until first transaction commits (they can read but cannot update the rows).
This is why they made a row per item. In this case, transaction 1 hopefully locks only several rows with items of product A. Transaction 2 instead of waiting for lock release skips them (due to SKIP LOCK) and locks several next rows. And so on.
Obviously you do not need to make a row per item - if the available amount is really large (10 000 items), you could have for example 100 rows having 100 items each. In this case each transaction locks the whole row (100 items) even if it wants to reserve just one item. The problem though is that now every row might have different amount of available items and you have to do more work to reserve the amount you want.
ok, lets model situation of 100 customers and one last remaining item. Who will get the last item?
in shopify's design, it is a user who was the first to lock the row and have successful payment. Sounds good, but how often does it happen ? It's a rare and extreme case and they model their entire system after the rare even, and incur the overhead of 1000 rows per SKU per shop for all combination of SKU and shop_id for all the normal items that are not sold out in flash sale.
the same outcome could be achieved without locking and without creating 1000 rows:
1. keep track of all active carts at the checkout in a table
2. for each cart, record the timestamp in nanoseconds when user clicked Pay (but I would prefer timestamp of clicking Checkout)
3. that timestamp will decide who gets the last available item.
4. in a shopping cart, have explicit field for each SKU: inventory_reserved.
5. this decision mechanism is now explicit via global monotonic non-decreasing counter. It is no longer tied to payment processing gateway timeouts, not opaque and implicit mechanism relying on database internals and quirks of how DB engine locks and releases some placeholder rows.
On a large scale "rare" events happen every day. That is why people use locks and transactions or other measures.
In case with shopify, they want to decide whether the user may place order or not, at the moment when the user clicks "Pay" or some other button. If the user cannot place an order, they are shown the error, if they can, the items are reserved and the user is redirected to the payment page. So payment is processed only after successful reservation, and reservation is made only if the user wants to pay. The similar system works for buying train tickets online in my country, for example.
In you case, when user A clicks a button, following happens (as I understand):
1 the server increments the counter
2 the server calculates available amount as (amount_in_stock - amount reserved by carts with time < counter)
3 if the amount is large enough, the server updates the "time" field for user's cart thus reserving the item
Imagine that at step 2 the user A sees that there is one item left. However before user A does step 3, another user B might reserve the item (complete all 3 steps), and proceed to the payment. Then user A then completes step 3 and proceeds to the payment too. Now we end up with both user A and B paying for the last remaining item which doesn't solve the stated problem. Shopify's solution doesn't have such issues.
This is a classical TOCTTOU situation. There were exploits against Linux kernel based on similar issues.
shopify is wrapping their entire dance with locking and moving rows inside a transaction. if you wrap step 1-3 inside transaction you will get same atomicity guarantee
but again, my idea was:
1) do not use throwaway placeholder rows to imitate a single item
2) do not rely on db engine to decide which transaction gets committed first (which customer gets the last item)
3) model queue explicitly by introducing counter field that sorts and prioritizes customers' orders and decides which order gets fulfilled and which customers gets the last item
Can you explain how that works? With the row-per-item I can see how you’d use locking primitives etc easily to deal with multiple concurrent shopping carts claiming available inventory.. but how does your solution solve contention? There’d need to be some “number of items in inventory” row, wouldn’t there be contention on that?
The point of one row per item is that thousands of concurrent shoppers don’t need to block each other as they can each claim as many free rows as they need for themselves?
One other advantage is item serial numbers. Or something else that makes an item that seems the same but actually be unique (perhaps the warehouse it’s in?)
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.
So those engineers at Shopify worked hard for months on a more performant system, but they missed the obvious structure? They chose a complex denormalization for no good reason?
It may be true, but I think it's presumptuous to belittle their work when we have only partial information. My guess is that they had good reasons to think that the more obvious ways would not scale.
And from reading your comments in this thread, I believe your structure would fail at their scale. A SQL query that uses 2 sub-queries with "group by" is probably too heavy. From the post, at peaks there would be millions of active shopping carts.
BTW, I suspect most orders are just for 1 or 2 of each item, so the denormalization is not as heavy as it seems.
i also work in big tech and know that a lot of bullshit design creeps into system design and prod, because everyone is overworked, overstressed, wants to just get things done for the quarterly performance review as to not get shitcanned with severance
re concurrency, it is not a big issue at all. stock exchanges deal with HFT traders and can easily deal with concurrency of orders. Same can be implemented with shopify, but I doubt they face the same level of concurrency as stock exchange anywhere near
Famously, stock is settled on a delay (and generally doesnt involve physical products that are not fungible). Im sure theres a lot to glean from how they handle concurrency but Im not sure they are solving the same problems.
settlement is a different process, what exchanges are doing is they match Buy and Sell orders.
you have an open Sell 1 APPL for $100.0. Millions of other HFT orders rush to scalp your single order. How do you think exchange matches your Sell to HFT's Buy orders? which Buy order gets fulfilled first?
If anything this article shows that concurrency is a big issue. It is such a big issue you have to write in-memory single threaded processor with custom journaling. If you have established workflows with MySQL and a team knowing how to work with it, throwing all that to do LMAX is not cost efficient. While there are domains where such approach is suitable and even required due to strict transaction ordering, Shopify case doesn’t look like one of them.
You might not have noticed that essentially the entire blog post was AI written.
There's even this bit where they discover a remarkable trick:
> Each round trip to the database has a cost. For carts with multiple line items, we batch reservation queries using UNION ALL so we fetch all needed units in one round trip
Insights like that really don't read like senior level output, and of course, it's LLM output. I'm not sure it's presumptuous to question it.
There is now new type of comment in HN, if you disagree with article you attack the fact that ai was used in post editorial process. People here now dismiss anything that have em dash.
You make a lot of assumptions here... that I disagree with something about the article (aside from with its quality) and post rationalized somehow, that it has anything to do with em-dashes, that they just used AI for "post editorial process". None of that is true.
What is there to disagree with? It's not making a statement or taking a stance, it's a technical writeup and I included a technical quote from the article which would be weird whether or not it was AI written.
It's not post edited by AI, it's written whole cloth by AI. No human cared enough to write it. Possibly if it was better quality you could make the case that's ok, but I suspect that you didn't read the article at all.
Your mental model here is mapping too close to an actual cart in a retail, at a in person, setting.
The assumption that a SKU maps 1 to 1 to a cart item is flawed.
If the first item in the cart is a bundle of SKU-A and SKU-B, the second item is a bundle of SKU-A and SKU-C and the third item is 5xSKU-B where do you do you keep the re-agregation of the SKU-X's to track them?
This is without accounting for item location in the reservation - and rules that may apply around that.
You haven't even gotten to the part where different customers will have different rules around shipping from different locations - because that can eat into margins.
You're also making a bunch of other assumptions around transaction flow and where carts are actually stored (and how they get converted to an invoice, with payment attached) that likely do not hold true.
Could you do it more like what you're sugesting -- maybe -- but only in a single tenant system.
Modeling after physical shopping process is actually the proper way to design a eshop system.
Imagine you are at Walmart store and go to checkout stage, you would have to pick item from the shelf and take it from availability for other shoppers, before you pay for the item.
What shopify did, is customer enters the store, heads straight to checkout and retail workers races back to shelves to pick up items for client. Sometimes it says: sorry bud, item is sold out, frustrating customer experience, who is already mentally prepared to pay and own an item.
Re SKU storage, you will have multiple row entries per SKU, if I order two items, there will be two rows corresponding to the items in your Purchase order.
The sum() aggregation check will run across all active orders per sku
Re single tenant: shopify creates 1000 rows per SKU per shop(tenant!!). As long as tenant is on the same DB you can run it, just add shop_id to the group by field
If Walmart (in person) behaved like Walmart (on line) the isles would be so littered with half full carts and items that others could not buy you would be unable to move in the store.
70 percent of carts are abandoned. You dont want your inventory sitting in carts, when other people want to buy it. It only comes off the shelf (and gets put in a box) when you have money in your hand.
> Sometimes it says: sorry bud, item is sold out, frustrating customer experience, who is already mentally prepared to pay and own an item.
This is better than
A) taking their money and then telling them you dont have it.
B) Them not being able to buy it because someone has it in their cart and is NEVER going to check out with it.
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.
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
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
The item is reserved when the user decides to place an order, but before paying for it. Not when a product is added to the cart because the user can keep it there for a month and end up not buying.
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.
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?
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
> Shopify incorrectly formulated the very problem they are trying to solve.
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.
that's why I mentioned active carts in my post, there are ways to define active cart to get rid of abandoned carts ( ignore carts where last user action was > N seconds ago).
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 $$
i dont know about the world, by authorize.net and Stripe, which work globally and work with global credit cards, they do support separate authorize and separate capture, which seems to be part of PCI standard
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?
Clearly this is for high concurrency cases where there are many people racing to get all the available items. It's not clear that it's in shopifys or the sellers interest to let items get sequestered in people's shopping carts, which is a spot where there isn't a strong commitment to complete the purchase. At payment time, you can be more assured that the item will actually be purchased.
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?
I was curious about what Tiger Beetle does. It has two phase transfers, which appears tailor built to handle this case. But maybe Tiger Beetle isn't the right database to track all your product stocks.
> 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)
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
> 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:
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.
reply