81–90 of 281 posts
so this is interesting to me, im in retail i work closely with platforms ive used shopify ive used magento ive used smaller players ive helped implement various pieces of all of them. and i was excited to get some insight, then i realized that this whole thing was written by AI and im going to guess the idea and implementation were probably very AI driven. > The solution: SKIP LOCKED > Core idea: one row per unit, bo…
Earlier quoted context omitted.
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 ( sel…
Earlier quoted context omitted.
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 th…
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.
Shopify’s founder and their coo both fund far-right extremism, and its founder thinks only rich people should be able to vote. But anyway, they switched databases. https://www.techwontsave.us/episode/340_shopifys_leaders_are...
The link you shared is just a podcast and does not contain even contain “far right”. Can you provide specific concerns, otherwise I don’t see anything wrong with a CEO of the most successful tech company should not be concerned about a horribly performing country from a GDP perspective.
But here's another link if you need something that includes the phrase "far right": https://pressprogress.ca/shopify-executives-right-wing-media...
Anyway, if you think a country having a low gdp per capita is how you measure if it should suspend voting rights for disabled people and stay at home parents, then I suspect you're not actually reading any of this.
> Instead of one row per item with a quantity column, we use one row per sellable unit. An item with 10 units has 10 rows. > But one row per unit for all inventory would break down at scale—an item with 50,000 units across 10 locations would mean 500,000 rows, and the reserve query would slow as it scans through them. Instead, we maintain a bounded pool of available rows, capped at 1,000 per item/location combination…
Earlier quoted context omitted.
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 th…
> 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…
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
Earlier quoted context omitted.
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 th…
> 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…
Earlier quoted context omitted.
> 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…
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 counte…
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
Earlier quoted context omitted.
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 th…
> 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 mone…
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 $$
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 10…
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.