MySQL: how to ensure integrity in multiple read only architecture

Question

Scenario is simple to describe, but might have a complex answer:

Imagine a case where you have one write only mysql database. Then you have about 5 or 6 read only databases. The write database has a count for a particular inventory. You have hundreds of thousands of users banging away at this particular inventory item, but only limited quantity. For argument's sake, say 10 items.

What's the best way to ensure that only 10 items get sold? If there is even a 200ms delta between the time the read-only slaves get updated, can't the integrity of the count go stale, thus selling inventory you do not have?

How would you solve/scale this problem?

Answer 1

The basic solution to concurrent users will probably cover this too. At some point in the "buy" transaction, you need to decrement the inventory (on the write-server). Through whatever method, enforce that inventory can't go below zero.

If there's one item left, and two people trying to buy it, one will be out of luck.

The replication latency is exactly the same thing. Two users see a product available, but by the time they try to buy it, it's gone. A good solution for that scenario covers both replication latency and a user simply snatching the last item out from under another user.

Answer 2

It all depends on when and what window you decide to lock the master table for the update.

A. If you have to be 100% sure an item will be attempted to be bought only when its surely available. You will have to lock the item for the particular user as soon as you list it to him (which means you will temporarily decrement the inventory stock)

B. If you are okay with showing the one off "sorry, we just ran out of stock" message. you should lock the item just before you bill (well, you could do that after transaction is complete. but at the cost of a very furious customer)

I would chose approach A for locking, and may be flag a "selling out soon" warning for items with very low stock left. (if its a very frequent situation, you could proly also count the number of concurrent users hitting on the item and give a more accurate warning)

From the business perspective, you wouldn't want to be so low on stock (lower than the number of concurrent buyers) This is inevitable of course at "christmas" times when its okay to be out of stock :)