Service Broker: Messages are queued, but how about readers?

The central database object that supports SQL Service Broker (SSB) is a queue. Messages sent between SSB services are queued and are delivered in order within a conversation. And that’s exactly what one would expect from a queued solution.

But how about other parts of the SSB equation? Do they also follow a queued/ordered path? Working on a solution recently, I decided to find out whether any guarantee existed with regard to readers getting messages in the order in which they had been waiting. I was curious as to what would happen if there were two or more readers waiting on a queue. Would the reader that had been waiting the longest be the first to pick up a message?

To test this, I started with the following setup script to create a test database and a simple queue/service pair:

[sql]CREATE DATABASE SimpleSSB
GO

USE SimpleSSB
GO

–Create a database master key
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = ‘onteuhoeu’
GO

–Create a message type
CREATE MESSAGE TYPE Simple_Msg
VALIDATION = EMPTY
GO

–Create a contract based on the message type
CREATE CONTRACT Simple_Contract
(Simple_Msg SENT BY INITIATOR)
GO

–Create a queue
CREATE QUEUE Simple_Queue
GO

–Create a service
CREATE SERVICE Simple_Service
ON QUEUE Simple_Queue
(Simple_Contract)
GO[/sql] Once this has been created, the test involved opening several SSMS windows (I used four) and starting readers waiting on the queue. Each window used the following code:

[sql]USE SimpleSSB
GO

WAITFOR
(
RECEIVE *
FROM Simple_Queue
), TIMEOUT 300000[/sql]

Finally, a test message was sent:

[sql]DECLARE @h UNIQUEIDENTIFIER

BEGIN DIALOG CONVERSATION @h
FROM SERVICE Simple_Service
TO SERVICE ‘Simple_Service’
ON CONTRACT Simple_Contract
WITH ENCRYPTION=OFF;

SEND ON CONVERSATION @h
MESSAGE TYPE Simple_Msg
GO[/sql]

I expected the first reader window I’d opened to process this message as it had been waiting the longest and therefore should have been first in the queue. However, much to my surprise that’s not what happened. Instead of the first reader receiving the message, the last reader–the one that had been waiting the least amount of time–got the message.

I puzzled over this for a while and finally decided to ask the expert, Remus Rusanu, who spends time in the MSDN Service Broker forums answering questions. Remus explained that the behavior I saw in my test was actually done that way on purpose. The expected behavior for readers is not that of a queue, but rather that of a stack (i.e., LIFO rather than FIFO).

The reason for this, as it turns out, is for activation scenarios. Imagine that you have an activation procedure that uses a timeout of 40 seconds, and messages are coming in every 30 seconds. Only one activated procedure is needed to fill the load. But now imagine that a burst of activity occurs, and an additional instance of the procedure has been activated. Once normal activity resumed, if the system worked the way I expected it to one of the instances would pick up the first message then start waiting again. Then 30 seconds later the second instance would pick up the message (since it would have been waiting longer) and start waiting again. Back and forth, and the second–unnecessary–instance would never timeout. But the way the system is actually implemented, the first instance picks up the first message, then starts waiting again and picks up the second message as well–allowing the second instance to timeout since it’s no longer needed.

This is not the behavior I either expected or was hoping for, but it makes perfect sense given what Service Broker is intended to do. I think it’s a very interesting feature of the system.

My next post will show you how to work around this and get a queued behavior for readers. This is the behavior I required for the situation I was working on (you can read about it in my thread with Remus). Although probably not too common a requirement it is something I’m betting others will need from time to time.