Ef and Transactionscope for Both SQL Server and Oracle Without Escalating/Spanning to Dtc

EF and TransactionScope for both SQL Server and Oracle without escalating/spanning to DTC?

First: never use var ts = new TransactionScope(). Is the one liner that kills your app. Always use the explicit constructor that let you specify the isolation level. See using new TransactionScope() Considered Harmful.

Now about your question: the logic not to promote two connections in the same scope into DTC relies heavily on the driver/providers cooperating to inform the System.Transactions that the two distinct connections are capable of managing the distributed transaction just fine on their own because the resource managers involved is the same. SqlClient post SQL Server 2008 is a driver that is capable of doing this logic. The Oracle driver you use is not (and I'm not aware of any version that is, btw).

Ultimately is really really really basic: if you do not want a DTC, do not create one! Make sure you use exactly one connection in the scope. It is clearly arguable that you do not need two connections. In other words, get rid of the two separate repositories in your data model. Use only one repository for Orders, Inventory and what else what not. You are shooting yourself in the foot with them and you're asking for pixie dust solutions.

Update: Oracle driver 12c r1:

"Transaction and connection association: ODP.NET connections, by default, detach from transactions only when connection objects are closed or transaction objects are disposed"

TransactionScope automatically escalating to MSDTC on some machines?

SQL Server 2008 can use multiple SQLConnections in one TransactionScope without escalating, provided the connections are not open at the same time, which would result in multiple "physical" TCP connections and thus require escalation.

I see some of your developers have SQL Server 2005 and others have SQL Server 2008. Are you sure you have correctly identified which ones are escalating and which not?

The most obvious explanation would be that developers with SQL Server 2008 are the ones that aren't escalating.

Entity Framework: Controlling db connection and specifying own transaction

Entity Framework 6 has two features which may help with this:

• Explicit Transaction Support
• Ability to create a DbContext from a DbConnection.

If you did want to use EF5, you'd need to use a TransactionScope:

var context = new MyContext();

using (var transaction = new TransactionScope())
{
    MyItem item = new MyItem();
    context.Items.Add(item);
    context.SaveChanges();

    item.Name = "Edited name";
    context.SaveChanges();

    transaction.Complete();
}

As mentioned in the linked article, you will need to reference System.Transactions to get TransactionScope.

Can TransactionScope in C# trigger rollbacks in stored procedures in SQL server?

Yes, TransactionScope is picked up by ADO.NET. If you catch any exceptions and call .Rollback(), everything will be rolled back.

There are fine details in the way it works (e.g. MSDTC can get involved), but what you are suggesting is perfectly valid and the correct way of doing what you want to achieve.

Same thing works for Oracle and other DBs supporting TransactionScope. You can also pass the transaction through a WCF service, where if more DBs are used they are made a part of the transaction, and so on.

With some low-level work, you can even include NTFS operations in the transaction. Windows API supports it, but not .NET without a wrapper, for NTFS.

EF: many-to-many why Clear() does not generate only one sql call?

dbContext.Database.ExecuteSqlCommand("DELETE FROM AB WHERE (A_UID = 1)");

is the only way to achieve this with a single SQL command. EF does not track if you have loaded/attached all or only a part of the related entities. You could call Clear in a situation like this...

var tempA = new A { UID = 1, B = new List<B>() }
tempA.B.Add(new B { UID = 2 });

using (var dbContext = new MyContext())
{
    dbContext.A.Attach(tempA);
    tempA.B.Clear();
    dbContext.SaveChanges();
}

...in which case deleting all entries from the join table for A.UID = 1 would be wrong because by clearing the collection you removed only the entity with B.UID = 2 but not with B.UID = 3.

C# controlling a transaction across multiple databases

It seems you may be re-inventing TransactionScope. Doing all this under a unit of work is straightforward*:

  using (TransactionScope scope = new TransactionScope())
  {
     ... Do Stuff with Connection 1 using SqlDataReader
     ... Do Stuff with Connection 2 using Entity Framework
     ... Do Stuff with Connection 3 on another Oracle Database
     ... And for good measure do some stuff in MSMQ or other DTC resource
     scope.Complete(); // If you are happy
  }

Stuff doesn't need to be inline at all - it can be in a different class, or a different assembly. There's no need to explicitly register database or queue connections with the TransactionScope - everything happens automagically, provided that the resources you use are able to enlist into an ambient transaction.

Now the small print:

• * Any time you use more than one database connection concurrently, or different connection strings, or multiple technologies, this will require 2 phase commit and escalate to a DTC transaction in order to ensure ACID across the resources. DTC itself has lots more small print and poses many more challenges in a corporate network, like firewalls, clustering, security configuration and bugs.

• However, with Lightweight transactions on MS Sql Server, if you can keep all your connections using the same database and same
  connection string settings, and close each connection before opening
  the next, then you can avoid
  DTC.

• Maintaining a transaction across multiple ACID resources will invariably maintain locks on these resources, until the transaction is committed or rolled back. This often doesn't make for good neighbourliness in a high volume enterprise, so be sure to consider the consequences of the locking.

• If the Stuff is done across multiple threads, you'll need to rope in DependentTransaction

• A last point worth mentioning is the default isolation level with TransactionScope is Serializable, which is prone to deadlocks. In most non-critical scenarios you'll probably be able drop this down to Read Committed.

---

Related Topics