When using TransactionScope spanning multiple connections to the same database ODP.NET always promotes to a distributed transaction. This has
caused much confusion in the .net community so much so that it has been finally fixed in when using .NET with SQL Server 2008 R2+. You can now
have code like this without escalating to a distributed transaction
------ The way it works with .net and sql server 2008 R2 (works fine)
using (var ts = new TransactionScope())
{
// create order open/close connection to DB1
_orderRepository.CreateOrder(order);
// update inventory open/close connection to DB1
_inventoryRrepository.UpdateInventory(inventory);
ts.Complete();
}
------ The way it works with .net and Oracle 11g
using (var ts = new TransactionScope())
{
// create order open/close connection to DB1
_orderRepository.CreateOrder(order);
// update inventory open/close connection to DB1 (*either promotes to distributed transaction or gives an error if using local transactions!!!!*)
_inventoryRrepository.UpdateInventory(inventory);
ts.Complete();
}
I was wondering if anybody could tell me if this is going to be fixed with the next version of ODP.NET ? This has been a long outstanding issue that needs to be addressed. See the post
below from a year ago that gives more details.
9455425
Are TransactionScope transactions spanning multiple connections to the same database always promoted to distributed transactions with the newest version of Oracle?
My Tests seem to confirm this is happening.
From the documentation it is not clear to me, if promotion is meant to happen with multiple connections to different database servers only or to different databases in the same server or even to the same database.
I would prefer this to not escalate:
Using tsc As New TransactionScope
Foo
...
Bar
End Using
Sub Foo
Using cnx As New OracleConnection("Data Source=//Server1/orcl1")
...
End Using
End Sub
Sub Bar
Using cnx As New OracleConnection("Data Source=//Server1/orcl1")
...
End Using
End Sub
MS SQL Server 2008 (in contrast to 2005) only escalates a transaction spanning multiple connections to a distributed transaction when the connections are nested.
I consider this important, because I would like to use TransactionScope as a business layer contruct and and managing connections does not belong in business layer: To avoid unwanted escalation I would have to use a global connection or write a connection manager (see Book: Expert C# 2008 Business Objects, ConnectionManager, ContextManager).