SQL Server race condition issue with range lock
- by Freek
I'm implementing a queue in SQL Server (please no discussions about this) and am running into a race condition issue. The T-SQL of interest is the following:
set transaction isolation level serializable
begin tran
declare @RecordId int
declare @CurrentTS datetime2
set @CurrentTS=CURRENT_TIMESTAMP
select top 1 @RecordId=Id from QueuedImportJobs with (updlock) where Status=@Status and (LeaseTimeout is null or @CurrentTS>LeaseTimeout) order by Id asc
if @@ROWCOUNT> 0
begin
update QueuedImportJobs set LeaseTimeout = DATEADD(mi,5,@CurrentTS), LeaseTicket=newid() where Id=@RecordId
select * from QueuedImportJobs where Id = @RecordId
end
commit tran
RecordId is the PK and there is also an index on Status,LeaseTimeout.
What I'm basically doing is select a record of which the lease happens to be expired, while simultaneously updating the lease time with 5 minutes and setting a new lease ticket.
So the problem is that I'm getting deadlocks when I run this code in parallel using a couple of threads. I've debugged it up to the point where I found out that the update statement sometimes gets executing twice for the same record. Now, I was under the impression that the with (updlock) should prevent this (it also happens with xlock btw, not with tablockx). So it actually look like there is a RangeS-U and a RangeX-X lock on the same range of records, which ought to be impossible.
So what am I missing? I'm thinking it might have something to do with the top 1 clause or that SQL Server does not know that where Id=@RecordId is actually in the locked range?
Deadlock graph:
Table schema (simplified):
