Sql-server – Any problem with same conversation handle used by multiple services communicating with one target queue in a multiplexed service broker order queue

best practicesservice-brokersql serversql-server-2008

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!

Best Answer

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.

Related Question