5

We noticed this weird situation, that a query that returns no records, will wait for a lock when WITH(UPDLOCK) is specified.

The query is as simple as

SELECT primaryKey FROM someTable WITH(UPDLOCK) 
WHERE columA = 'A' 
      AND columnB BETWEEN 6 AND 8 
      AND columnC != 'C' ;

The system that runs this query uses isolation level read uncommitted, which we cannot change.

Is there a way, e.g. a hint, that wont let this wait, but still lock records if it would yield results?

I noticed that index usage plays part in this, because other queries do not have this behaviour. But sadly, defining indexes is also not in the realm of possibilities.

The only solution I came up with is throwing an IF EXISTS in there, before executing the actual query, but it might still happen due to race conditions.

Similar problem has been answered here: Why does UPDLOCK cause SELECTs to hang (lock)?

Without an index to locate the row(s) to be locked, all tested rows are locked, and locks on qualifying rows are held until the transaction completes.

But I do not think the provided answer there applies to my version of the problem, because the query does not return rows to "qualify".

I do not not have access to the system sadly. We can only run queries against certain tables.

The purpose is to lock and read a record if it exists and to update it eventually. Insert if it is missing.

UPDLOCK is used because that is the correct one to my understanding, when locking in read uncommitted isolation.

The table has a primary key, but it is not used in the where clause.

1
  • 1
    You can change the isolation level using table hints, as well as setting it manually before running the query Feb 5, 2022 at 21:10

1 Answer 1

7

There's no direct way to do what you want: read rows at read uncommitted isolation, only taking U locks on rows that match a given predicate.

How UPDLOCK works

When you specify UPDLOCK, SQL Server takes U locks at the row or page level. If a table lock is needed, SQL Server takes an X lock instead.

Table and page locks are held to the end of the transaction regardless of whether any matching rows were found. When row-level locks are used, SQL Server always takes a U lock on a row before testing to see if it matches the conditions.

In the special case where a row is tested and found not to match the predicate in the same data access operator (e.g. a scan or seek), the U lock on the current row is released just before acquiring a U lock on the next row. Otherwise, the U lock is held to the end of the transaction as usual.

That is a description of how SQL Server implements the UPDLOCK hint. I do understand it is not clear in the documentation, or how you would like it to behave. Nevertheless, that is how it is.

Workarounds

There is no perfect way to implement what you want, but there are a couple of partial solutions.

The first one is WITH (UPDLOCK, READPAST, ROWLOCK):

SELECT 
    ST.PrimaryKey
FROM dbo.SomeTable AS ST 
    WITH (UPDLOCK, READPAST, ROWLOCK) 
WHERE
    ST.ColumnA = 'A' 
    AND ST.ColumnB BETWEEN 6 AND 8 
    AND ST.ColumnC != 'C';

This will skip blocking if the row to be tested has an incompatible row-level lock. The query will still block if the incompatible lock is held at the page or table level.


The second workaround is to read at the session isolation level, then take U locks in a separate data access by joining back on the primary key:

SELECT 
    CA.PrimaryKey 
FROM dbo.SomeTable AS ST -- No hints here
CROSS APPLY 
(
    SELECT TOP (1) 
        ST2.PrimaryKey
    FROM dbo.SomeTable AS ST2 
        WITH (UPDLOCK, FORCESEEK)
    WHERE
        ST2.PrimaryKey = ST.PrimaryKey
) AS CA
WHERE
    ST.ColumnA = 'A' 
    AND ST.ColumnB BETWEEN 6 AND 8 
    AND ST.ColumnC != 'C';

It is important to follow the pattern exactly there, including the top. When implemented correctly, this method should be pretty reliable in practice.

Upserts

I have to mention the question seems like an upsert pattern. Taking UPDLOCK alone is not sufficient.

You also need a transaction around every part of the upsert, and a SERIALIZABLE hint to ensure any row that does not exist, continues not to exist until the insert is performed. Update locks can only be taken on rows that exist. See SQL Server UPSERT Patterns and Antipatterns by Michael J Swart.

Of course when you use SERIALIZABLE semantics, your blocking chances might increase substantially.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.