SQL Select Rows from a table and update the same rows

Question

I want to :

1. Select N rows from a table for processing where flag=0
2. Do some work on a second table using values from these N rows
3. Update these N rows and set flag=1

I have parallel processes doing this same work together, and i want to ensure that all get to work on unique rows. How do i ensure that?

Answer 1

I assume you are running on SQL Server (because of the tag), if not then my answer is not applicable. Locking alone is not enough. If you use database record locking SqL server will block other processes trying to access the locked row and in effect you will handle only one row at a time. The solution for you is to combine row locking with READPAST hint so the rows locked by someone else will be skipped. Here's what each process should do:

1. select next unlocked row for processing and lock it
2. do the work
3. update the row and end transaction

select top 1 id, ... from TheTable with (updlock, readpast) where flag = 0

//do the work now

update TheTable set flag = 1 where id=<previously retrieved id>

The nice thing here that the operation of selecting the next unlocked row and locking it is atomic so it guarantees that no one else will be able to select the same row.