21

(SQL SERVER 2008) If a Transaction Timeout error occurs within a TransactionScope (.Complete()) would you expect the transaction to be rolled back?

Update:
The error is actually being thrown in the closing curly brace (i.e. .Dispose()), not .Complete(). Full error is:

The transaction has aborted. System.Transactions.TransactionAbortedException TransactionAbortedException System.Transactions.TransactionAbortedException: The transaction has aborted. ---> System.TimeoutException: Transaction Timeout
   --- End of inner exception stack trace ---
   at System.Transactions.TransactionStateAborted.BeginCommit(InternalTransaction tx, Boolean asyncCommit, AsyncCallback asyncCallback, Object asyncState)
   at System.Transactions.CommittableTransaction.Commit()
   at System.Transactions.TransactionScope.InternalDispose()
   at System.Transactions.TransactionScope.Dispose()

As far as I can tell the transaction is not rolled back and the tables remained locked until I issued a KILL against the SPID/session_id.

I used DBCC OPENTRAN to get the oldest transaction and then KILL it. I have tried KILL WITH STATUS but get a message that no status is available as nothing is being rolled back. Status of the SPID/session_id in sys.dm_exec_sessions is 'sleeping'. Code snippet:

try
{            
    using (var transaction = new TransactionScope())
    {
        LOTS OF WORK CARRIED OUT WITH LINQ ENTITIES/SubmitChanges() etc.
        transaction.Complete();  //Transaction timeout
    }
    return result;
}
catch (Exception ex)
{
    logger.ErrorException(ex.Message, ex);
    result.Fail(ex.Message);
    return result;
}

UPDATE:
Problem is not entirely solved, but further information should anyone else have this problem.

  1. I am using LINQ to SQL and within the transaction scope I call context.SubmitChanges(). I am carrying out a lot of inserts. SQL Server profiler indicates that a separate INSERT statement is issued for each insert.
  2. In development, if I sleep the thread for 60 seconds (default TransactionScope timeout is 60 seconds) BEFORE calling SubmitChanges() then I get a different error when calling TransactionScope.Complete() (The operation is not valid for the state of the transaction.).
  3. If I sleep for 60 seconds AFTER .SubmitChages() and just before .Complete() then I get 'The transaction has aborted - System.TimeoutException: Transaction Timeout'
  4. NOTE however that on my dev machine no open transactions are found when using DBCC opentran - which is what you would expect as you would expect the transaction to rollback.
  5. If I then add the code at the bottom of this question (sorry couldn't get the website to insert it here) to my config file which increases the TransactionScope timeout to 2 minutes, things start working again (research indicates that if this doesn't work there could be a setting in machine.config that is lower than this that is taking precedence).
  6. Whilst this will stop the transaction aborting, due to the nature of the updates, it does mean that locks on a core business table could be up to 2 minutes so other select commands using the default SqlCommand timeout of 30 seconds will timeout. Not ideal, but better than an open transaction sitting there and totally holding up the application.
  7. A few days ago we had a disastrous release that meant we ran out of diskspace mid upgrade (!) so we did end up using the shrink database functionality which apparently can cause performance problems after you have used it.

  8. I feel a rebuild of the database and a rethink of some business functionality coming on...

david.s
  • 11,283
  • 6
  • 50
  • 82
MT.
  • 791
  • 5
  • 15
  • 23
  • Should it roll back when there is a timeout during .Complete()? – MT. Aug 10 '11 at 09:57
  • You may want to check this question http://stackoverflow.com/questions/6159876/transactionscope-timeout-occurs-prematurely. In particular 'Transaction Binding' may be relevant to your question. – alun Aug 10 '11 at 11:05
  • Cheers. Question updated as it appears the exception is actually occurring in the closing curly brace .Dispose().... – MT. Aug 10 '11 at 19:30

2 Answers2

18

I'm thinking that the TransactionAbortedException is actually a timeout. If so you should find that the InnerException of the TransactionAbortedException is a timeout.

You should be able to get rid of it by making sure that the timeout of the transactionscope is longer than the command timeout.

Try changing the transaction scope to something like this:

new TransactionScope(TransactionScopeOption.Required, TimeSpan.FromSeconds(60))

And also set an explicit timeout on your context. Should be something like:

myContext.CommandTimeout = 30; //This is seconds
alun
  • 3,393
  • 21
  • 26
  • 1
    Thanks. Will definitely check that out. It is a timeout as you say, but I do not understand why the transaction is not rolling back and the tables are remaining locked. I am assuming that the changes have not been committed, but I really need to try and replicate the problem locally. I guess I can do that by playing with the code above – MT. Aug 11 '11 at 06:33
  • @MT If this is the cause you should be able to take some long running query and just set the transactionscope timeout to something really short and the problem should appear ... hopefully:) – alun Aug 11 '11 at 06:36
  • 2
    Maybe this is similar to http://sankarsan.wordpress.com/2009/02/01/transaction-timeout-in-systemtransactions/ – MT. Aug 11 '11 at 08:45
  • Although my problem was a return before the transactionScope.Complete() was called, this answer led to another that gave the solution for the timeouts with async. Thanks! https://stackoverflow.com/questions/24593070/how-to-support-async-methods-in-a-transactionscope-with-microsoft-bcl-async-in – Daniel Lobo Jan 21 '22 at 21:06
0

I resolve this problem modifying the "physical file" machine.config.

1. You have to localize the file:

  • 32 Bits: C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config\machie.config
  • 64 Bits: C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config

2. You have to add the following code:

<system.transactions>
     <defaultSettings timeout="00:59:00" />
</system.transactions>
Victor Campos Lins
  • 41
  • 1