Hacker News new | past | comments | ask | show | jobs | submit
isignal | on: Shopify replaced Redis with MySQL for inventory reservations–and it scaled
It seems there could be a simpler solution.

1. Deduct the reservation from the inventory when the user starts to order, but in the same txn also maintain a separate row for the in progress order flow. 2. If the order flow is aborted or times out have a background process that returns these to the inventory.

That seems simpler than this approach and involves no locking. Though their presented approach is also reasonable, there must be some reason not to choose a simpler flow. It is not that difficult to have a gc service that scales, but may be they didn't want to separate that.

stillpointlab | parent | next
I was investigating Durable Objects (DO) and had Fable walk me through where in my app they might be appropriate. One place had a dependency with billing (where I use a transaction now) and the proposed re-work to allow for concurrent editing with DO looked very much like this, reservations with idempotency keys. And if you add hierarchical allotments then it scales pretty well.

I disagree with the other posters about the bg process, if you have any bg processing already you should be able to handle the few edge cases without too much trouble.

firasd | parent | next
My understanding is: your proposal is not very different from what Shopify is doing except they are tracking 'reserved units' (one per row) and you are proposing tracking 'orders' as the temporary state to then reconcile back with inventory quantities.
isignal | root | parent
Yes, at a high level. It doesn't rely on skip locked, which is not cheap at DB level. DB has to still typically run query and keep going until it finds an unlocked item. Deducting and checking inventory counts are simpler ops inside the DB.
treis | root | parent
This seems like what triggers are for and how we do similar type things. Update trigger on order does select for update on the inventory and increases/decreases it as appropriate.

I don't think you really need that even. An indexed lookup is fast and you don't need to store a computed quantity generally.

sandeepkd | parent | next
The moment you added a background process you just replaced the complexity.

1. Backgrounds process can back up

2. They need context of the user and need to switch context per user

3. What if they fail, you create some DLQ or another process to handle the failure

4. Who looks on those failure and how do they act

TLDR; there is always a cost

0x696C6961 | root | parent
The design in the shoppify post already had a background process for the item replenishment.
soontimes | parent | next
Can you clarify why this involves no locking? There can still be 2 actors fighting for the same row.
isignal | root | parent
Two concurrent deductions of inventory do contend but only during the actual DB update. That is just normal DB locking for SQL isolation levels. The blog refers to explicit locking by the app, which is where skip locked comes in.
soontimes | root | parent
Yes, the point is to spread contention across multiple rows. They also mention this in the beginning of the article
vxxzy | parent
now you have two problems. what happens when your reservation system backs up?