Query that returns no records hangs on lock



  • 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: https://dba.stackexchange.com/q/114572

    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.



  • 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 https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table#arguments , 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 https://michaeljswart.com/2017/07/sql-server-upsert-patterns-and-antipatterns/ by Michael J Swart.

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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2