Question

Leila S on Sun, 19 Oct 2014 20:28:18


Hi all,

I am sending two messages from Service1 to Service2. The first message indicates DELETE command to delete a row, the second message has the information for INSERTing a new row. Actually it's the process of updating a row but splitted into two phases (Delete/Insert at the target).

When I check the transmission queue at Service1 (initiator), my messages are queued correctly: First delete, Second insert.

But when they are delivered to Service2, they are not necessarily in the same order. Sometimes the Insert message is delivered earlier, that causes duplication of PK in my table.

How can I instruct SSB to deliver these messages to Service2 at the same order as they are queued in TQ at Service1?

Any help would be greatly appreciated.

Leila 


Sponsored



Replies

Dan Guzman on Sun, 19 Oct 2014 20:57:33


How can I instruct SSB to deliver these messages to Service2 at the same order as they are queued in TQ at Service1?

Messages are delivered in order within the same conversation.  If you are receiving messages other than the order in which they were sent, it seems the delete and insert messages are in a different conversation.  Save the conversation handle returned on the BEGIN DIALOG statement (perhaps in a state table) and reuse it for the subsequent related messages.

Erland Sommarskog on Sun, 19 Oct 2014 21:16:36


I guess it also matters how many queue readers you have. As long as you have a single queue reader, they should come in order. If you have multiple queue readers, things can get interesting if the messages are picked up different processes.

Leila S on Mon, 20 Oct 2014 06:04:24


 it seems the delete and insert messages are in a different conversation. Save the conversation handle returned on the BEGIN DIALOG statement (perhaps in a state table) and reuse it for the subsequent related messages.

Hi Dan,

Yes we use two different conversations. If I reuse the handle, my messages become member of a group and I should read from queue in a different way?

Leila S on Mon, 20 Oct 2014 06:07:15


I guess it also matters how many queue readers you have. As long as you have a single queue reader, they should come in order. If you have multiple queue readers, things can get interesting if the messages are picked up different processes.

Hi Erland,

Before letting the service program to pick the messages (disabled SP), I query the queue and see that order of messages are not correct, while they have had correct order in TQ of initiator.

Erland Sommarskog on Mon, 20 Oct 2014 07:43:24


Exacly how does your SELECT statement look like?

Leila S on Mon, 20 Oct 2014 09:28:12


Exacly how does your SELECT statement look like?

So Simple (at Service2):

SELECT * FROM MyTargetQ


Erland Sommarskog on Mon, 20 Oct 2014 11:41:52


So there is no ORDER BY clause. How do you deduce that one message comes before another? What happens when you RECEIVE? (Which you can do with rollback.)

Dan Guzman on Mon, 20 Oct 2014 21:39:56


By default, all messages in the conversation will be part of the same conversation group. The conversation group is the level of locking SB users to ensure only a single queue reader will dequeue the message.  Reusing the same conversation handle will guarantee messages are received in the order sent.


Dan Guzman, SQL Server MVP, http://www.dbdelta.com


Leila S on Tue, 21 Oct 2014 06:25:34


So there is no ORDER BY clause. How do you deduce that one message comes before another? What happens when you RECEIVE? (Which you can do with rollback.)

I looked to "queuing_order" column in the resultset.

The RECEIVE behaves according to queuing_order column.