CODEWITHSUNDEEP
sql-serverservice-broker
ibubi
ibubi

Reputation: 2539

Service Broker queue rollback on receive

As using base on here

Service Broker provides automatic poison message detection. Automatic poison message detection sets the queue status to OFF if a transaction that receives messages from the queue rolls back five times. This feature provides a safeguard against catastrophic failures that an application cannot detect programmatically.

I have a windows service application that polls SB queue and send them to a web service endpoint. Since, I should handle any "server goes-off" issues ─get back message to the queue, so I include "queue item receiving" and "queue item sending" methods into the same transaction. On the very first exception (HttpRequestException), I start pinging server for predefined timeout then continue/close program.

However, rolling back five times is a problem, I understand that whatever the time gap between 5 consecutive rollbacks it always increments rollback count globally, so the queue will be disabled eventually. Am I right on this? Does queue has a timeout for zeroing rollback count?

If this is the behaviour, is it better to exclude "queue item sending" method from transaction? If I do this, I should follow the approach that, on exception keep the message in another resource(table, file) to be sent later, or other alternatives...

What about using tables as queues to keep my transaction united and be freed from SB's rollback issue? Would it be as reliable as SB?

Upvotes: 1

Views: 1013

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

AFAIK, 5 consecutive rollbacks of the same message on a queue with POISON_MESSAGE_HANDLING = ON with will disable the queue regardless of the time gap.

Have you considered simply turning off poison message handling for the queue? The onus would then be on your application to distinguish between a true poison message (one that can never be successfully processed) versus a problem with an external service dependency. In the first case, you could log the problem message elsewhere and commit instead of rollback.

There are other patterns one could use, such as re-queuing the message and committing but much depends on whether messages must be processed in order.

Upvotes: 2

Related Questions