Best way to handle a table-based queue?

Question

Best way to handle a table-based queue?

Viewing post 31 (of 30 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 1

Scott Coleman - Tuesday, June 13, 2017 8:58 AM
Jeff Moden - Monday, June 12, 2017 5:49 PM
Scott Coleman - Monday, June 12, 2017 2:50 PM
I'd still like to hear someone say why they don't use UPDATE ... OUTPUT to pick an item from the queue without an explicit transaction.
UPDATE TOP (1) dbo.ItemQueue WITH(READPAST) SET SessionKey = NEWID() -- additional columns such as StartTime as needed OUTPUT Inserted.* INTO @QueueItem WHERE SessionKey IS NULL;
I'm not concerned with the distinction between OUTPUTting the whole row from the queue table vs just the key value, I want to see comments on any potential pitfalls of using UPDATE (or DELETE) with an OUTPUT clause to pop an item off a queue table in one atomic operation.
I might be missing something but it seems to me that, for the original problem stated in this post, that would require a join to the Table Variable when you wanted to do the final update to the table if the table had start and end date columns as I proposed. Even without such columns, if this were in a stored procedure as stand-alone functionality and you needed to return the value as an output variable, it would no longer be a single operation.
This update finds the first queue entry with a NULL SessionKey, updates SessionKey, and returns the selected row in one atomic operation without an explicit transaction. If you only need QueueItemId, you can use OUTPUT Inserted.QueueItemId INTO @QueueItemId, or you can use SELECT @var = col FROM @QueueItem to unpack the columns from a single-row table variable into as many variables as you need. None of those actions are going to have locking issues with concurrent processes. Your suggestion with the SELECT in a CTE and the quirky update to preserve the key value accomplishes the same thing in one statement.
I did something similar recently where I needed a FIFO queue table to control parallel processes, but had no reason to leave the items in the queue. I used "DELETE TOP (1) OUPUT Deleted.* ..." to pop items off the queue. I don't think you can make an UPDATE do the same thing, no matter how quirky. It worked fine from a C# program in an Command.ExecuteReader method. Running it from C# also allows use of Thread.Sleep to wait for a retry instead of an active session with WAITFOR DELAY. If you're really obsessed with efficiency and only need the key value, use OUTPUT Deleted.ItemKey in an ExecuteScalar method and avoid creating the DataReader object.
A lot of intelligent people have addressed the topic of how to get the next item from the queue without race conditions or excessive blocking. I was curious why no one suggested using an OUTPUT clause, except another post asking why no one used OUTPUT. That's my basic question, OUTPUT seems like a good solution for this problem and I'm interested in whether anyone knows of any reason not to do it that way.

From the above...

This update finds the first queue entry with a NULL SessionKey...

It actually doesn't find the "first" queue entry. It finds "A" queue entry. Without an ORDER BY, it may or may not be the "first" entry either temporally or by IDENTITY column. Also and as previously stated, a "SessionKey" isn't really necessary here. The ItemQueueId will suffice and is already present.and, unless there's a separate table for the SessionKeys to keep track of start and end times, does nothing but provide a mark that a queue item has been picked up for processing.

I did something similar recently where I needed a FIFO queue table to control parallel processes, but had no reason to leave the items in the queue. I used "DELETE TOP (1) OUPUT Deleted.* ..." to pop items off the queue. I don't think you can make an UPDATE do the same thing, no matter how quirky

I agree. If you don't need to keep a history of what has been processed or that's tracked in some other table if you do, then DELETE with an OUTPUT to pick up on any parameters or instructions from the queue works great provided that you don't have a problem using a Table Variable or a Temp Table. If it IS a FIFO operation that's needed, you'll still need an ORDER BY to guarantee the processing order.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)