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).
It depends on the widget.
If the widget is rare and expensive (exactly 10 Ferraris), then the approach you're following is correct. Of course, you also need to account for inventory that's being returned but hasn't been restocked yet, inventory that's out for repair, etc.
If the widget is a bit more common (5,000 wrenches) then the usual approach is to:
You can use a separate database for users with their carts than for inventory tallies, by using simple id's instead of foreign keys, and making up the non-null requirements by the application.
This will reduce some contention as compared with a single database.
The inventory database can store the total available inventory count for each item, and also in that inventory database (as you suggest) store/cache the computed value that is the total cart-claimed count from the all of the users/carts database, which will need to be updated as items are claimed/released by carts.
This will reduce some load on the user/cart database at the expense of managing the cached value by the application (caching/denormalized for performance).
Both the user/cart database and the inventory database can be sharded across numerous databases.
Sharding stores the same tables in multiple databases, though not the same data, as specifically chosen different rows go in each of the databases to spread the various access and modification loads across those databases. Sharding works well for things like users and inventories that don't need to be all accessed at the same time / in the same query (we don't often need to query all users (e.g. count of all users) or all items of inventory at the same time, e.g total of all inventories).
If the sharding strategy is simple (e.g. for inventory, the inventory id modulo the number of shards), it is relatively easy to identify which inventory shard has that inventory item.
The combination of the above should significantly reduce contention for database services.
Orthogonal to some of the above, you can also distribute the inventory count among inventory replicas, where if you have 5000 widgets and 2 replicas, each is given a tally of 2500 to sell.
No coordination is needed until some minimum threshold is reached (say one replica sells 2400 and is now down to 100).
At that point then the system may request a rebalancing of inventory from the other replica, so if the other replica still has 2000 remaining, then maybe half of that can be taken by the other.
The replication/distribution of inventory method can be combined with the sharding method, in that replicas can be sharded.