Broker from a .NET application - nested transactions and routing failures to a separate queue

Question

I'm planning to use service broker to process some pieces of data in a multi-step process.  Basically grab an item and then do several steps of processing.  At each step, if the processing succeeds, move on to the next step, otherwise move to a FailedStep#X type of state.

For service broker this seems like I'd need one queue for each step and also a Failed processing queue for each step.  To make up some examples

1. Grab items from a feed and shove into queue ItemsToParse

2. externally activated app on ItemsToParse -> Read from items to parse

 - if parsing succeeds, put parsed item into ItemsToProcess

 - if parsing fails, put item into ItemsThatFailedParse

3. externally activated app on ItemsToProcess -> Read from items to process

 - if processing succeeds, done

 - if processing fails, put item into ItemsThatFailedProcessing.

Hopefully that makes sense.  We'll have monitoring reports / code / etc watching the failed queues so that the issues can be corrected and the messages removed from that queue and potentially placed back into the main one to be attempted again.

The issue I'm running into is how to manage transactions for this scenario, especially since our processing is all going to be in an externally activated .net app.  The processing we do takes several forms:

1. pure .net code

2. database related stuff

The general pattern should be something like

begin transaction

    do a receive

    if no records commit and we're done

    try processing, then write to next queue

    catch exception, send item to failed queue

commit

For processing that is just .net code that works fine.  For example, parsing our item throws, so we send the item to the Failed queue and commit the transaction containing the receive.

The problem comes in when we have processing that also needs to do database work (let's say inserting a few rows into a few different tables).  I want the database work to be transactional.  The inserts may fail (let's say with a FK violation).  I need to detect this failure so I can write the item to the failure queue.  Also, sql ignores nested COMMITs.

Hopefully I've explained the problem.  I can't use nested transactions, because the nested COMMMIT is ignored, so the commit of the receive would be what triggers the db inserts and causes the FK violation, but then the receive is rolled back and I've lost the lock on the message I want to move to my Failed queue.

Is there some pattern I'm missing here?  This doesn't seem like an uncommon scenario.  Let me know if I haven't explained things clearly.

Thanks

Answer 1

anyone has ideas?

I would recommend to submit feedback to the Microsoft Connect at this link https://connect.microsoft.com/SQLServer/Feedback.

Answer 2

Your general pattern sounds right. To everything in one transaction and commit.

If the database you insert into is on the same SQL server as the queues then use a single connection and it should work. If it is on a different server, then you will need to use a distributed transaction to keep it all together. No need for nested transactions. Just one single (distrubuted)  transaction.

Note that, some SQL Server errors can make the transaction uncommitable, so you won't be able to write to the failed queue. In this case, you should rollback the transaction, then recieve the mesage again in a new transaction and move it to the failed queue without processing.

Answer 3

The problem comes in when we have processing that also needs to do database work (let's say inserting a few rows into a few different tables).  I want the database work to be transactional.  The inserts may fail (let's say with a FK violation).  I need to detect this failure so I can write the item to the failure queue.  Also, sql ignores nested COMMITs.

One approach is to use different connections/transactions for the service broker message processing (which will normally be committed) and a different one for the database processing (which may be committed or rolled back). 

---

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

Answer 4

Yes - of course - that work much better in .net code. 

(I usually do database work so I missed that.)