THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

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:

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
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:

USE SimpleSSB
GO

WAITFOR
(
RECEIVE *
FROM Simple_Queue
), TIMEOUT 300000

Finally, a test message was sent:

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

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.

 

Published Wednesday, January 10, 2007 2:08 PM by Adam Machanic
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Simon said:

If messages are sent on the same conversation then if a reader performs the read of the queue in a transaction then only that reader will be able to read the subsequent messages on that conversation.

If a second reader comes along, when they do a read they will get no results if no new converastions exist.

There is a statement GET CONVERSATION GROUP that assists in the process of locking conversation groups.

January 12, 2007 7:08 AM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement