One day, I hope to be able to fully understand and explain the “Ambient Transaction” true/false option in BizTalk WCF-SQL and WCF-Custom send and receive ports. Until then, I’m going to collect the most relevant info and blogs here:
Specifies whether the SQL adapter performs the operations using the transactional context provided by the caller. Default is true, which means that the adapter always performs the operations in a transactional context. If there are other resources participating in the transaction, and SQL Server also joins the transaction, the transaction gets elevated to an MSDTC transaction.
However, there can be scenarios where you do not want the adapter to perform operations in a transactional context. For example:
While performing a simple Select operation on a SQL Server database
While specifying a polling statement that performs a Select operation, and does not involve any changes to the table either through a Delete statement or by invoking a stored procedure.
Both these operations do not make any updates to the database table and, hence, elevating these operations to use an MSDTC transaction can be a performance overhead. In such scenarios, you can set the binding property to false so that the SQL adapter does not perform the operations in a transactional context.
Not performing operations in a transactional context is advisable only for operations that do not make changes to the database. For operations that update data in the database, we recommend setting the binding property to true; otherwise you might either experience message loss or duplicate messages, depending on whether you are performing inbound or outbound operations.
http://msdn.microsoft.com/en-us/library/ms973865.aspx (Richard Seroter above refers to the “Ambient Transaction” feature as being part of the Systems.Transaction in .NET Framework 2.0 and afterwards. It seems like understanding Ambient Transactions from native C# first would be key to understanding how it works in BizTalk.
The above link provides the following information, but as for me now, it does not pass the “so what” and the “WIIFM” (What’s In It for Me) test.
System.Transactions defines a concept called an ambient transaction. The ambient transaction is the transaction that is present in the thread that the current application code is executing within. To obtain a reference to the ambient transaction call the static Current property of Transaction:
If there is no ambient transaction, Current will return null.
The ambient transaction object is stored in the thread local storage (TLS). As a result, when the thread winds its way across multiple objects and methods, all objects and methods can access their ambient transaction.
Later it says:
The value of TransactionScopeOption lets you control whether the scope takes part in a transaction, and if so, whether it will join the ambient transaction or become the root scope of a new transaction.
A TransactionScope object has three options:
Join the ambient transaction.
Be a new scope root, that is, start a new transaction and have that transaction be the new ambient transaction inside its own scope.
Do not take part in a transaction at all.
This forum post tries to answer the question: “What exactly is an ambient transaction?”
There are 2 main kinds of transactions; connection transactions and ambient transactions. A connection transaction (such as SqlTransaction) is tied directly to the db connection (such as SqlConnection), which means that you have to keep passing the connection around – OK in some cases, but doesn’t allow “create/use/release” usage, and doesn’t allow cross-db work.
The alternative is an ambient transaction; new in .NET 2.0, the TransactionScope object (System.Transactions.dll) allows use over a range of operations (suitable providers will automatically enlist in the ambient transaction). This makes it easy to retro-fit into existing (non-transactional) code, and to talk to multiple providers (although DTC will get involved if you talk to more than one).
Note here that the two methods can handle their own connections (open/use/close/dispose), yet they will silently become part of the ambient transaction without us having to pass anything in.
If your code errors, Dispose() will be called without Complete(), so it will be rolled back. The expected nesting etc is supported, although you can’t roll-back an inner transaction yet complete the outer transaction: if anybody is unhappy, the transaction is aborted.
The other advantage of TransactionScope is that it isn’t tied just to databases; any transaction-aware provider can use it. WCF, for example. Or there are even some TransactionScope-compatible object models around (i.e. .NET classes with rollback capability – perhaps easier than a memento, although I’ve never used this approach myself).
Blog on on the topic: Transaction.Current and Ambient Transactions by Florin Lazar:
A transaction which automatically identifies a code block that needs to support a transaction without explicitly mentioning any transaction related things. An ambient transaction is not tied just to a database, any transaction aware provider can be used. TransactionScope implements an ambient transaction. If you see the use of TransactionScope, you will not find transaction related anything sent to any method or setting any property. A code block is automatically attached with the transaction if that code is in any TransactionScope. A WCF transaction is another example of a transaction aware provider. Any one can write a transaction aware provider like the WCF implementation.
WCF-SQL and DTC Ambient Transactions in a cross domain scenario
The ambient transaction option ensures that the BizTalk adapter flows a transaction through to SQL Server and thus the SQL transaction will only commit when the message received by BizTalk is successfully written to the BizTalk message box database. This is of course crucial in a guaranteed delivery based solution where you can’t afford to lose any messages.
I found that all worked well when ambient transactions were turned off, however when turned on it looked like the receive location just hangs, holding a lock on SQL resources (I tried to do a select on the table in question using SQL Server Management Studio and it couldn’t return any values due to locks being in place) which won’t be removed until the host instance is reset.
The above blog talks about using DTCPing and DTCTester to identify DTC issues, and fixing them by laxing the security options in the DTC configuration.
Conclusions (or lack thereof):
So for now, I suggest studying that second web page to get an idea of what the topic is really about. If I had time, I would write some C# programs to test outside of BizTalk.