I have never been a fan of solutions that rely on pessimistic locking. They always seem to lead to concurrency issues and performance problems.

Remember that SQL Server uses locks when it's reading data so if you were to set your isolation level to REPEATABLE READ or SERIALIZABLE, you'd be holding the SELECT locks on rows and index keys until the transaction terminates. This is probably the problem that you're seeing in your testing.

Would it be possible to process the data in blocks of rows instead of a single row? Fall back to the idea of creating a flag in the row. "Lock" the rows by setting the flag and then return them to the client. Let the client do the calculations for each row and then write all the updates to all rows. Then release the "lock."

-Mike

>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