Service Broker Blocking

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

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