Design Real-Time Product Stock Management

Question

Hi,

I need to design a real-time product stock management engine (C# & WCF) but i don't know how to proceed in order to handle concurrency access and data integrity.

Here is some of the features the engine should be handle :

1. Stock Incoming products
2. Order preparation
3. Move products from one place to another
4. ...

May i use MSMQ in order to ensure correct stock count (Messages processed in order by message pooling) or may i use application thread locking.

Note that my application have to be in Real-Time, preparer have to know in real-time how many products there are in stock in time. If there is lack of products at picking he can send a "request" to an operator.

Answer 1

Use a SQL database. They are already designed with data integrity, concurrency and data storage in mind.

Answer 2

Hi,

you should probably use an SQL database as Lee says. If you use a transaction to e.g. store an order and decrease available product counts (both in the same transaction) the database guarantees atomicity. You probably also want some kind of concurrency mechanism (like a row version) to prevent inconsistent values (1st process reads, 2nd process updates the same value, then 1st process updates too overwriting the previous update based on outdated values).

Answer 3

Well the scenario that you have mentioned is generally where one has to use a queue rather than a persistent storage to meet the throughput needs. On searching on the net you can find a lot of case studies for the same where people have employed queuing systems to enhance the throughput of the system. SQL server can just not scale to that levels.

In special cases when your need to make your queue persistent very special methods are used as to how to mitigate the performance effects because of this. For ex. Apache's ActiveMQ has its own special file storage system which performs much better compared to simply using a MySQL for the backend persistence. Probably MSMQ also provides a similar option but am not sure.