Q:

Data Synchronisation among clients

I have a database server (MySQL).
This holds a table of products.  One of the field of products table is remaining quantity.
I have few clients (around 10) which will be querying for the remaining quantity while placing an order. Once an order is placed the remaining quantity will decreased based on the order.
I face a problem when the quantity is low: for example if the remaining quantity is "10" and client1 is creating an order for 8quantity(order is not yet placed, but just in the process ) and client2 places order for 5 quantity, then the client which places the order latter will fail.
I want to improve the system such that when one client is in the process of ordering, then the other clients should be shown only the (remaining quantity - client1 order quantity).  Initially I thought of altering the remaining quantity as soon as it is entered in the order table, but this creates a problem when the order is cancelled or the computer which is placing the order is powered off / crashed.
Please suggest me some suitable algorithm to handle this case effectively.. Whenever the order is in progress, all other clients should see only the (remaining quantity - order in progress quantity).

A:

One answer might be in your question:
other clients should see only the (remaining quantity - order in progress quantity).

So your problem is to store data on "orders in progress"
Some people add an "confirmed" attribute to orders, which can be set to true/false to maintain the correct information. Or you could have a separate structure to record orders in progress, and move them to a permanent order table after confirmation. Either way, available stock is, as you said, (stock - orders in progress quantity), a query will give you that.
Then, you need to know what to do about cancellations. If a cancellation is explicit - user changes their basket - it's easy: remove the order. Only awkward case is when an order is not completed through: when is an "order in progress" not "in progress" any more? You could set a given time that an order remains in progress, and let the user know, e.g. "your order will remain in the basket for 24hrs, after which priority will be given to other customers on the stock" - or something like this.
Your system would use a cleanup process to compare the current date/time to the time the "order in progress" was made, and if the order is too old it's cancelled - or at least put on hold for the client to renew.

