2

I am reading through SQL server books online for the first team whilst learning how to use the service broker and I came across this statement:

The application that implements the service reads the message, runs the query, and returns the results. All three of these operations take place in the same transaction. If a failure occurs before the transaction commits, the entire transaction rolls back and the message returns to the queue. When the computer recovers, the application restarts and processes the message again.

I am guessing that this will block in the same way as usual transactions? Ie. row/page locks. Am I correct? Is this something that I should worry about or just leave it until I have a problem?

1
3

When the service broker pulls messages from the queue using an activation stored procedure (or in any method) all the normal locking rules apply to the queue and the objects which are referenced in the code after the receiving of messages from the service broker queue.

Note that the information in BOL that you quoted is only true is you do the receive within an explicitly defined transaction. If you receive a message and there is no explicit transaction to rollback then the database can't put the message back in the queue.

1
  • thanks for this :) I thought as much, but it was a long session earlier and my brain is quiet functioning as it should ha! Aug 22 '11 at 21:30

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.