We currently have a store/shopping cart system that uses a single database. We have products with a field for the number we have in inventory (say 100 widgets). We have a customer table. When someone adds a widget to their cart, we insert a record in a join table between the customer and the product which represents intent to purchase. That customer_product record has a status indicating that it's either in the cart or that the purchase has been completed ('Pending','Purchased').
When a customer request hits the system to add a product to their cart, we count the number of purchased and pending customer_product records for that product and disallow it if the number is equal to the total (100). This way, we ensure that we don't allow 101 people to have 100 items.
The database is our system bottleneck, and the join table gets hit a lot. I suspect row and page locks affect performance under load. I would guess systems like Amazon's/eBay's must have a distributed db architecture, and yet somehow manage the problem of 2 people wanting to put the last item in their cart at the same time. I'd like to rearchitect our store/cart to alleviate the db constraint.
With a single database, we can do something in our join record insert WHERE clause to include a subquery count so that if two db transactions are trying to do the "last widget" insert concurrently that whichever tries to commit second will fail because the count will prevent it after the 2nd-to-last transaction takes the last widget and changes the count. But in a distributed database, I'm guessing that trick won't work.
What general system architecture guiding principles or patterns apply when addressing such concurrency and shared resource challenges in a distributed system?
Note: I'm aware of similar questions (like Best-practice to manage concurrency into a basket in a e-commerce website). This question is specifically about how to handle it in a distributed architecture where every db instance has a copy of the tables and changes in one propogate to the others only every so often (at least that's how I imagine it - I haven't actually set up a distributed db system before).