I need to be able to find the next nonlocked row in a table. The scenario i'm trying to accomplish is this :

1. find a record in a table that needs updating and lock it
2. perform some calculations
3. update it
4. commit the changes
5. loop back to step 1 til complete

This update needs to run on multiple PCs simultaneously while other PCs may also be reading the data. I want the program to create a readonly type lock in step 1 such that during steps 2 through 4 the old data can still be read. However, if a second PC is running this same update process, when looking for a record to update, it must not get stuck on a record that's locked and being processed by the first PC.

I know I could add a field to flag that the record is currently being processed. But it seems that in step 1, I should be able to find a record that needs updating AND is not locked.

I've been playing with SELECT ... WITH (UPDLOCK) to lock the record and SELECT ... WITH (READPAST) to skip over the records locked by other PCs, but I can't seem to get this to work properly for me. The locks seem to escalate such that I can't read other rows in the table, or my READPAST doesn't bypass the lock i think i've got.

This doesn't seem that uncommon a problem, i'm thinking there must be a standard way of doing this.

TIA,
Anthony Letts