Service Broker Check after Receive?
in [Programming SQL]
|
From: SnapDive on 29 Jun 2010 23:02 If this is the wrong place to ask, please redirect me. I have a table with an after insert trigger that takes some stuff from the OUTPUT clause, converts it into XML, and then dumps that into a Service Broker queue. I have an external activation queue setup so the message can get sent to an external .NET app listening for work. The .NET app may not get the work done, and it knows it internally. What is the best way to talk back to the queueing infrastructure so the previously-received item can get re-queued and re-done by the .NET app again? Thanks.
From: Jeroen Mostert on 30 Jun 2010 02:30 On 2010-06-30 5:02, SnapDive wrote: > If this is the wrong place to ask, please redirect me. > There's a separate service broker newsgroup, but it sees practically no activity, so you might as well come here. > I have a table with an after insert trigger that takes some stuff from > the OUTPUT clause, converts it into XML, and then dumps that into a > Service Broker queue. I have an external activation queue setup so the > message can get sent to an external .NET app listening for work. > > The .NET app may not get the work done, and it knows it internally. > What is the best way to talk back to the queueing infrastructure so > the previously-received item can get re-queued and re-done by the .NET > app again? > There are multiple approaches: - You can do the RECEIVE and the processing in a transaction and roll it back if you can't process in time. The drawback of this is that you hold open a transaction for the duration of processing. I don't know if this can prevent SEND from working (I don't think so, but I've never tried it), if it can that's obviously a major problem. Even without blocking SEND, this limits concurrency -- you can't really have more than one queue reader active with this approach. This solution guarantees that you will process requests strictly in order, which may or may not be required. - Receive as normal and if you're not done in time, do a SEND on the queue just like the trigger would. This means messages are no longer processed in-order. The "churn" you cause if most or all messages need to be reprocessed this way and the timeout is short can seriously hamper performance, as your SENDs will compete with the trigger's, but unlike the previous solution there's no problem with multiple readers. - As before, but do a SEND not on the original queue but a special retry queue. This allows processing on the original queue to continue smoothly, which improves concurrency, and you can set different activation policies on the retry queue. Drawback is that you need a separate queue with separate services and separate conversations, which means more administration. For doing the SEND on the proper conversation in the last two solutions I'd pass the conversation handle to send retries on in the message itself so you keep the queue details in the database (and not hard- or soft-code the handle in the application). All of these solutions have problems with poison messages: if your application is never finished in time because it can't (the data isn't processed correctly due to a bug, or it will simply always take longer than you allow) the queue will grow without bounds. To combat this, you can include a time stamp and/or retry count with the message so you can discard it if it goes unprocessed for too long. If discarding messages is not an option, monitor the queue size so you can manually intervene if processing is too slow. -- J.
From: SnapDive on 30 Jun 2010 20:43 I think this is the tactic I'll try since it keeps the number of queues to a minimum. The processing order does not matter. Timing doesn't matter either now that I think on it more... The console app is going to launch some stuff on a background thread, and I want to put that in a try/catch, so I guess if I have to catch I'll delete the original table row, insert it again, which will fire the trigger which will load the target queue which will send to the event queue. I was going to use this code as a base for mine: http://webcache.googleusercontent.com/search?q=cache:xp8JWDnTOXQJ:rusanu.com/2007/04/11/consuming-event-notifications-from-clr/+%2BPostEventNotification+%22using+system%22&cd=1&hl=en&ct=clnk&gl=us&client=firefox-a However, this is mentioned: " One thing to keep in mind is that the Event Notifications are going to be fired even when the application is not running. So the application might start up and find a number of notifications pending from the period was not running and has to be prepared to deal with this case. " I'm not sure what that means, based on the code, wouldn't the previous notifications just be "missed"? Thanks. On Wed, 30 Jun 2010 08:30:52 +0200, Jeroen Mostert <jmostert(a)xs4all.nl> wrote: > >- Receive as normal and if you're not done in time, do a SEND on the queue >just like the trigger would. This means messages are no longer processed >in-order. The "churn" you cause if most or all messages need to be >reprocessed this way and the timeout is short can seriously hamper >performance, as your SENDs will compete with the trigger's, but unlike the >previous solution there's no problem with multiple readers.
From: Jeroen Mostert on 1 Jul 2010 03:08 On 2010-07-01 2:43, SnapDive wrote: > I think this is the tactic I'll try since it keeps the number of > queues to a minimum. The processing order does not matter. Timing > doesn't matter either now that I think on it more... The console app > is going to launch some stuff on a background thread, and I want to > put that in a try/catch, so I guess if I have to catch I'll delete the > original table row, insert it again, which will fire the trigger which > will load the target queue which will send to the event queue. > > I was going to use this code as a base for mine: > > http://webcache.googleusercontent.com/search?q=cache:xp8JWDnTOXQJ:rusanu.com/2007/04/11/consuming-event-notifications-from-clr/+%2BPostEventNotification+%22using+system%22&cd=1&hl=en&ct=clnk&gl=us&client=firefox-a > Event notifications are a particular application of service broker. You don't need to use them in the scenario you describe (a trigger inserting a message into a queue). You can simply do a SEND in the trigger and a RECEIVE in the application (with the [DEFAULT] contract or a schema of your own). > However, this is mentioned: > " > One thing to keep in mind is that the Event Notifications are going to > be fired even when the application is not running. So the application > might start up and find a number of notifications pending from the > period was not running and has to be prepared to deal with this case. > " > > I'm not sure what that means, based on the code, wouldn't the previous > notifications just be "missed"? > No. The queue will be filled with messages regardless of whether someone's there to receive them. You may be confusing event notifications with query notifications, another application of Service Broker which does work this way: notification events are only produced if an application is "listening". In .NET, this is implemented through the SqlDependency class. It's a little finicky to use, but on the plus side, it doesn't require you to create explicit triggers or even anything specific to Service Broker (this is all done under the hood). -- J.
From: SnapDive on 1 Jul 2010 15:44
Very cool, thanks for the insight. I was mixing up technologies in my head. I have the basics working. Thanks! My follow-up question is - How can I specify that I only want to receive a message of a particular type? Right now everything is loose but as I get more clues I will have multiple queriers each going into the same queue and I don't want them to get messages that are not created for their consumption. Thanks again! On Thu, 01 Jul 2010 09:08:42 +0200, Jeroen Mostert <jmostert(a)xs4all.nl> wrote: >> However, this is mentioned: >> " >> One thing to keep in mind is that the Event Notifications are going to >> be fired even when the application is not running. So the application >> might start up and find a number of notifications pending from the >> period was not running and has to be prepared to deal with this case. >> " >> >> I'm not sure what that means, based on the code, wouldn't the previous >> notifications just be "missed"? >> >No. The queue will be filled with messages regardless of whether someone's >there to receive them. You may be confusing event notifications with query >notifications, another application of Service Broker which does work this >way: notification events are only produced if an application is "listening". >In .NET, this is implemented through the SqlDependency class. It's a little >finicky to use, but on the plus side, it doesn't require you to create >explicit triggers or even anything specific to Service Broker (this is all >done under the hood). |