I have a producer consumer mechanism, I am forced to store produced items in database, this is a requirement. Also I have several producers and several consumers, producers and consumer threads will access records in several database tables; ProcessID column will determine which thread is accessing what record.
Threads will be working through a windows service.
ProcessID is created for three reasons.
1- If a thread terminated un-gracefully, the processID will be used to avoid re-starting processing.
2- Threads are synchronized through database Locking and hopefully I will got only Row locks, and probably I will have few blocked threads for short time, because every thread will access few records marked with ProcessID.
3- I wanted to keep track which thread on which time did what, because I am logging errors into database. Note that consumers will send items to a Web Service.
If I used In-Memory array as a queue, I doubt that it will enhance performance, it is having the following drawbacks:-
When a consumer de-queue an item, it will have to update its record in database anyway with its processID.
The producer will insert item into database, and get its ID using Output stp parameter, then it will put the item in the queue with its ID avoiding re-read it from database, the is the only benefit from the in-Memory queue, avoiding re-read an item from database. Note that once a record is inserted in DB, nothing will update it except a consumer.
Another problem is that, I assume that an operator can stop consuming a certain item by deleting it from database, If I used in memory queue, I will lose this future.
The Queue class will have to lock on private object, Accessing queue methods should be synchronized. I feel that I am duplicating the probability that a thread will be starved. and I feel that I am duplicating the time in which a thread will be blocked waiting.
Two Questions
1- Do I miss something in this design? Do you think it will work?
2- Is it a good idea to not use In-Memory queue?
Maybe I'm missing something, but I would still make a central in-memory queue as a service which would also take care of correct process retrieval strategy, and updating product rows with statuses. I don't think it's a huge development overhead.
You clearly stated by yourself that there will be situations when threads will be blocked for a short term. Such a queue would pay off if you may need to increase number of threads.
As for this, you can double check before handing over to consumer:
Another problem is that, I assume that an operator can stop consuming a certain item by deleting it from database, If I used in memory queue, I will lose this future.
And, of course, it's a much clearer design.. you'll follow single responsibility principle, when producers and consumers will take care of only producing and consuming items.
As for:
1- Do I miss something in this design? Do you think it will work?
.. I don't see reasons it shouldn't be working. So you have to make a final decision based on the whole picture.