4

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?

  • The 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 any ORDER 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 McCall Dec 1 '17 at 17:09
  • @LowlyDBA Nothing happened to the row, it is still in the queue with the same status (ready for processing). There is no order by in the first query because it does not matter which record is processed next. The readpast 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:03
  • Can you post the create statement for the queue table? What other operations are being performed before the SELECT in the SP? – LowlyDBA - John McCall Dec 1 '17 at 18:58
  • @MisterMagoo Serializable defeats the purpose of readpast. On top of that, updlock is not released prematurely, only shared locks do. – GSerg Jul 13 '18 at 7:03
  • I think you need only updlock, readpast .Can you explain why use three hints.I think you should check compatibility Level of 2016.Where is rollback ? If there is error in 2nd proc then can it RollBack.Or if 2nd proc do ot process then should it rollback.I do not see any update/insert so why use Trans ? – KumarHarsh Aug 17 '18 at 5:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.