TransactionScope, ADO.NET, and Timeouts

Question

Hi!

I've been having trouble using the TransactionScope to wrap multiple database queries into a transaction. What happened was, that the queries took too long to finish and the transaction timed out. Of course, this is the expected behaviour. What I didn't expect, is that the data was committed to the database anyways.

I built a small test-app to see what happens. At first, I thought it was because I opened the database connection before starting the transaction. That's why I built two methods, one opening the SqlConnection before creating a TransactionScope, and one the other way around. Both simply add some entries to the database and wait a little to cause the timeout. And in both cases, the result was identical.

What I noticed was, that the Timeout fires AFTER the TransactionScope is disposed. So the queries themselves are not aborted and the ts.Complete() method is called. After the TransactionAbortedException is fired, I can see, that the entries made during the transaction's timeout period are not in the database, so the rollback works. However, all entries made after the timeout period are not removed. I don't think, that this is correct behavior. Does anyone have a clue as to what I'm doing wrong?

Sample code:

        private void ConInsideTS()
        {
            try
            {
                using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 0, 2)))
                {
                    using (SqlConnection con = new SqlConnection(Settings.Default.ConnectionString))
                    {
                        InsertValues(con);
                        ts.Complete();
                    }
                }
            }
            catch (TransactionAbortedException tae)
            {
                MessageBox.Show(this, tae.ToString(), Application.ProductName);
            }
        }

        private void ConOutsideTS()
        {
            using (SqlConnection con = new SqlConnection(Settings.Default.ConnectionString))
            {
                using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 0, 2)))
                {
                    InsertValues(con);
                    ts.Complete();
                }
            }
        }

        private void InsertValues(SqlConnection con)
        {
            con.Open();
            Random random = new Random();
            for (int i = 1; i <= 20; i++)
            {
                System.Threading.Thread.Sleep(200);
                StringBuilder sb = new StringBuilder(i);
                for (int j = 0; j < i; j++)
                {
                    sb.Append((char)(random.Next(65, 91)));
                }
                using (SqlCommand cmd = new SqlCommand("INSERT INTO Names(Name) VALUES (@name)", con))
                {
                    cmd.Parameters.AddWithValue("@name", sb.ToString());
                    cmd.ExecuteNonQuery();
                }
            }
        }

Answer 1

Is TransactionScope refferring to distributed transactions - e.g.  when multiple databases are involved i.e. when MSDTC is implied?

In  your case, you're using a single database, use SqlTransaction class instead. See code below:

 

1 public void ExecSqlStmt(string strConnString) {
2    SqlConnection conn = new SqlConnection(strConnString);
3    conn.Open();
4
5    SqlCommand myCommand = new SqlCommand();
6    SqlTransaction myTrans;
7
8    // Start the transaction here
9    myTrans = myConnection.BeginTransaction();
10
11    // Assign the connection object to command 12    // Also assign our transaction object to the command 13    myCommand.Connection = myConnection;
14    myCommand.Transaction = myTrans;
15
16    try
17    {
18        // 1. SQL command to withdraw money from account A 19        myCommand.CommandText = "Update into Accounts Set Balance = Balance - 100 Where Account = 'A'";
20        myCommand.ExecuteNonQuery();
21        // 2. Do some processing here 22        // .... more code goes here.... 23
24        // 3. SQL command to deposit money from account B 25        myCommand.CommandText = "Update into Accounts Set Balance = Balance + 100 Where Account = 'B'";
26        myCommand.ExecuteNonQuery();
27        myTrans.Commit();
28        Console.WriteLine("Money was transfered successfully.");
29    } catch(Exception e) {
30        myTrans.Rollback();
31        Console.WriteLine("Error: {1}", e.Message);
32        Console.WriteLine("Error reported by {1}.", e.Source);
33        Console.WriteLine("Money was not transfered.");
34    }
35    finally
36    {
37        // Don't forget to close the connection. 38        myConnection.Close();
39    }
40 }

Answer 2

Mihai,

 

I have to disagree with your statement about TransactionScope.

System.Transactions was built for both local and distributed transactions. For local transactions it is using a very fast and lightweight in-memory transaction manager. For the cases were only one (remote) SQL Server is used, using a mechanism called "promotable transactions", the transaction will continue to be lightweight as long as necessary. In addition, if later you change your existing code that uses TransactionScope to call into 2 database servers, the transaction related code will remain the same; System.Transactions will handle automatically the "promotion" to a distributed transaction.

 

Coeamyd is hitting a different bug here that the System.Data team should look into investigating and explaining.

What you suggested is merely a workaround, rather than an answer to the problem being reported.

 

Cheers!

Answer 3

I agree with Florin. I know how ADO.NET transactions work. I kept the example simple to illustrate the problem. ADO.NET transactions work fine, but so should the System.Transactions transactions. This definitely seems to be a bug, since the transaction's timeout is evaluated erroneously, leading to interesting, and more importantly inconsistent, results.

Cheers
   Christoph

Answer 4

I also ran up against this, and also thought: "OMG, there must be a bug in the framework!".

It's clearly the case that when the "ambient" transaction times out, you want it to rollback in the database (which it does) - and you want it to throw an exception to blow out your code (which it doesn't).

I personally believe that throwing an exception on rollback should be default behaviour, and perhaps declaratively supressable through an extension to the existing TransactionOptions struct, but it isn't.

However, all is not lost, because you can do it yourself. Firstly, you can get at that "ambient" transaction: Transaction.Current. And better still, the Transaction object has an event TransactionCompleted that it raises. All you have to do is add an event handler to that event, test to see if its an abort, and if so, hurl an exception.

private void TransactionCompleted( object sender, TransactionEventArgs e )
{
    if( e.Transaction.TransactionInformation.Status == TransactionStatus.Aborted )
    {
        throw new TimeoutException( "The transaction timed out" );
    }
}

public void RunTests()
{
    System.Transactions.TransactionOptions txOptions = new TransactionOptions();
    txOptions.Timeout = new TimeSpan( 0, 0, 1 );
    txOptions.IsolationLevel = IsolationLevel.ReadCommitted;
    using( TransactionScope txs = new TransactionScope( TransactionScopeOption.Required, txOptions ) )
    {
        Transaction.Current.TransactionCompleted += new TransactionCompletedEventHandler( TransactionCompleted );
        using( SqlConnection cn = new SqlConnection( Properties.Settings.Default.mydbConnectionString ) )
        {
            cn.Open();
            System.Threading.Thread.Sleep( new TimeSpan( 0, 0, 2 ) );
            // Try to do some stuff on the connection here - without the event handler it would be executed after the tx has rolled back
        }
        txs.Complete();
    }
}

I've left out the error handling, but I'm sure you get the picture.

HTH - Alasdair.

Answer 5

Hmm - I may have spoken too soon. Certainly it gets to the throw statement in my TransactionCompleted handler, but it's on a different call stack to the main code block, so even if you add error handling into the main code (just inside the using( TransactionScope... block for example), it won't catch the exception thrown in TransactionCompleted. So, I'm back to the drawing board again. Here's the Exception, just so you can see the callstack has (naturally) come from an asynch timer callback:

CRet.TransactionCompleted(Object sender, TransactionEventArgs e) in C:\Dev\CRet.cs:line 28
   at System.Transactions.TransactionCompletedEventHandler.Invoke(Object sender, TransactionEventArgs e)
   at System.Transactions.InternalTransaction.FireCompletion()
   at System.Transactions.TransactionStateAborted.EnterState(InternalTransaction tx)
   at System.Transactions.TransactionStateActive.Rollback(InternalTransaction tx, Exception e)
   at System.Transactions.EnlistableStates.Timeout(InternalTransaction tx)
   at System.Transactions.Bucket.TimeoutTransactions()
   at System.Transactions.BucketSet.TimeoutTransactions()
   at System.Transactions.TransactionTable.ThreadTimer(Object state)
   at System.Threading._TimerCallback.TimerCallback_Context(Object state)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading._TimerCallback.PerformTimerCallback(Object state)

If anyone can improve on this, please go ahead...

Cheers, A.

Answer 6

OK, I think I've got it now. The problem is that in TransactionCompleted, you are in a different thread than your main thread where you need the exception to fire. So instead of throwing an exception, you brutally abort the main thread, which actually raises a ThreadAbortException. You tag the exception with some unique data and in the exception handler, if you recognise it as the one you threw, you call ResetAbort() and carry on about your normal business of recovery after the timeout. One final puzzle - how to you get at the main thread in TransactionCompleted? Well, my simple but klutzy answer is to save the current thread in a member variable before starting.

So, the code (which belongs in a class) looks something like this (with some changes to protect the guilty):

private Thread _threadMain;
private const string _sTIMED_OUT = "Transaction timed out";

private void TransactionCompleted( object sender, TransactionEventArgs e )
{
    if( e.Transaction.TransactionInformation.Status == TransactionStatus.Aborted )
    {
        if( _threadMain != null )
        {
            // Blow the main thread up, but pass it a token so it can tell who caused the explosion
            _threadMain.Abort( _sTIMED_OUT );
        }
    }
}

public void RunTests()
{
    System.Transactions.TransactionOptions txOptions = new TransactionOptions();
    txOptions.Timeout = new TimeSpan( 0, 0, 1 );
    txOptions.IsolationLevel = IsolationLevel.ReadCommitted;
    _threadMain = Thread.CurrentThread; // save the thread to a member variable - klutzy, but works
    try
    {
        using( TransactionScope txs = new TransactionScope( TransactionScopeOption.Required, txOptions ) )
        {
            // attach my handler to the ambient transaction
            Transaction.Current.TransactionCompleted += new TransactionCompletedEventHandler( TransactionCompleted );
            using( SqlConnection cn = new SqlConnection( Properties.Settings.Default.stokesConnectionString ) )
            {
                cn.Open();
                Thread.Sleep( new TimeSpan( 0, 0, 2 ) );
                // Try to do some stuff on the connection here - without the event handler it would be executed after the tx has rolled back
            }
            txs.Complete();
        }
    }
    catch( ThreadAbortException ex )
    {
        Debug.WriteLine( "abort() called:\n" + ex.ToString() );
        if( ex.ExceptionState is string && ex.ExceptionState as string == _sTIMED_OUT )
        {
            Debug.WriteLine( "Calling ResetAbort()" );
            Thread.ResetAbort(); // essential, otherwise this re-throws and actually kills the thread
            // Do your timeout recovery stuff here
        }
    }
    Debug.WriteLine( "Carrying on as normal" );
}

I hope this helps anyone else similarly stuck.

Best regards,

Alasdair.

Answer 7

Hi Alasdair!


Woah, this seems like quite a crowbar approach. But I guess it's ok, since there seems to be no other way to do it for now. This doesn't make transactions much easier, as you would initially think when looking at the TransactionScope documentation.

I might also add, that this bug is not constrained to the System.Transactions timeouts, but also occurs when using the ServiceDomain with transactions enabled. The effect is exactly the same (since they probably basically use the same infrastructure). There is definitely potential for improvements to the framework here :-)

Anyways, thanks Alasdair for your solution. At least, now I don't have to pass around any Transaction objects.


Cheers,

Christoph

Answer 8

Well, it is a crowbar approach. But it had to be done .

Dear Microsoft - it seems there are two critical flaws in your otherwise reasonable attempt at implicit transactions:

1. The timeout problem discussed at length in this forum thread.
2. The need to pass a connection reference around to avoid escalation.

I think we've done point 1 to death. I have a feeling that as neither TransactionScope nor Transaction are sealed, I could subclass them to implement a notion of "ambient connection", but simply don't have time right now.

Plus I actually preferred the declarative model we had in COM+, and attributes would surely have been the way to go?

OT: Have you noticed how much better these forums look in Firefox than IE7 on a wide screen?

Anyway - glad to have been of some help.

Alasdair.

Answer 9

A note of caution about thread aborts: these are handled differently from most other exceptions and may break some assumptions.  Fer instance, they can blow past catch statements and even simple finally blocks if the thread is in certain states (inside locks can trigger this). 

If you fire off a thread abort inside any SqlClient operation that is doing real work on a connection, the connection will be marked as aborted, but cleanup will be delayed (we can't trust the state of the thread firing the abort), potentially holding resources on the server for some time.

Answer 10

Hi Alazel,

I agree, that ThreadAbortExceptions should not be the solution to problems like this, but do you have any better ideas? I believe, you see the problem at hand, and we're anxious to see a good solution. But the way things present themselves currently, this is the only "solution" we have.

Hope, you have better ideas.

Cheers,
   Christoph

Answer 11

Alazel - thanks for your input. I'm no expert at threading, just scrabbling around in desperation for a fix to what appears to be broken semantics.

I tried using Thread.Interrupt(), but, in my code, it didn't actually interrupt the main thread. I understand that Interrupt() is documented to do exactly that, and requires the target thread to be in the WaitSleepJoin state. So yep, we're stuck with the code as described. What I haven't done is to test extensively to see what happens if, for example, I try to reuse the pooled SQL connection after I've so rudely blown out its waiting client thread. I hope that even if the dispose doesn't run synchronously to rollback the transaction, that SQL Server will interpret the loss of its client as a forcible disconnect of the SQL SPID and that it will rollback any transaction on that SPID - just like killing your copy of ISQL when it's running a long query.

I'd really like to see an explanation from MS about why the semantics of the TransactionScope timeout are as they are, and what the intended use-case was, because where I'm sitting the current timeout implementation looks more like a timebomb.

Cheers,

 

Alasdair.

Answer 12

I ran into this while trying to resolve an issue I am having.  I am also using transaction scope, but with datasets.  Both the table adapters are configured to use the same connection string setting against a local SQL Server Express database.

 

I am getting an error stating that "MSDTC on Server xxx is unavailable.  This code works if I manually start the MSDTC service, but I am concerned that this is not a realistic expection for the client (I intend to sell this commerically).  A code sample follows.  If what you say is true, why would I be getting an error on MSDTC error in the first place?  Wouldn't it use the lightweight in memory transaction manager since they are from the same database?

 

I'd appreciate any help you could give me.

 

public static void AddNewEmployee(ref mtda.Employees.EmployeesRow emp, ref mtda.Cars.CarsRow car)

{

using (System.Transactions.TransactionScope tran = new System.Transactions.TransactionScope())

{

mtda.EmployeesTableAdapters.EmployeesTableAdapter empDa = new mtda.EmployeesTableAdapters.EmployeesTableAdapter();

mtda.CarsTableAdapters.CarsTableAdapter carDa = new mtda.CarsTableAdapters.CarsTableAdapter();

carDa.Update(car);

emp.Default_Car_ID = car.Car_ID;

empDa.Update(emp);

car.Belongs_Employee_ID = emp.Employee_ID;

carDa.Update(car);

tran.Complete();

}

}

 

Answer 13

Hi,

 

to fix the 'MSDTC on Server xxx is unavailable' issue do the following:

 

• Control panel
• Administrative tools
• Services
• Distributed Transaction Coordinator
• Start and set to automatic

Regards,

Charles

Answer 14

New thread http://forums.microsoft.com/Forums/ShowPost.aspx?PostID=3026934&SiteID=1

 

Same bug, different code sample, more workarounds.