Nested transactions are returned, although TransactionScopeOption.Suppress

Question

I want to cancel a transaction named "scope", but an internal transaction named scope2 should not rollback. But they both roll back !!! TransactionScopeOption.Suppress option does not work ...

I have already enabled DTC and I am using Visual Studio 2010 with .net 4.0 and Microsoft SQl Server 2008. What is wrong here ???

using (SqlConnection conn = new SqlConnection(@"Data Source=.\DEVELOPER;Initial Catalog=TestDatenbank;Integrated Security=sspi")) { using (TransactionScope scope = new TransactionScope()) { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = "UPDATE Orders SET ID='111'"; cmd.ExecuteNonQuery(); using (TransactionScope scope2 = new TransactionScope(TransactionScopeOption.Suppress)) { SqlCommand cmd1 = new SqlCommand(); cmd1.Connection = conn; cmd1.CommandText = "UPDATE Orders SET OrderName='aaa'"; cmd1.ExecuteNonQuery(); scope2.Complete(); } //scope.Complete(); } } 

Thank you for your responses!

I finally work !!!!

 using (SqlConnection conn = new SqlConnection(@"Data Source=.\DEVELOPER;Initial Catalog=TestDatenbank;Integrated Security=sspi")) { using (TransactionScope scope = new TransactionScope()) { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = "UPDATE Orders SET ID='111'"; cmd.ExecuteNonQuery(); using (SqlConnection conn2 = new SqlConnection(@"Data Source=.\DEVELOPER;Initial Catalog=AdventureWorks;Integrated Security=sspi")) { using (TransactionScope scope2 = new TransactionScope(TransactionScopeOption.Suppress)) { conn2.Open(); SqlCommand cmd1 = new SqlCommand(); cmd1.Connection = conn2; cmd1.CommandText = "UPDATE Henrik SET ID='111'"; cmd1.ExecuteNonQuery(); scope2.Complete(); } } //scope.Complete(); } } 

One strange thing is when I delete the line scope2.Complete (); and replace //scope.Complete (); with scope.Complete (); I expected the following:

area will be executed

scope2 will lean back

but actually: the scope will be executed scope2 will also be executed

Any ideas ???????

Answer 1

I don’t know for sure, but the reason may be that, despite the fact that you suppress any external transactions using the suppression function, you are using the same connection as for the external area. Thus, the connection is already participating in the transaction (external), and therefore all changes are tied to this. The fact that an external transaction is not present in the internal area may not matter. In other words, the suppression area will not do anything here.

You can try running the internal logic in TransactionScope using RequiresNew and with a new connection.

Answer 2

Try creating 2 transaction areas for individual connections. At the SQL level, transactions are cloudy at the level of each connection.