Locking data access and transaction scope will share database objects using the same connection
I am using System.Transactions and Transaction capabilities to handle transactions in conjunction with the Enterprise Library Data Access Application Block.
In some cases, I use separate instances of the Database class in a transaction. The connection belongs to the same database.
I am wondering if the application block would reuse the same connection and not propagate a distributed transaction in this case?
a source to share
In a nutshell MSDN has your answer:
"An enterprise library, on the other hand, typically opens and closes a connection for each request. This approach is incompatible with how the TransactionScope class works. If there are multiple connections, the TransactionScope class considers the transaction to be a distributed transaction. Distributed transactions have significant performance and overhead compared to with a local transaction ". (MSDN)
AND
"To avoid this, database class methods such as ExecuteDataSet recognize when a TransactionScope instance is active and they end calls to the database in that transaction. If a transaction is currently active as a result of using a TransactionScope instance, the database class methods use the same connection ". (MSDN)
You don't specify which database you are using. In the Oracle 10g client I used, it was the case that if you were using TransactionScope, you would always have a distributed transaction. It looks like this issue has now been fixed in later versions .
You can check the answer by looking at Transaction Statistics .
a source to share