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.