For many years we had a simple queue system, where a regularly executed loop would select and lock a next available row and call a stored procedure to actually process it, which, in turn would also select the same row with the same locking hints.
In a simplified form:
begin tran;
select top (1) @current_queue_row_id = QueueTable.row_id
from
dbo.QueueTable with (updlock, rowlock, readpast)
inner join <other tables>
where
QueueTable.status = 'Placed'
and <other tables>.something = 'OK';
if @current_queue_row_id is not null
exec dbo.ProcessQueueItem @current_queue_row_id;
commit tran;
create procedure dbo.ProcessQueueItem
@queue_row_id int
as
begin
...
select <@variables> = <columns>
from dbo.QueueTable with(rowlock, updlock, readpast)
where row_id = @queue_row_id;
...
end;
This worked for years, but after updating to SQL Server 2016 we intermittently (several times a week) see a situation where the outer select top (1)
would find a row, call ProcessQueueItem
, and it would fail because its select
returns nothing, so all @variables
remain null
. We have put in all sorts of logging and determined that the value passed to ProcessQueueItem
is a valid one, and that current_transaction_id()
returns the same value in the outer code and inside ProcessQueueItem
.
The isolation level is READ COMMITTED
, but no one is even trying to delete rows from anywhere, so non-repeatable reads should not be a problem.
Is there something fundamentally wrong with our setup in the first place, so that it's no wonder it broke after a server upgrade, or does this look like a problem in SQL Server 2016?
ProcessQueueItem
that turns up NULL in the SP - what happened to it? Its no longer in the queue but once was - was it processed or not? You don't have anyORDER BY
in your first query so I'm wondering if the same ID can't be grabbed 2x and sent to the SP twice by two separate checks of the queue, resulting in one failure and one success? I've seen similar issues after an upgrade where things overall perform a little slower/faster and inconsistencies in workflows pop up since they're not as tightly controlled as they should be. – LowlyDBA - John M Dec 1 '17 at 17:09order by
in the first query because it does not matter which record is processed next. Thereadpast
hint exists specifically to make sure that multiple queue processors will not grab the same row and will not be blocked by each other, and we currently have only one queue processor. The same ID should be visible to any code called by the processor because they are within the same transaction. – GSerg Dec 1 '17 at 18:03SELECT
in the SP? – LowlyDBA - John M Dec 1 '17 at 18:58Serializable
defeats the purpose ofreadpast
. On top of that,updlock
is not released prematurely, only shared locks do. – GSerg Jul 13 '18 at 7:03