SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


messages arriving in an odd order


messages arriving in an odd order

Author
Message
notoriousdba
notoriousdba
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 80
Hi,

I have a setup where I'm using triggers to capture data changes in a few tables in one database, and send those changes over to another database via service broker. The basic setup works fine, except that occasionally changes that I know had to have happened in a certain order in the source database are arriving in a different order at the target database. For example, let's say I was capturing changes to people, orgs, and the relationships between people and orgs. A user is adding a new person in the source database, then linking that new person to an org. On the target database I'm sometimes getting the message to link the person to an org before I get the message adding the person, even though that's not possible. Any idea how something like this could happen?

FWIW, both databases are SQL Server 2008.
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8565 Visits: 7660
Different queues will not operate in tandem. The broker overall doesn't operate by timestamps, but by queues. My guess is each of these structures have a different queue on your source system.

The only way to avoid hierarchal interruptions is to use conversations to move data in the correct hierarchal order.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
notoriousdba
notoriousdba
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 80
No, no different queues. All messages are going into the same service in the source database, to be delivered to the same service on the target database.

Let me be more specific:

1) The app calls a proc to add a new person.
2) The proc inserts a new record in the person table.
3) A trigger on the person table sees the insert and sends a message via service X to service Y to add the new person.
4) The app calls a proc to link the new person to an org.
5) The proc inserts a new record in an xref table linking the person to the org.
6) A trigger on the xref table sees the insert and sends a message via service X to service Y to add the new link.
7) When the messages arrive in the queue for service Y in the target database, sometimes the message to add the new person arrives after the message to add the link between the new person and an org.

Clearly the message to add the link couldn't be sent before the message to add the user, so given that service broker guarantees that messages are delivered in the order they are sent, I'm a bit nonplussed as to how they are arriving in a different order than it seems they logically should.
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2433 Visits: 3575
Are you sending the messages within the same conversation? I'm pretty sure that only messages within the same conversation are guaranteed to be received in the order they were sent.
venoym
venoym
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1459 Visits: 2082
notoriousdba (10/25/2011)
No, no different queues. All messages are going into the same service in the source database, to be delivered to the same service on the target database.

Let me be more specific:

1) The app calls a proc to add a new person.
2) The proc inserts a new record in the person table.
3) A trigger on the person table sees the insert and sends a message via service X to service Y to add the new person.
4) The app calls a proc to link the new person to an org.
5) The proc inserts a new record in an xref table linking the person to the org.
6) A trigger on the xref table sees the insert and sends a message via service X to service Y to add the new link.
7) When the messages arrive in the queue for service Y in the target database, sometimes the message to add the new person arrives after the message to add the link between the new person and an org.

Clearly the message to add the link couldn't be sent before the message to add the user, so given that service broker guarantees that messages are delivered in the order they are sent, I'm a bit nonplussed as to how they are arriving in a different order than it seems they logically should.


1. Queue processing is guaranteed to be in order by conversation on the receiving side. If you have 100 messages, each with their own conversation, you have not guarantee as to the order processed on the receiving side.
2. If this is using 1 conversation we need much more information to determine cause. i.e. Queue definitions, sender code, receiver code, etc.
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8565 Visits: 7660
venoym (10/26/2011)
1. Queue processing is guaranteed to be in order by conversation on the receiving side. If you have 100 messages, each with their own conversation, you have not guarantee as to the order processed on the receiving side.


To confirm this:
http://msdn.microsoft.com/en-us/library/ms166053.aspx
Applications receive messages from the queue for the service. For each conversation, queues return messages in the order in which the sender sent the message. All the messages returned from a single receive operation are part of conversations that belong to one conversation group. In effect, a queue holds sets of related messages, one set for each conversation group.


notoriousdba (10/25/2011)

1) The app calls a proc to add a new person.
2) The proc inserts a new record in the person table.
3) A trigger on the person table sees the insert and sends a message via service X to service Y to add the new person.
4) The app calls a proc to link the new person to an org.
5) The proc inserts a new record in an xref table linking the person to the org.
6) A trigger on the xref table sees the insert and sends a message via service X to service Y to add the new link.
7) When the messages arrive in the queue for service Y in the target database, sometimes the message to add the new person arrives after the message to add the link between the new person and an org.


Add in a handling message or a wait. What's a handling message...

In the contract, include two new message types. Have one be a non-exist Person Request. That request on the conversation will be sent back to the originating queue. On pickup of that in the conversation (which will include the original request for the association), it will resend both the necessary Person data AND the association data, allowing the Person to be built...

... and if you do that, you'll have to decide on the option of removing the Person send at all or not. If there's no association, do you need the Person?


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
notoriousdba
notoriousdba
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 80
Craig,

Ah, that's an excellent idea. I should have thought of that. Thanks!

BTW, no, the messages being sent aren't in the same conversation, but what I found is that this is happening because the app calling the procs has them wrapped in its own transaction. Without the outer transaction surrounding the two procedure calls, things work the way I expect them to. I'm trying to make this whole setup less brittle, so that things like this wouldn't have any effect. I tried forcing all the messages to be part of the same conversation, and that worked for a while, but has broken down for reasons I haven't figured out yet.

Clearly this process isn't as simple as I had hoped, and I'm probably going to have to do some major re-writing. One option I'm looking at is based on the "Real-time Data Integration" sample code in the Microsoft SQL Server Community Samples: Service Broker project on codeplex. That code has the triggers on the tables simply send messages that some data has changed, not send the actual data itself, then the receiving service uses Change Tracking to find any changed data and send it along to the target system. I'd hoped to not have to build something that elaborate, but it looks like I don't have much choice.

-Chris
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8565 Visits: 7660
notoriousdba (11/3/2011)

Craig,

Ah, that's an excellent idea. I should have thought of that. Thanks!

My pleasure. Sorry it's taken me a while to get back to this, was down ill for a bit.

BTW, no, the messages being sent aren't in the same conversation, but what I found is that this is happening because the app calling the procs has them wrapped in its own transaction. Without the outer transaction surrounding the two procedure calls, things work the way I expect them to.

Has... what... wrapped in the transaction? Sorry, you lost me there.


I'm trying to make this whole setup less brittle, so that things like this wouldn't have any effect. I tried forcing all the messages to be part of the same conversation, and that worked for a while, but has broken down for reasons I haven't figured out yet.

That can be tough to do, and you usually want broker to handle the conversation associations, but you can pull it off if you're careful. We'd have to go through your process with a fine toothed comb though to find the break.

Clearly this process isn't as simple as I had hoped, and I'm probably going to have to do some major re-writing. ... I'd hoped to not have to build something that elaborate, but it looks like I don't have much choice.
-Chris


There's one other choice... turn off the foreign key association and let the database be eventually consistent.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search