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
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 EXISTSin 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.
UPDLOCKis 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.
carriann last edited by
There's no direct way to do what you want: read rows at read uncommitted isolation, only taking
Ulocks on rows that match a given predicate.
When you specify
UPDLOCK, SQL Server takes
Ulocks at the row or page level. If a table lock is needed, SQL Server takes an
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
Ulock 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
Ulock on the current row is released just before acquiring a
Ulock on the next row. Otherwise, the
Ulock is held to the end of the transaction as usual.
That is a description of how SQL Server implements the
UPDLOCKhint. 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.
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
Ulocks 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.
I have to mention the question seems like an upsert pattern. Taking
UPDLOCKalone is not sufficient.
You also need a transaction around every part of the upsert, and a
SERIALIZABLEhint 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
SERIALIZABLEsemantics, your blocking chances might increase substantially.