Service Broker Adventures: Stucked Queue Monitors

,

This week I'm in London helping a client with its large scale Service Broker deployment,

mainly troubleshooting some strange problems with Service Broker and helping to improve

the overall scalability and messaging throughput. Today I want to share a strange

scenario with you, how a badly written Activated Stored Procedure isn't really activated

by Service Broker. Let's assume the following basic setup of Service Broker, where

we are sending messages from the Initiator Service to the Target Service:

CREATE DATABASE Chapter4_InternalActivation

GO

USE Chapter4_InternalActivation

GO

--*********************************************

--* Create

the message type "RequestMessage"

--*********************************************

CREATE MESSAGE TYPE

[http://ssb.csharp.at/SSB_Book/c04/RequestMessage]

VALIDATION = NONE

GO

--*********************************************

--* Create

the message type "ResponseMessage"

--*********************************************

CREATE MESSAGE TYPE

[http://ssb.csharp.at/SSB_Book/c04/ResponseMessage]

VALIDATION = NONE

GO

--************************************************

--* Changing

the validation of the message types

--************************************************

ALTER MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c04/RequestMessage]

VALIDATION = WELL_FORMED_XML

GO

ALTER MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c04/ResponseMessage]

VALIDATION = WELL_FORMED_XML

GO

--************************************************

--* Create

the contract "HelloWorldContract"

--************************************************

CREATE CONTRACT [http://ssb.csharp.at/SSB_Book/c04/HelloWorldContract]

(

[http://ssb.csharp.at/SSB_Book/c04/RequestMessage] SENT BY INITIATOR,

[http://ssb.csharp.at/SSB_Book/c04/ResponseMessage] SENT BY TARGET

)

GO

--**************************************************

--* Create

a table to store the processed messages

--**************************************************

CREATE TABLE ProcessedMessages

(

ID UNIQUEIDENTIFIER NOT NULL,

MessageBody XML NOT NULL,

ServiceName NVARCHAR(MAX) NOT NULL

)

GO

--********************************************************

--* Create

the queues "InitiatorQueue" and "TargetQueue"

--********************************************************

CREATE QUEUE InitiatorQueue

WITH STATUS = ON

GO

--************************************************************

--* Create

the queues "InitiatorService" and "TargetService"

--************************************************************

CREATE SERVICE InitiatorService

ON QUEUE InitiatorQueue

(

[http://ssb.csharp.at/SSB_Book/c04/HelloWorldContract]

)

GO

For the Target Service an Activated Stored Procedure was written, and this Stored

Procedure was driven by a configuration table. The configuration table specified if

the Stored Procedure was retrieving messages from the underlying queue, or not. Have

a look at the following code:

--************************************************

--* Create

a simple config table

--************************************************

CREATE TABLE Config

(

QueueName SYSNAME NOT NULL PRIMARY KEY,

QueueEnabled BIT NOT NULL

)

GO

--************************************************

--* Insert

a config record

--************************************************

INSERT INTO Config VALUES ('TargetQueue', 0)

GO

--************************************************************************

--* A

stored procedure used for internal activation on the target queue

--************************************************************************

CREATE PROCEDURE ProcessRequestMessages

AS

DECLARE @ch UNIQUEIDENTIFIER

DECLARE @messagetypename NVARCHAR(256)

DECLARE @messagebody XML

DECLARE @responsemessage XML

DECLARE @enabled BIT;

WHILE (1=1)

BEGIN

BEGIN TRY

BEGIN TRANSACTION

--

Check if the queue is enabled

SELECT @enabled = QueueEnabled FROM Config

WHERE QueueName = 'TargetQueue'

IF (@enabled = 1)

BEGIN

WAITFOR (

RECEIVE TOP(1)

@ch = conversation_handle,

@messagetypename = message_type_name,

@messagebody = CAST(message_body AS XML)

FROM TargetQueue

), TIMEOUT 60000

IF (@@ROWCOUNT = 0)

BEGIN

ROLLBACK TRANSACTION

BREAK

END

IF (@messagetypename = 'http://ssb.csharp.at/SSB_Book/c04/RequestMessage')

BEGIN

-- Store

the received request message in a table

INSERT INTO ProcessedMessages (ID, MessageBody, ServiceName) VALUES (NEWID(), @messagebody, 'TargetService')

-- Construct

the response message

SET @responsemessage = '<HelloWorldResponse>' + @messagebody.value('/HelloWorldRequest[1]', 'NVARCHAR(MAX)') + '</HelloWorldResponse>';

-- Send

the response message back to the initiating service

SEND ON CONVERSATION @ch MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c04/ResponseMessage] (@responsemessage);

-- End

the conversation on the target's side

END CONVERSATION @ch;

END

IF (@messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')

BEGIN

-- End

the conversation

END CONVERSATION @ch;

END

END

ELSE

BEGIN

--

When the queue is not "enabled" in the config table, we just return

COMMIT TRANSACTION

BREAK

END

COMMIT TRANSACTION

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION

END CATCH

END

GO

This approach is a little bit strange, because you just have to disable activation,

which leads to the same scenario, so that the Activated Stored Procedure isn't doing

anything anymore (as soon as the queue is empty). This scenario was invented, because

sometimes it was impossible to disable Service Broker queues, because of a Locking/Blocking

scenario. The Locking/Blocking scenario occurred, because there were uncommitted transactions

from the Activated Stored Procedure, which means that some locks were held forever

in SQL Server, which finally blocked the disabling of the queue. See my last blog

posting on this phenomenon: http://www.sqlpassion.at/blog/PermaLink,guid,fc4f98af-f42a-4b3d-872e-c31815e6fc02.aspx.

So when you have changed the entry in the config table, the Activated Stored Procedure

was just exiting – so far so good in the theory…

The real truth about that "solution" was the fact, that the Activated Stored Procedure

wasn't ever processing messages. You might now ask why. The answer on that question

was finally very easy: As soon as your Stored Procedure gets activated by Service

Broker, you MUST process messages from your queue through the RECEIVE statement,

otherwise Service Broker assumes that your Stored Procedure has encountered a problem,

and considers the Stored Procedure to be failed. See the following remarks from Books

Online (http://msdn.microsoft.com/en-us/library/ms171585(v=sql.105).aspx):

An

activated stored procedure must receive messages from the queue that activated the

procedure. If the stored procedure exits without receiving messages or the queue monitor

detects that the stored procedure is not receiving messages after a short time-out,

the queue monitor considers the stored procedure to have failed. In this case, the

queue monitor stops activating the stored procedure.

But how can you now troubleshoot that specific problem to find out if Service Broker

had considered your Stored Procedure to be failed? There's the DMV sys.dm_broker_queue_monitors,

which shows the so-called Queue Monitors. Those components are responsible

for activating your Stored Procedure. A Queue Monitor can be in 3 different states:

  • INACTIVE
  • NOTIFIED
  • RECEIVE_OCCURING

When a Stored Procedure is not activated for your queue, then the corresponding Queue

Monitor is in the INACTIVE state. As soon as the Queue Monitor has

started your Stored Procedure, the Queue Monitor goes into the NOTIFIED state.

And finally the Queue Monitor goes into the RECEIVE_OCCURING state,

when the Activated Stored Procedure receives messages. This means that the Queue Monitor

remains in the NOTIFIED state, when your Stored Procedure isn't receiving

any messages. As long as your Queue Monitor is stucked in the NOTIFIED state,

you are not processing any messages from your queue!

In this specific scenario the Queue Monitor is also not moving into the RECEIVE_OCCURING state,

when you change the entry in the user-defined configuration table – it just remains

in the NOTIFIED state. The only solution is to disable and re-enable

the queue to restart the corresponding Queue Monitor. But when your queues are blocked

through locks from an uncommitted transaction… you see one problem leads to another

problem… You can find here the

download to the script with which you can reproduce the stucked Queue Monitor within

Service Broker.

What's the moral of this story: read the f… manual and code your Activated Stored

Procedures in a very robust way J. I've

talked a lot to different people over the last years about Service Broker. Everyone

is just scared about Service Broker, because it's such an overcomplicated technology.

In my opinion Service Broker isn't really complicated, but you have to know the various

design patterns behind Service Broker, and it's up to your Activated Stored Procedures

what you're doing with Service Broker, and how healthy your Service Broker solution

will be. Service Broker itself is very robust (he's just sending messages from A to

B, nothing more), and will not cause any problems to you, almost of the time you are

the trigger of the problems…

Thanks for reading

-Klaus

Rate

Share

Share

Rate