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.