Select query hangs when a large number of rows have an update lock

733 Views Asked by At

I am designing a program that will read a queue table. I am using locking to make sure that multiple instances do not pull the same row.

I am locking rows like this:

 BEGIN TRANSACTION
       UPDATE top(10) Source with (ROWLOCK, READPAST, updlock)
            SET status = status + 1

With another connection I read the rows like this:

SELECT COUNT(*) FROM  Source WITH (ROWLOCK, READPAST, updlock)

The count from the select statement does not include the rows I have locked. This is exactly what I want.

This works fine when I pick the top 10 rows, or 100, or even 1000. Somewhere around 4,690 (it's not consistent) the select begins to hang until the transaction is committed. It's not just slow; it waits for the transaction to end.

This is a test. My real query will not be using top. It will use a join which also causes the problem when too many rows are locked.

Any ideas on what may cause this? Is there a better way to have multiple instances read a table and not have conflicts?

0

There are 0 best solutions below