Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I asked a question about implementing conversation groups in a SQL Service Broker setup which has several User Services communicating with the Server Service (Processing xml data requests to be sent to an outside web service). But since conversation groups don't guarantee order in the processing, do you think there is anything wrong with instead using the same conversation handle for all the requests going to the same target queue (guaranteeing the order in which they were sent regardless of source)?

for the background: How would you implement conversation groups/ locking for multiple users of a Service Broker Queue?

As it was mentioned by Rusanu in the answer,

And don't forget that the only construct that guarantees order in SSB is a conversation. So if users have to send bids for A on conversation 1 followed by bids for B on conversation 2 then there is no guarantee that the bid for A is going to be processed after the bid for B is processed. The only guarantee is that if user 1 sends two bids for A, they will be processed in the order sent.

Also, if two different users send bids for product A then there is no guarantee of the order of processing of these bids. However, if the bids for product A end up on the same CG then there is a guarantee that only one 'processor thread' will see both bid from user 1 and bid for user 2, but be careful because there is no guarantee that the bids are presented in the RECEIVE result set in the order they were received. RECEIVE only guarantees that:

* all messages in the result set are from the same CG
* the messages belonging to a conversation are in order

but the order of conversations in the result is basically random (is the driven by the conversationhandle ordering, a GUID).

Like in a real auction, I would actually like for the queue items to be processed in the order they are arriving on the queue for user A, user B, and user C sending a "bid" for the same product. I am thinking of implementing this whole thing by using the same conversation handles for all the conversations on the same product.

What I think I will try so far:

The mechanism will be the server app sends a conversation starter (initialization) for N conversations to a User_0 (where N is the number of "multiplexed conversation channels" we will have)-- we store the conversation handles in a table sort of like this:

CREATE TABLE ConversationChannelStatus
(ConversationChannel_pk numeric Primary Key, ConversationHandle UNIQUEIDENTIFIER)

USER_A, when sending "bids", will send a message from User_A Service to the target service using the conversation handles that are saved in the ConversationChannelStatus Table (Indexed by a simple numeric primary key, the "ConversationChannels").

Is it problematic to store conversation handles in a table, then have multiple initiator services keep using the same conversation handles as they send message to the target queue?

Thank you.

Update: Sorry got confused with these words, Conversation ID, Conversation Handle. The same conversation handle can definitely not be used because it is different for initiator and target. I think I am referring to trying to use the same Conversation ID from multiple initiators to the same target to try to maintain order in queue processing messages coming from multiple services.

Update 2: Still working on it but I guess solution will be like this, involving both conversation handles and conversationgroupid.

  1. Start by initializing N conversation vs User 0, and store conversationgroupid as well as conversation handle both on the channel settings table.
  2. When a UserA, UserB, UserC Service wants to join, the server app will actually initiate the N conversations with each of the user services, using the conversation handles we stored earlier on the channel settings table.
  3. Then the User Services (the targets of that message) will receive that message & reply using the conversationgroupId's from the channel settings table only when the User Service has a "bid" to send. Thus providing an easy way for the user service to know which related conversation group id to use while maintaining the desired conversation order on the serverAPP Service queue processing of messages from users A, B, C (since it is replying to a specific conversation started by the serverapp service). I hope it works!
share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

Contention will be the biggest problem. A conversation guarantees order but in order for such guarantee to be give the SEND verb must lock the conversation handle used until the end of transaction (or during the statement if no explicit transactions is used). In effect that means that only one transaction can send a bid on product A at any time. Whether this is acceptable or not depends entirely on your business requirements.

The terms: a conversation has two endpoints, the initiator and target. Each conversation has an unique ID, the conversation_id (a guid) which has the same value on both endpoints (ie. it travels on the wire with the messages). Each endpoint has its own handle, the conversation_handle (again, a guid) which you use with SSB verbs. the handle is local only and never travels with the message. So you got it right in your update ;)

I think what you describe in Update 2 is OK, but there as always the devil is in the details. I suggest you start experimenting with prototype code and exchanging real messages, you will get a much better feel once you see it working.

share|improve this answer
    
Thanks, I think it will work as well, will have to design many things around it still but just needed the confidence to know that it was a feasible plan since it's the first time to implement this framework. Thank you for the analysis. –  fa1c0n3r Jun 8 '12 at 0:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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