Why would I not specify ROWLOCK to get rid of page locks?

Question

I have a query that goes something like this:

SELECT TOP (1) * FROM MyTable WITH (UPDLOCK, READPAST) ORDER BY SomeColumn ASC;

This answer says that it's hard to say for sure but it will probably obtain a page level lock. I'm now profiling my database (actually just staring into sys.dm_exec_requests periodically) and it looks like when this query is executed from multiple clients the multiple instances of the query "get into a line" - like the earliest one locked something and all the others are waiting till that something is released.

I'd like to try add ROWLOCK to enforce obtaining a row level lock but it's kind of risky in the production database and I cannot fully reproduce the same load in my test database (at least until I understand all the subtleties).

Why would I not use ROWLOCK hint here? What could likely break?

Answer 1

Assuming the availability of an index to provide the requested sort order (SomeColumn ASC) the sample query you posted will in all likelihood take a row lock, even where the index in question is not covering for the query.

The query processor is smart enough to know that reading a single row from one end of an index (possibly followed by a lookup to retrieve additional columns) is a low-cost enterprise that suits a row locking strategy.

If there is no such index, or the execution plan is more complex than the sample query suggests (perhaps because the table is partitioned, or there is a WHERE clause), so that a significant amount of data must be read (and probably sorted) then the engine may start with page locks.

Note that ROWLOCK hints are suggestions. The engine can and will ignore the hint in some circumstances. This differs from other 'hints' like FORCESEEK that will throw an error if it cannot be honoured.

One reason not to use ROWLOCK is that it runs the risk of a very large number of locks being taken (and possibly held to the end of the transaction, depending on the exact circumstances). If there are conflicting locks on the table (or partition), these row locks would be prevented from escalating to a partition or table lock to release memory, which could affect the entire instance.

Ultimately, the details depend on localized details that are not provided in the question. That simple query should not require a ROWLOCK hint, given correct indexing. Nevertheless, the evidence suggests that page locks are currently being taken, which READPAST cannot skip.

If this query is being used to implement some sort of queuing mechanism, you would probably benefit from reading Remus Rusanu's excellent article on Using Tables As Queues.