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
