I have a program with multi threads, say 2 workers. They will monitor the db
and get the records to process.
Now, I create a field, isLocked (bit) and once the record picked by either
one, it will be set to 1 (TRUE). I implement the logic at sql server stored
procedure:
1. select the records available from the table where isLocked = 0 (False)
2. select the top 1 based then available records
3. update the isLocked thread
4. return the RecordID for the Worker to process
But, how can i ensure there's no concurrency issue? I cannot ensure that
before Worker1 executing the update statement, Worker2 may run step 1
already to get the record. Therefore, both Worker1 and Worker2 would process
the record.
Any ideas to ensure only run by ONE worker?
thanks!