Data Access Application Block and Transaction Scope will separate database objects use the same connection

Question

I'm using System.Transactions and Transaction scope for my transaction handling in conjunction with the Enterprise Library Data Access Application Block.

In some cases I'm using separate instances of the Database class within the transaction. The connection is to the same database though.

I'm wondering if the application block will reuse the same connection and not span a distributed transaction in this case?

Answer 1

TransactionScope is a very strange beast. To my experience a DB transaction is promoted to a distributed one as soon as another connection (even to the same DB) gets open.

Answer 2

In a nutshell MSDN has your answer:

"Enterprise Library, on the other hand, normally opens and closes a connection for each request. This approach is incompatible with the way the TransactionScope class works. If there are multiple connections, the TransactionScope class considers the transaction to be a distributed transaction. Distributed transactions have a significant performance and resource overhead compared with a local transaction." (MSDN)

And

"To avoid this, the Database class methods, such as ExecuteDataSet, recognize when a TransactionScope instance is active and they enlist database calls in this transaction. If a transaction is currently active as a result of using a TransactionScope instance, the Database class methods use a single connection." (MSDN)

You don't mention what database you are using. In the Oracle 10g client I was using, it was the case that if you used TransactionScope you would always have a distributed transaction. It looks like this issue is now addressed in later releases.

You can check the answer by looking under Transaction Statistics.