0

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?

Jofre
  • 3,718
  • 1
  • 23
  • 31
Don Chambers
  • 3,798
  • 9
  • 33
  • 74
  • 2
    *sounds* like lock escalation, have you checked in `sys.dm_exec_requests` or used a utility such as *sp_whoisactive* to see what is blocking? – Stu Mar 17 '22 at 19:31
  • 1
    It's probably not _number of rows_ but rather _number of pages that hold those rows_ that is the tipping point leading to more aggressive locking. This is one of the reasons we often advocate for finding the sweet spot for your table and keeping your update activities to batches <= sweet spot. So maybe keeping the `TOP` and spreading the updates across multiple batches is going to be the right answer (or not getting your counts that way in the first place, since updates won't change an unfiltered count - see https://sqlperformance.com/2014/10/t-sql-queries/bad-habits-count-the-hard-way)) – Aaron Bertrand Mar 17 '22 at 19:48
  • In other words, why do you want the `COUNT(*) FROM Source` to _not_ include the rows you are _changing_ (but not adding or deleting)? They were there before the update, and they will still be there after the update, so why should they not count _during_ the update? Really you should be getting unfiltered counts from DMVs but if the count is really filtered (like on `status`) then maybe RCSI is the answer. – Aaron Bertrand Mar 17 '22 at 19:51
  • I want to select some rows and do something with them. There will be several isntances of this and I don't want them selcting the same rows. I have tried just updating the status but there seem to be collisions when I do this. Not a lot, but a few each hour when there are millions of rows. – Don Chambers Mar 17 '22 at 22:06

0 Answers0