using WHILE EXISTS (SELECT 1 FROM dbo.TargetQueue) caused service broker keep calling activation function even there is no msg on the target queue
0
votes
0
answers
73
views
I have the following SQL service broker set up:
USE master
-- Create database
ALTER DATABASE ServiceBrokerDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE ServiceBrokerDemo
CREATE DATABASE ServiceBrokerDemo;
-- Create a log
USE ServiceBrokerDemo
GO
DROP TABLE IF EXISTS DemoServiceBrokerLog;
CREATE TABLE DemoServiceBrokerLog (
ID INT IDENTITY(1,1) PRIMARY KEY,
Date DATETIME,
MessageTypeName sysname,
ActivationFunction sysname,
MessageBody xml
);
-- CREATE TWO MESSAGE QUEUES: one for the sender and one for the receiver (who processes messages from the sender)
USE ServiceBrokerDemo
GO
CREATE QUEUE dbo.InitiatorQueue WITH STATUS=ON ON [PRIMARY]
GO
CREATE QUEUE dbo.TargetQueue WITH STATUS=ON ON [PRIMARY]
GO
-- CREATE TWO ACTIVATION FUNCTIONS: for the two queues
-- This is for the target queue
CREATE OR ALTER PROCEDURE [dbo].[TargetQueueProcess]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @conversationHandle UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE @messagebody XML
DECLARE @errorNumber AS int;
DECLARE @errorMessage AS nvarchar(3000);
WHILE EXISTS (SELECT 1 FROM dbo.TargetQueue)
BEGIN
;RECEIVE TOP(1) @conversationHandle = conversation_handle
, @messagetypename = message_type_name
, @messagebody = CASE WHEN validation = 'X' THEN CAST(message_body AS XML) ELSE NULL END
FROM [dbo].[TargetQueue]
IF @conversationHandle IS NOT NULL
BEGIN
-- Log
INSERT INTO DemoServiceBrokerLog VALUES (
getdate(),
@messageTypeName,
'TargetQueueProcess',
@messagebody
);
IF @messageTypeName = 'http://my.dba.aventure/dbo/DemoServiceBroker/endofconversation '
BEGIN
-- End the conversation
-- The initiator will receive an EndDialog message afterwards
END CONVERSATION @conversationHandle;
END
ELSE IF @messageTypeName = 'http://my.dba.aventure/dbo/DemoServiceBroker/message '
BEGIN
BEGIN TRY
-- Process the message
PRINT ('Do the actual task in here')
END TRY
BEGIN CATCH
-- Capture the error
SELECT @errorNumber = ERROR_NUMBER()
, @errorMessage = ERROR_MESSAGE()
-- End the conversation with an error
-- The initiator will receive an Error message afterwards
END CONVERSATION @conversationHandle WITH ERROR = @errorNumber DESCRIPTION = @errorMessage;
END CATCH
END
END
END
END
GO
-- This procedure mainly to end the conversation at the initiator side and perform action upon errors
USE ServiceBrokerDemo
GO
CREATE OR ALTER PROCEDURE [dbo].[InitiatorQueueProcess]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @conversationHandle UNIQUEIDENTIFIER
DECLARE @messageTypeName SYSNAME
DECLARE @messagebody XML
DECLARE @errorCode int;
DECLARE @errorMessage nvarchar(3000);
--BEGIN TRANSACTION
;RECEIVE TOP(1) @conversationHandle = conversation_handle
, @messageTypeName = message_type_name
, @messagebody = CASE WHEN validation = 'X' THEN CAST(message_body AS XML) ELSE NULL END
FROM [dbo].[InitiatorQueue]
IF @conversationHandle IS NOT NULL
BEGIN
INSERT INTO DemoServiceBrokerLog VALUES (
getdate(),
@messageTypeName,
'InitiatorQueueProcess',
@messagebody
)
IF @messageTypeName = 'http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer '
BEGIN
;SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [http://my.dba.aventure/dbo/DemoServiceBroker/endofconversation] ;
END
ELSE IF @messageTypeName = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog '
BEGIN
END CONVERSATION @conversationHandle;
END
ELSE IF @messageTypeName = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error '
BEGIN
-- Log the error before ending the conversation
SET @errorCode =
(SELECT @messagebody.value(
N'declare namespace
brokerns="http://schemas.microsoft.com/SQL/ServiceBroker/Error ";
(/brokerns:Error/brokerns:Code)', 'int'));
SET @errorMessage = (SELECT @messagebody.value(
'declare namespace
brokerns="http://schemas.microsoft.com/SQL/ServiceBroker/Error ";
(/brokerns:Error/brokerns:Description)', 'nvarchar(3000)'));
-- do something with the error
PRINT('log the errors to some table')
-- End conversation on the initiator side
END CONVERSATION @conversationHandle;
END
END
--COMMIT TRANSACTION;
END
GO
-- Alter queue to associate them to the activation functions
USE ServiceBrokerDemo
GO
ALTER QUEUE dbo.TargetQueue WITH STATUS = ON, RETENTION = OFF, ACTIVATION (STATUS = ON, PROCEDURE_NAME = dbo.TargetQueueProcess, MAX_QUEUE_READERS = 1, EXECUTE AS SELF) , POISON_MESSAGE_HANDLING (STATUS = OFF)
GO
ALTER QUEUE dbo.InitiatorQueue WITH STATUS = ON, RETENTION = OFF, ACTIVATION (STATUS = ON, PROCEDURE_NAME = dbo.InitiatorQueueProcess, MAX_QUEUE_READERS = 1, EXECUTE AS SELF) , POISON_MESSAGE_HANDLING (STATUS = OFF)
GO
-- Create message type
CREATE MESSAGE TYPE [http://my.dba.aventure/dbo/DemoServiceBroker/message]
CREATE MESSAGE TYPE [http://my.dba.aventure/dbo/DemoServiceBroker/endofconversation]
-- Create contract
CREATE CONTRACT [http://my.dba.aventure/dbo/DemoServiceBroker/contract]
(
[http://my.dba.aventure/dbo/DemoServiceBroker/message] SENT BY INITIATOR,
[http://my.dba.aventure/dbo/DemoServiceBroker/endofconversation] SENT BY INITIATOR
)
-- CREATE Service
CREATE SERVICE [http://my.dba.aventure/dbo/DemoServiceBroker/InitiatorService] ON QUEUE [dbo].[InitiatorQueue] ([http://my.dba.aventure/dbo/DemoServiceBroker/contract])
CREATE SERVICE [http://my.dba.aventure/dbo/DemoServiceBroker/TargetService] ON QUEUE [dbo].[TargetQueue] ([http://my.dba.aventure/dbo/DemoServiceBroker/contract])
I sent a message from the initiator to the target using the following code:
USE ServiceBrokerDemo
DECLARE @conversationHandle UNIQUEIDENTIFIER
DECLARE @fromService SYSNAME = 'http://my.dba.aventure/dbo/DemoServiceBroker/InitiatorService '
DECLARE @toService SYSNAME = 'http://my.dba.aventure/dbo/DemoServiceBroker/TargetService '
DECLARE @contract SYSNAME = 'http://my.dba.aventure/dbo/DemoServiceBroker/contract '
-- Craft a contrived message body
DECLARE @messageBody XML = 'some message'
BEGIN DIALOG CONVERSATION @conversationHandle
FROM SERVICE @fromService
TO SERVICE @toService
ON CONTRACT @contract
WITH ENCRYPTION = OFF;
BEGIN CONVERSATION TIMER (@conversationHandle) TIMEOUT = 5
;SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [http://my.dba.aventure/dbo/DemoServiceBroker/message]
(@messageBody)
I ran the following code to check the status of the queue
SELECT *
FROM sys.dm_broker_queue_monitors AS m
INNER JOIN sys.service_queues q
ON m.queue_id = q.object_id
The status of the queue keeps to be RECEIVES_OCCURRING even hours after the last time I ran the code that sends a message. I would expect the state to be INACTIVE since all the messages in the target already processed right? And the target activation function keeps executing even though nothing in the queue.
However, as soon as I fixed the while loop part, the state of the queue is correctly changed to INACTIVE as soon the messages have been processed.
...
WHILE (1=1)
BEGIN
;RECEIVE TOP(1) @conversationHandle = conversation_handle
, @messagetypename = message_type_name
, @messagebody = CASE WHEN validation = 'X' THEN CAST(message_body AS XML) ELSE NULL END
FROM [dbo].[TargetQueue]
IF (@@ROWCOUNT=0)
BEGIN
BREAK
END
IF @conversationHandle IS NOT NULL
BEGIN
...
Could you help me explain why the WHILE EXISTS (SELECT 1 FROM dbo.TargetQueue)
caused the service broker keep calling the activation function to receive messages even though there is no message on the target queue?
Asked by Tuyen Nguyen
(343 rep)
Oct 18, 2024, 06:20 PM
Last activity: Oct 30, 2024, 02:28 PM
Last activity: Oct 30, 2024, 02:28 PM