We have a .Net service that reads data from sql server. do some processing and then again update the sql server table. Now we need to run multiple instance of the this service. So i am presented with below issue.
- Since we don't have any column in sql server end that let me know whether a row has been read or not. so i may get same value in different instance.
In my opinion i need to create a column to specify a row is read or not. Can you suggest anything like MSMQ or Service Broker
You can consider creating a table, something like ReservedRows to keep track of rows that are being processed, it contains IDs of rows in target table.
Then in the service, first lock the ReservedRows table, then read the data you want to process, excluding the ones already in ReservedRows, insert into ReservedRows the ids of the records you are reading, then unlock the table. Since you inserted the ids of rows you are processing, other instances of the service won't process those until you are done.
Once you are done processing the rows and updating the target table. Delete the IDs of rows you just processed from the ReservedRows table so that other instances can process those later.