Let's say I have a table with 50,000 transactions that need to be processed in the background in the same order they were received. I have a Windows service that polls for transactions, picks them up, updates the transaction table that that record is picked by this instance at a particular time. All this works perfectly well.
Now, suppose I want to scale out the processing and run another instance of the same service on another machine, how do I ensure that both instances do not pick up the same records?
I either have both instances picking the same record and one instance overwriting update of the other or if I have additional filters in the update query to prevent that I have one instance picking up some records and updating it and the other instance running but not able to do anything simply because the record is already updated.
Is there a way to solve this?