共用方式為

Behavior of BizTalk Adapters with Transactions

  • 發行項

Plenty of folks make the assumption that wrapping a couple BizTalk adapters in an atomic scope makes the entire scope behave transactionally. However, the adapter framework, and overall messagebox architecture actually make that a false assumption. When a message leaves an orchestration through an adapter, we simply publish a message to the messagebox. At that point, the actual adapter process may be running on a completely different machine, on a completely different thread. So in a scenario with two SQL adapters send shapes in an atomic scope, a transmission failure in the second shape does NOT mean that the first database operation automatically gets rolled back.

So how do you make database operations from an orchestration behave transactionally? There's a few ways I've seen.

  • Literally put the ADO.NET code in an orchestration Expression shape, then wrapped in an atomic transaction. If any of the operations fail, you should get default compensation.
  • Use a single stored procedure that accepts a set of data and rely on the database to manage the transaction
  • Use an updategram, although I haven't personally walked through a transaction scenario with one.
  • Use the standard SQL adapter and a mix of transactions and compensations.

All that said, I thought I'd run through that last case and a few scenarios to see what the outcome was with regard to adapters and transactions using this orchestration process:

Scenario #1
As you can see in the orchestration, I have two separate adapter calls within a (atomic) scope. If I keep the default Delivery Notification setting on the port (see below for more info), and intentionally cause an error in the stored procedure that Send_1 consumes, the next action, Send_2 still fires and updates my other database table. Probably not the intended outcome.

Scenario #2
So if I flip Delivery Notification on and the first send action fails, an exception is caused, and the orchestration suspends and the Send_2 action never fires.

NOTE: Much more articulate men than me have written on the coolness and function of delivery notification and the corresponding ACKs and NACKs. See Kevin Smith as an example. This is a fairly powerful feature of BizTalk Server.

Scenario #3
Let's say that Delivery Notification is on, and the *second* send action in our atomic scope fails. The first send, Send_1 was successfully fired, the database was updated, an delivery notification update (ACK) was received, thus allowing the orchestration to continue processing. When we receieve a NACK for the second send shape, the compensation block *does* fire, but the orchestration is suspended due to the error caused by the NACK.

Scenario #4
How about we wrap the atomic shape in a long running transaction which allows exception handling (unlike atomic shapes which only permit compensation)? I added a Compensation shape to the long running transaction's exception block, which then fired the compensating block associated with the atomic scope. And, since I actively catch the thrown exception, the orchestration doesn't get suspended.

So a moral of the story is that by using delivery notification, and compensating actions, you can make your orchestrations appear to behave transactionally with respect to adapters. And of course this doesn't just apply to the database adapter, but any sort of application/technology adapter. If I replace "SQL Adapter" with "SAP Adapter", the same principles apply. An SAP rollback step would be included in any compensation shape to remove SAP changes if a subsequent action in the atomic scope failed. If you choose to *not* use delivery notification, just be aware of the consequences of adapter transmission failures.

Comments

  • Anonymous
    July 14, 2005
    Could you expand on when to use transactions when interacting with database adapters? My impression from last week's discussions were that you don't use transactions at all when sending messages into SQL Server...
  • Anonymous
    July 14, 2005
    Hey Lucas. As usual, it depends. If you have operations in an orchestration that depend on database success, being able to capture ACKs and compensate for failure is huge. The value of using a transaction around the adapter is to compensate for failure and catch exceptions. It's not really adding any "transactional" capabilities except the ability to knowingly catch and handle problems.
  • Anonymous
    July 11, 2006
    How can I make two stored procedure calls from same orchestation?
  • Anonymous
    July 11, 2006
    You would generate SQL messages for each stored proc ("Add Generated Items ... SQL Adapter" and pass that through ports in separate actions in the orchestration.
  • Anonymous
    July 12, 2006
    The comment has been removed
  • Anonymous
    July 12, 2006
    The comment has been removed
  • Anonymous
    July 13, 2006
    I thought atomic tran will be rollbacked automatically.

    "xyz" is a dummy table name in the select query in my second stored proc, to make it fail.  

    what about suspended send port? Is it normall behaviour that biztalk will suspend send port in case of error in stored proc.

    How can I avoid error that biztalk is writing to event viewer

    Thanks
  • Anonymous
    July 13, 2006
    Remember the whole point of this post.  That is, nothing in an orchestration can automatically roll back an adapter transmission.  That's what compensation is for.  Depending on how you SQL communication is set up (request only or request/response), a send port error is exactly right.  If you simply have a "request only" submission, then the orchestration is done with the message, and it will only fail in the send transmission.  One way to still catch a failure in the sending of messages is to use "Delivery Notification."
  • Anonymous
    July 13, 2006
    My communication with SQL is one way (resquest only) i.e. I am just calling two stored procs. separatly.

    Just to clarify,  does send  transmission failure includes any error that occurs inside the stored proc ?

    I am using send port's "Delivilar Notification" =Transmitted and catching Microsoft.XLANGs.Core.DeliveryFailureException

    Thanks.
  • Anonymous
    July 13, 2006
    I don't recall if you get error info specific to what was thrown in the stored proc itself.
  • Anonymous
    January 21, 2009
    PingBack from http://www.keyongtech.com/324286-how-to-rollback-a-stored