locked
Strange behaviour of TransactionScope with local transaction RRS feed

  • Question

  • I am playing with TransactionScope at this moment. I’ve created the following code to test the behavior of transaction scope with local and distributed transactions, different TransactionScopeOptions. It also includes options to complete or not to complete the root and nested scopes.

    public void Local(TransactionScopeOption scopeOption, bool completeRoot, bool completeNested)

    {

        using (TransactionScope scope1 = new TransactionScope())

        {

            using(SqlConnection conn = new SqlConnection("...")){

                conn.Open();

     

                Insert(1, conn);//Inserts a value into a database using specified connection;

                using (TransactionScope scope2 = new TransactionScope())

                {

                    Insert(2, conn);//Insert a value into a database using specified connection;

                    if (completeNested)

                        scope2.Complete();

                }

                Insert(3, conn);//Insert a value into a database using specified connection;

                if (completeRoot)

                    scope1.Complete();

            }

        }

    }

     

    public void Distributed(TransactionScopeOption scopeOption, bool completeRoot, bool completeNested)

    {

        using (TransactionScope scope1 = new TransactionScope())

        {

            InsertToDB1(1);//Creates a connection to DB1 and inserts a value;

            using (TransactionScope scope2 = new TransactionScope())

            {

                InsertToDB2(2);//Creates a connection to DB2 and inserts a value;

                if (completeNested)

                    scope2.Complete();

            }

            InsertToDB3(3);//Creates a connection to DB3 and inserts a value;

            if (completeRoot)

                scope1.Complete();

        }

     

    }

    I am using SQL Server 2005 for testing. TransactionScope works well with distributed transactions with, however using TransactionScope leads to extremely unpredictable behavior, when used with local transaction. Please see the following comparison table. It includes the values, inserted into the database for local and distributed transactions, with different TransactionScopeOptions and different options of completing root and nested scope:

     

    CompleteRoot

    false

    true

    false

    true

     

    CompleteNested

    false

    false

    true

    true

    Required

    Local

    3

    3 TransactionAbortedException

     

    1,2,3

    Distributed

     

     

     

    1,2,3

    RequiresNew

    Local

     

    1,2,3

     

    1,2,3

    Distributed

     

    1,3

    2

    1,2,3

    Suppress

    Local

     

    1,2,3

     

    1,2,3

    Distributed

    2

    1,2,3

    2

    1,2,3

     

    Does anyone have any idea about this strange behavior? I saw a similar thread (http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/3ee09045-b07d-4dac-a1d3-4605128f13bd/) here, however noone replied on it.

    Thanks
    Wednesday, May 20, 2009 2:36 PM

Answers

  • This is a known issue. You can see it mentioned here as well: http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/206e0b5d-4dcf-415a-83a8-04ccd90197c4/. Read also about transaction binding behavior here: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx, scroll to "Transaction Binding" section for details.


    In short, when scope2 transaction goes out of scope (disposes), since it is not completed, it causes the parent transaction to fail. Due to default configuration of SqlConnection set to implicit unbind, the next operations will run with no transaction scope. This is known issue, we already have a bug that deals with this situation for the next releases of .Net framework.

    To workaround this, set Transaction Binding=Explicit Unbind; in the connection string. This way, the last operation will raise exception (The transaction is either not associated with the current connection or has been completed). We recommend to ALWAYS set the tx binding value to explicit unbind to override the default behavior which causes strange issues like the one you raised above.

    Thanks,
     Nissim

    Postings are provided "As Is" with no warranties and confer no rights.
    Thursday, May 21, 2009 11:23 PM

All replies

  • This is a known issue. You can see it mentioned here as well: http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/206e0b5d-4dcf-415a-83a8-04ccd90197c4/. Read also about transaction binding behavior here: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx, scroll to "Transaction Binding" section for details.


    In short, when scope2 transaction goes out of scope (disposes), since it is not completed, it causes the parent transaction to fail. Due to default configuration of SqlConnection set to implicit unbind, the next operations will run with no transaction scope. This is known issue, we already have a bug that deals with this situation for the next releases of .Net framework.

    To workaround this, set Transaction Binding=Explicit Unbind; in the connection string. This way, the last operation will raise exception (The transaction is either not associated with the current connection or has been completed). We recommend to ALWAYS set the tx binding value to explicit unbind to override the default behavior which causes strange issues like the one you raised above.

    Thanks,
     Nissim

    Postings are provided "As Is" with no warranties and confer no rights.
    Thursday, May 21, 2009 11:23 PM
  • Note that this issue has been fixed in .Net 4.0, so you will no longer need use Explicit Unbind.

     

    Cheers,

    Jared


    Postings are provided "As Is" with no warranties and confer no rights.
    Friday, August 20, 2010 1:41 AM
  • Why isn't this in any MSDN examples of TransactionScope (not even the examples that are supposedly demonstrating how to perform atomic transactions eg. http://msdn.microsoft.com/en-us/library/ms172070.aspx)???

    Microsoft needs to understand that people generally don't like their databases to be corrupted by partially committed transactions.  This is database technology 101.

    A bit frustrated...

    Tuesday, March 8, 2011 6:43 PM