SQL Server, using a table as a queue

Question

I'm using an SQL Server 2008 R2 as a queuing mechanism. I add items to the table, and an external service reads and processes these items. This works great, but is missing one thing - I need mechanism whereby I can attempt to select a single row from the table and, if there isn't one, block until there is (preferably for a specific period of time).

Can anyone advise on how I might achieve this?

Solution 1

I need mechanism whereby I can attempt to select a single row from the table and, if there isn't one, block until there is (preferably for a specific period of time).

You can loop and check for new rows every second:

while not exists (select * from QueueTable)
    begin
    wait for delay '00:01'
    end

Disclaimer: this is not code I would use for a production system, but it does what you ask.

Solution 2

The only way to achieve a non-pooling blocking dequeue is WAITFOR (RECEIVE). Which implies Service Broker queues, with all the added overhead.

If you're using ordinary tables as queues you will not be able to achieve non-polling blocking. You must poll the queue by asking for a dequeue operation, and if it returns nothing, sleep and try again later.

I'm afraid I'm going to disagree with Andomar here: while his answer works as a generic question 'are there any rows in the table?' when it comes to queueing, due to the busy nature of overlapping enqueue/dequeue, checking for rows like this is a (almost) guaranteed deadlock under load. When it comes to using tables as queue, one must always stick to the basic enqueue/dequeue operations and don't try fancy stuff.

Solution 3

"since SQL Server 2005 introduced the OUTPUT clause, using tables as queues is no longer a hard problem". A great post on how to do this.

http://rusanu.com/2010/03/26/using-tables-as-queues/

Solution 4

The previous commenter that suggested using Service Broker likely had the best answer. Service Broker allows you to essentially block while waiting for more input.

If Service Broker is overkill, you should consider a different approach to your problem. Can you provide more details of what you're trying to do?

View more solutions

Comments

[Barguast]

Thanks. This is essentially the workaround I'm using at the moment but as you suggest it's not really the best solution. Specifically, I'd prefer something that doesn't involve looping - some kind of wait operation which either times out, or is kicked into action by a new row added to the table (via a trigger?)

[Barguast]

Essentially, I'm looking for the best way of passing work items between distributed services (currently on the same machine but, in future, they may be on different machines on the same LAN). In some cases I need the same work item to be sent to two different queues, hence the reason I originally chose to attempt my own implementation. Any advice would be welcomed. At the moment I'm considering a combination of service broker, along with a set of stored procedures that'll handle the distribution to multiple queues.

[RMD]

If the nature of these work items is asynchronous, you should consider something like MSMQ. MSMQ can provide reliable message delivery across service/machine boundries, including things like transmission retrial. But if you want to say "inside" SQL Server, Service Broker is exactly what you're looking for. Each service/machine would host a copy of SQL Server Express (or better) which would act as the local queue. It would then manage the process of transmitting your messages to remote services. It even manages the notifications on your end of message receipt. It supports multicasting too.

[irag10]

That's a great article, although according to stackoverflow.com/a/940001/8479 the locking hints aren't quite correct - e.g. need UPDLOCK

[David Roussel]

Is this pseudo code for your app layer? How do you implement GetNextMessage so multiple works can work on separate messages at the same time? Using row lock? Any more detail would be appreciated.

[Didaxis]

@David, my last paragraph in my example explains how this is accomplished. It is a little more involved however, as the actual queue operations need to be handled specially (i.e., if the transaction should be rolled back, we still need the message to move to the retry/poison queues as applicable). GetNextMessage is trivial, the work is getting SQL to do what we want here. In a nutshell, a queue client class was used that handles enlisting in the transaction, and a SQL CLR was used on the SQL side that allows enlisting in the .NET transaction.

[Didaxis]

We use regular SQL tables as queues, under heavy loads (200K+/hr) and have never experienced a deadlock. The very nature of a queue structure supports this, you insert at one end (ie the end of the table) and dequeue from the other (ie the top of the table via SELECT TOP 1). In real world usage, it's more complicated when things are transactional, but this is the gist of it