5

I regularly see code that looks like this:

BEGIN TRANSACTION;
BEGIN DIALOG CONVERSATION @u....
SEND ...
COMMIT;

BOL for BEGIN DIALOG doesn't use the transaction handling in the examples and it doesn't mention transactions at all.

Are transactions needed? If so why?

1

You are correct that the BEGIN DIALOG CONVERSATION does not specify using a TRANSACTION for starting a Service Broker dialog. But many developers use them as a standard practice.

For example, the 2008 R2 TechNet page Beginning a Conversation and Transmitting Messages seems to recommend BEGIN TRANSACTION and COMMIT TRANSACTION (or ROLLBACK) as a good practice.

This topic Configuring Service Broker for Asynchronous Processing also shows transactions being used.

These multi-step examples that use transactions do so for the same reason transactions are used in other code. Namely to ensure either a complete transaction or else a rollback to a consistent state.

Of course, you can decide if your Service Broker dialog is simple enough that you do not need a transaction. But that is up to you to decide.

  • Are you really saying this is an option? Nothing bad happens because a transaction isn't used? I don't know the internals of BEGIN DIALOG and SEND and how that interacts with a RECEIVE that is in a transaction. Is there a potential for blocking on system tables. i have seen this wait_type and wonder if it can be caused by not using a transaction on the BEGIN/SEND. SERVICE_BROKER_TRANSMISSION_WORKTABLE – dan holmes Dec 3 '15 at 15:26
  • @danholmes Along with your question in the previous comment, I am curious as to the consequence of not using a transaction. Meaning, can some info be added to this answer related to what happens if, with no BEGIN TRAN, the BEGIN DIALOG CONVERSATION succeeds but the SEND fails for some reason. Is something left in a bad state in the Service Broker queue tables? – Solomon Rutzky Dec 3 '15 at 15:29
  • @danholmes - No, I would use a transaction. Just saying that it "works" without a transaction, if that is what you want. – RLF Dec 3 '15 at 21:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.