How to make sure the record processed by one thread

[Mullin Yu]

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!

 

[Guest]

Use BEGIN TRAN inside the SP

 

[Mullin Yu]

i added already, but don't know whether it's sufficient enough to guard
this!

 

[Guest]

It should be...
SQL server has extremely complex and robust algorithms to handle transactional requests to the same procedure in the same database, when these requests are expected to be from different processes on different machines, so it shouldn't be any different just because they're from different threads on the same process on the same machine.

Set up a test bench to hammer it with as many different concurrent requests as possible, and see how it stands up if you've got any doubts.

If in the ulikely (probably impossible) scenario that it doesn't, then you'll have to engineer some sort of queuing component or transactional resolver, but like I say it WILL be fine.