Rollback then receive the same message from queue-CodePudding

I have an activation stored procedure like this:

DECLARE
    @conversation_handle [uniqueidentifier],
    @message_body [varbinary](max)

WHILE 1 = 1
BEGIN TRY
    BEGIN TRANSACTION

    WAITFOR
    (
        RECEIVE TOP (1)
            @conversation_handle = [conversation_handle],
            @message_body = [message_body]
        FROM
            [dbo].[my_queue]
    ), TIMEOUT 1000;

    IF @@ROWCOUNT = 0
    BEGIN
        ROLLBACK TRANSACTION
        BREAK
    END
    
    SAVE TRANSACTION SavePoint
    -- do things
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF XACT_STATE() = 1
        ROLLBACK TRANSACTION SavePoint

    IF XACT_STATE() = -1
    BEGIN
        ROLLBACK TRANSACTION

        BEGIN TRANSACTION;

        RECEIVE TOP (1)
            @message_body = [message_body]
        FROM
            [dbo].[my_queue]
        WHERE
            conversation_handle = @conversation_handle
    END

    -- insert the message to a error log table

    END CONVERSATION @conversation_handle

    COMMIT TRANSACTION
END CATCH

My question: if this queue has multiple queue readers, will another queue reader B receive the poison message after queue reader A has rolled the transaction back, and reader A cannot receive the same message with the conversation handle given that all messages have their own conversation groups?

CodePudding user response：

will another queue reader B receive the poison message after queue reader A has rolled the transaction back

Yes. If you ROLLBACK the message is available on the queue for another reader to RECEIVE, and your conversation group lock is released.

So if you dequeue a message and end up in a doomed transaction or have XACT_ABORT ON, you have no choice but a complete rollback. Add to this the incompatibility with MultipleActiveResultSets, and savepoints are really not that useful.

So what to do? One option is to turn message retention ON the queue and commit the RECEIVE. Then on error write to your error table. The message body will be available both in the error table and by SELECT from the queue for the life of the conversation.

Or you could set MAX_QUEUE_READERS to 1 to prevent a concurrent activation procedure from dequeuing the poison message.

Or (untested) you could ROLLBACK, and re-RECEIVE the poison message while your session owns an exclusive Application Lock, and require the activation proc's initial RECEIVE to hold a shared lock on the same resource name.