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
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.