Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sql Server Service Broker Conversation Groups

Can someone explain conversation groups in service broker?

Currently, I'm using service broker to send messages from one SQL server to another. On the sending server, I'm trying to correlate the messages so they are processed in serial on the receiving side. Based on the documentation, conversation groups seem to be a perfect fit for this, but on the receiving server, the messages get assigned to a different conversation group from the one I specified when sending the message.

I've search around the web and saw that this behavior seems to be intended (http://social.msdn.microsoft.com/forums/en-US/sqlservicebroker/thread/baf48074-6804-43ab-844a-cb28a6dce02b/), but then I'm confused about the usefulness of the syntax from (http://msdn.microsoft.com/en-us/library/ms178624.aspx)

WAITFOR( 
  GET CONVERSATION GROUP @conversation_group_id FROM [dbo].[ReceiveQueue]
)

If the conversation group doesn't come across with the message from the sender and messages sent with the same conversation group id don't have the same conversation group id on the receive side, what's the point of the code above?

like image 214
Brian Hasden Avatar asked Aug 21 '09 20:08

Brian Hasden


1 Answers

Conversation groups are a local primitve used for locking. Messages within a conversation group have no order guarantees, and conversation groups do not flow across the wire.

The message order is guaranteed by Service Broker within a conversation. So to preserve the order of corrleated messages in processing, send them on the same conversation.

Conversation groups are needed for groupping a set of conversations that are related to each other. Both GET CONVERSATION GROUP and RECEIVE verbs guarantee that they will lock an entire converstaion group, thus preventing any other thread from processing related messages. For example consider a traveling site. It receives a message with a request to book a holiday package. As a result it initiates a conversation with a hotel booking service and sends a request to reserve a room, it initiates a conversation with an airline reservation service and asks for travel reservation, it initiates a conversation with a car rental agency service and asks for a car reservation. These three new conversation it created are all in the same group with the initial conversation that the request was received on (the application has used the WITH RELATED_CONVERSATION clause of BEGIN DIALOG on all 3 of them). It then commits and proceed to process the messages in the queue. Later responses from these 3 correlated requests start comming in, at pretty much random times. Say the hotel resposnse comes in first. The message gets picked up by the applicaiton and it goes ahead to update the status of the request with the response from the hotel. At the same time, the airline response comes in. If another thread would be allowed to pick it up, it would try to update the status of the same request, thus resulting in blocking or even deadlock against the thread that is processing the hotel response. When the hotel response is processed, the thread commits and thus unlocks the whole conversation group, allowing for any thread (including itself) to pick up the airline response and process it.

like image 64
Remus Rusanu Avatar answered Oct 07 '22 00:10

Remus Rusanu