This post is dedicated to all 10 other DBAs that use Service Broker (you know who you are).
The main reason for this blog post is that I’ve got no hits on Google for CSbRollbackHandlerTask::DisableQ
.
To improve my SEO I’ll be the first to write about it (repeatedly) and get that sweet sweet Service-Broker-questions traffic.
The problem
I was paged about a blocking chain where the blocked resource was a Service Broker queue, the lead blocked transaction was called CSbRollbackHandlerTask::DisableQ
, the lock mode was SCH-M
and even lock partitioning was involved.
I won’t repro fully this time, but I’ve covered lock partitioning repro in my post Async-stats-update-causing-blocking. The goal was to capture an instance of transaction CSbRollbackHandlerTask::DisableQ
with the same lock mode.
Repro
Let’s create some Service Broker objects
CREATE DATABASE ServiceBrokerDemo
USE ServiceBrokerDemo
-- Create the message type
CREATE MESSAGE TYPE SB_MessageType
VALIDATION = WELL_FORMED_XML;
GO
-- Create the contract
CREATE CONTRACT SB_Contract
(
SB_MessageType
SENT BY INITIATOR
);
GO
-- Create the target queue and service
CREATE QUEUE SB_TargetQueue;
GO
CREATE SERVICE SB_TargetService
ON QUEUE SB_TargetQueue
(SB_Contract);
GO
We’ll need to get the object_id
of the SB_TargetQueue
to plug into the monitoring
SELECT OBJECT_ID('SB_TargetQueue')
To monitor, I’ll create an Extended Event session CSbRollbackHandlerTask
CREATE EVENT SESSION CSbRollbackHandlerTask ON SERVER
ADD EVENT sqlserver.broker_queue_disabled
(
SET collect_database_name = 1
ACTION
(
sqlserver.is_system
)
)
, ADD EVENT sqlserver.lock_acquired
(
SET
collect_database_name = 1
, collect_resource_description = 1
ACTION
(
sqlserver.is_system
)
WHERE
[object_id] = 901578250 /* insert Queue's object_id here */
AND [mode] = 'SCH_M'
), ADD EVENT sqlserver.sql_transaction
(
ACTION
(
sqlserver.is_system
)
WHERE
[object_name] LIKE N'CSb%'
)
GO
ALTER EVENT SESSION CSbRollbackHandlerTask ON SERVER STATE = START
Test manual disable
Can it be as simple as disabling the queue?
ALTER QUEUE SB_TargetQueue WITH STATUS = OFF
The SCH-M lock is there (as expected, I’m disabling the queue) but no luck with the CSbRollbackHandlerTask::DisableQ
transaction.
Test poison message
The only other way to automatically disable a queue (I know of) is via poison message handling.
After I fail to process a message 5 times it gets marked as poisoned and the queue will get disabled (to prevent out-of-order processing)
So I’ll open up two sessions - one to send a message and another one where I’ll repeatedly fail to process it.
DECLARE @conversation_handle uniqueidentifier
DECLARE @message_body xml
BEGIN DIALOG @conversation_handle
FROM SERVICE SB_TargetService
TO SERVICE N'SB_TargetService'
ON CONTRACT SB_Contract
WITH ENCRYPTION = OFF;
SELECT @message_body = N'<SEO>CSbRollbackHandlerTask::DisableQ</SEO>';
SEND ON CONVERSATION @conversation_handle
MESSAGE TYPE SB_MessageType
(@message_body);
And in another session receive it in an infinite loop where I’ll rollback each time and exit the loop on no messages processed.
Don’t forget to reenable the queue after Test 1 ALTER QUEUE SB_TargetQueue WITH STATUS = ON
.
DECLARE @conversation_handle uniqueidentifier
DECLARE @message_body xml
WHILE 1 = 1
BEGIN
BEGIN TRAN
WAITFOR
(
RECEIVE TOP(1)
@conversation_handle = conversation_handle
, @message_body = CAST(message_body AS XML)
FROM SB_TargetQueue
)
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK
BREAK /* break out of the loop on error */
END
ROLLBACK /*5 rollbacks in a row trigger poison message handling */
END
After five runs, I get an error message
The service queue "SB_TargetQueue" is currently disabled.
Looking at the XE session
I get both sql_transaction
with the correct name - CSbRollbackHandlerTask::DisableQ
and a broker_queue_disabled
event. I guess manually disabling it doesn’t count 🤷♂️.
You can also notice that each entry has an is_system
flag set to True
.
This is unsurprising but interferes with my favourite tool for debugging blocking - the blocked process report.
When a blocked process report detects a system process is involved, it just gives up on giving me any useful details to track the culprit. The sad part is, that it considers an Activation Stored Proc as a background process and refuses to give me that information either. This and everything else is why debugging Service Broker issues isn’t fun.
As always thanks for reading.
CSbRollbackHandlerTask::DisableQ