See MSDN: You should also use the TransactionScope and DependentTransaction class for applications that require the use of the same transaction across multiple function calls or multiple thread calls. So maybe look into DependentTransaction – in particular, there is a worker thread example, here.
It was totally my fault and not fully understanding transactionscope. A connection is not automatically enlisted in transactionscope unless you open the connection within the transactionscope: Automatic Enlistment using (var scope = new TransactionScope()) { con.Open(); //update/delete/insert commands here … scope.Complete(); } Manual Enlistment con.Open(); using (var scope = new TransactionScope()) { con.EnlistTransaction(Transaction.Current); //update/delte/insert statements … Read more
It depends on the scope option you start the nested transaction scope with. If you use the default option TransactionScopeOption.Required then the nested scope will enlist in the same transaction as the outer scope and as such when the outer scope rolls back the inner scope will also be rolled back even if it has … Read more
There’s no way to implement it so far. But they work on it
Don’t forget to supress your select statements from your TransactionScope. In SQL Server 2005 and above, even when you use with(nolock), locks are still created on those tables the select touches. Check this out, it shows you how to setup and use TransactionScope. using(TransactionScope ts = new TransactionScope { // db calls here are in … Read more
The connection pool calls sp_resetconnection before recycling a connection. Resetting the transaction isolation level is not in the list of things that sp_resetconnection does. That would explain why “serializable” leaks across pooled connections. I guess you could start each query by making sure it’s at the right isolation level: if not exists ( select * … Read more
The fact Serializable is the default comes from times when .NET wasn’t even released (before year 1999), from DTC (Distributed Transaction Coordinator) programming. DTC uses a native ISOLATIONLEVEL enumeration: ISOLATIONLEVEL_SERIALIZABLE Data read by a current transaction cannot be changed by another transaction until the current transaction finishes. No new data can be inserted that would … Read more
After doing some research, it seems I cannot have two connections opened to the same database with the TransactionScope block. I needed to modify my code to look like this: public void MyAddUpdateMethod() { using (TransactionScope Scope = new TransactionScope(TransactionScopeOption.RequiresNew)) { using(SQLServer Sql = new SQLServer(this.m_connstring)) { //do my first add update statement } //removed … Read more
Make sure that the “Distributed Transaction Coordinator” Service is running on both database and client. Also make sure you check “Network DTC Access”, “Allow Remote Client”, “Allow Inbound/Outbound” and “Enable TIP”. To enable Network DTC Access for MS DTC transactions Open the Component Services snap-in. To open Component Services, click Start. In the search box, … Read more
I found out the answer in Entity Framework 6’s documentation: With the introduction of EF6, Microsoft recommends to use new API methods: Database.BeginTransaction() and Database.UseTransaction(). Although System.Transactions.TransactionScope is still very well supported, it is no longer necessary for most users of EF6. While Database.BeginTransaction() is used only for database related operations transaction, System.Transactions.TransactionScope, in addition … Read more