> If you want a more user-friendly guarantee, you can reserve it when it's added to the cart.
If you open an ACID transaction when the user adds something to the cart and don't close it until they check out, you'll find your database gets locked up pretty quickly. So you can't actually use the ACID transactions to implement the behaviour you want - you have to implement some kind of reserve/commit semantics in userspace, whether you're using an ACID database or not.
You don't need to hold the transaction open the entire time, you just need the inventory count to be correct.
You track the inventory and reservations. Taking a reservation checks that inventory is available. With row-level locking, only that inventory item is locked. If that fails, it can search for timed out reservations, update the inventory and try again.
If it succeeds, it decrements the inventory then adds a reservation. At that point, the transaction can close, and in the common case, you only held locks long enough to update a row in inventory and add a row to reservations.
You still have to handle the case where your transaction fails though. So you actually end up writing the same thing you'd do if you didn't have transactions: you issue an attempt to reserve, wait for the response to that attempt (whether that's transaction commit succeeding/failing or a queue processor processing), and handle both possible results. The transaction support doesn't actually help you because it's at the wrong level to be useful.
If you open an ACID transaction when the user adds something to the cart and don't close it until they check out, you'll find your database gets locked up pretty quickly. So you can't actually use the ACID transactions to implement the behaviour you want - you have to implement some kind of reserve/commit semantics in userspace, whether you're using an ACID database or not.