Earlier quoted context omitted.
boot = strapped
Until now I thought it was boots-trapped. I am not a native speaker :)
The idiom is to "pull oneself up by one's boot straps":
We replaced Redis with MySQL for inventory reservations and it scaled
111–120 of 281 posts
Re: We replaced Redis with MySQL for inventory reservations and it scaled
#112> 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…
I would call this one-row-per-contract-type, and this is the most general model for the problem (e.g. the model cannot be further broken down into finer level), thus, the most scalable model given storage is dirt cheap.
Storage might be cheap but search and RAM isn't
Re: We replaced Redis with MySQL for inventory reservations and it scaled
#113Re: We replaced Redis with MySQL for inventory reservations and it scaled
#114Earlier 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…
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…
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.
Re: We replaced Redis with MySQL for inventory reservations and it scaled
#115Earlier 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…
> 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.
Re: We replaced Redis with MySQL for inventory reservations and it scaled
#116Could not they shard the inventory table by shop_id? As I understand, the order includes only items from one store, so there is no need to keep all the stores in a single table.
Also, I wonder why they could not have a row status (available/reserved) and UPDATE it instead of deleting the rows.
Re: We replaced Redis with MySQL for inventory reservations and it scaled
#117Is it really the right choice to drop Redis and go back to a disk based relational database just to wrap transactions into a single unit?
Redis handles tens of thousands of concurrent connections in a single event loop, while MySQL uses one thread per connection. No matter how I look at it, that seems like a step backward.
Of course, performance isn't everything. And if performance isn't a problem, having everything in one place does make it easier to reason about. But I'm worried that under spike traffic, this approach might actually cause more problems.
I think putting a scheduling layer in front of the DB would be a better approach. The application server could handle concurrent connections and only write to MySQL when correctness is actually needed. That seems like a cheaper way to do it. but is it different for large-scale enterprise distributed systems?
Re: We replaced Redis with MySQL for inventory reservations and it scaled
#118so 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…
Example of their culture: https://x.com/tobi/status/1909251946235437514
holy yikes
Re: We replaced Redis with MySQL for inventory reservations and it scaled
#119> 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…
you should, their design is not the best. There is middle ground between "one row per SKU" and "1000 rows per SKU". Its called one row per shopping cart*SKU combo. if two people order 100 and 500 items of the same SKU, respectively, the table should have only two rows: for order1 and order2. Not 600 rows.
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.
Re: We replaced Redis with MySQL for inventory reservations and it scaled
#120> 3. Consistent lock ordering: avoiding deadlocks This section is badly written. For example, it refers to different table names than those previously introduced. The slop shows. While I appreciate the post, I wonder why they didn't bother using an LLM in a way that would at least ensure internal consistency.
What way would that be?