CODEWITHSUNDEEP
c#.net
Henrik
Henrik

Reputation: 289

nested Transaction rolls back although TransactionScopeOption.Suppress

I want to roll back a transaction named "scope" but the inner transaction named scope2 should not roll back. But they both roll back!!! The option TransactionScopeOption.Suppress doesn't work...

I already enabled DTC and I'm using Visual Studio 2010 with .net 4.0 and Microsoft SQl Server 2008. What's 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();

            }

        }

Thanks alot for your answers!

I finally works!!!!

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:

scope will be executed scope2 will roll back

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

Any ideas???????

Upvotes: 4

Views: 1854

Answers (2)

StuartLC
StuartLC

Reputation: 107297

Try creating the 2 transaction scopes on separate connections. At a SQL level, transactions are 'scoped' at a per-connection level.

Upvotes: 1

Christian.K
Christian.K

Reputation: 49270

I don't know for sure, but the reason could be that although you do suppress any ambient transaction by using suppress, you use the same connection as for the outer scope. Thus the connection already participates in a transaction (the outer one) and so all changes are bound to this. The fact that there is no ambient transaction present in the inner scope might not make a difference. In other words the suppress scope wouldn't do anything here.

You could try to run the inner logic in a TransactionScope using RequiresNew and with a new connection.

Upvotes: 2

Related Questions