Execute asynchronous code with Service Broker inside a transaction

Question

Hi,

I have a problem with executing asynchronous code with Service Broker inside a transaction.

Here is what I am trying to do:

begin transaction

 ...

 execute some service broker async code with internal activation on the target service( I don't have any transactions inside the activation procedure )

 ...

comit

The problem is that the activation procedure is not triggered because the queue associated to the target service is locked.

If I execute the some code outside the transaction everything works fine.

My question is: is it possible to execute async code inside a transaction ?

Thanks for help,

Ciprian Pavel

Answer 1

Hi Ciprian,

Could you post the sample asynchronous code? Why do you want to execute asynchronous code in one transaction?

Since in on transaction, we always execute synchronous components, and if there is something asynchronous, we start another transaction to execute.

A database transaction, by definition, must be atomic, consistent, isolated and durable. Database practitioners often refer to these properties of database transactions using the acronym ACID.

Transactions provide an "all-or-nothing" proposition, stating that each work-unit performed in a database must either complete in its entirety or have no effect whatsoever. Further, the system must isolate each transaction from other transactions, results must conform to existing constraints in the database, and transactions that complete successfully must get written to durable storage.

Please see:

http://blog.sqlauthority.com/2007/12/09/sql-server-acid-atomicity-consistency-isolation-durability/

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

I'm assuming that by "executing asynchronous code" you are referring to using service broker to execute tasks in parallel as was demoed in several blog posts on the net. If you do, the answer is "no, you can not execute asynchronous code within a transaction". As long as your process hasn't committed the transaction that contains the send call, the message hasn't been fully handed over to the Service Broker process. i.e. Service Broker will not be able to deliver it at the destination queue until you "let go" of the message. A similar thing happens on the target end: the receiving procedure can only receive a message when the service broker process has completely delivered the message at the target queue. You will have to commit the transaction after sending each 'job'.

This is not a bad thing though. Service Broker guarantees delivery of any and all messages sent. So you can with peace of mind commit the transaction after you've sent the message, relying on service broker to deliver the message at the target process. Think of SSSB as a perfect (sub)contractor that you delegate your tasks to (or as an army of contractors with max_queue_readers set to > 1): You decide what needs to be done, then hand the job over and you can rely on the contractor coming back to you when the job is done. In SSSB terms this means that once you've committed the send, service broker will take over and deliver the message at its destination for you. The procedure at the target end then takes the same care to not loose the message by enclosing the receive, the processing of the received message and the send of the reply message all together in a single transaction.

---

SQL expert for JF Hillebrand IT BV - The Netherlands.

Answer 3

Hi,

Thank you for your answer. Your post answered my question.

Ciprian