pradeep bn pradeep bn -4 years ago 159
C# Question

Pattern to access database from multiple processes

I have a windows scheduler service written in C# that queries a bunch of records from database, performs a set of operations on the data and updates these records back to the database. I now need this service to be deployed on multiple machines. However, the obvious problem I will end up is the concurrency. The service running on multiple machines should not pick up the same records. Is there a pattern/best practice that fits this situation?
I'm using Oracle 11g database.

We have already reached the maximum limit in terms of performance after using multi threaded approach for processing records. To achieve higher performance, we are thinking of running the service on multiple machines.

Answer Source

You need to maintain the concurrency programmatically.

  1. You can have the status column in the table(records) depicting whether the record is already processed or in-process or not processed.
  2. You can use the rowversion/timestamp in table(records) in case you want to avoid other transaction updating the same value. (Considering MS-SQL Server family)

Can we know the reason to have services on multiple machines? In case your answer lies for the performance then you can use Task/multithreading etc to achieve the same.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download