CODEWITHSUNDEEP
.netsql-serverc#-4.0
PabloInNZ
PabloInNZ

Reputation: 555

TransactionScope Timeout overriden

I've dug around all over for this I can't quite understand what I am seeing. This is not code that I wrote, so I am trying to understand what is going on. The bare bones are here :

(action() is an Action object passed in. Basically something like this () => sp.BalanceAccounts(dto)

     try
     {

       TransactionOptions transactionOptions = new TransactionOptions();
       transactionOptions.IsolationLevel = IsolationLevel.ReadCommitted;
       transactionOptions.Timeout = new TimeSpan(0, 0, 1); //added as a test
       sw.Start(); //this is a timer I added to check timeout
       using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
           {
             action();

             scope.Complete();                    
           }
           sw.Stop();
      }
catch (Exception ex)
      {
          sw.Stop();
          Do some stuff
      }

The issue is that when the action passed in involves a sqlDataAdapter, the timeout period seems to be that for the SelectCommand.CommandTimeout, not the one set for the TransactionScope. I added the explicit Timeout setting to try and see what was going on.

In my limited understanding, I though the timeout period would be for the TransactionScope and it would drop to the catch block if this time was exceeded.

If I run a known long running query through that uses the sqlDataAdapter, and set the CommandTimeout to 18 seconds(so it's obvious), then the stopwatch in the catch block above shows just over 18 seconds. If I run something else through as the action which is a bit slow, that does not use the sqlDataAdapter, then the timer in the catch block shows just over 1 second (which is what I set as the transaction scope timeout). Is it just that the transaction is timing out at 1 second anyway, but it can't drop through to the catch block until the sqlDataAdapter times out as well ? I fixed the long running query in the sqlDataAdapter, so it doesn't time out anymore, but am left with this slightly bigger picture puzzle.

Many thaks in advance for any tips !

Upvotes: 1

Views: 820

Answers (1)

Mark Sowul
Mark Sowul

Reputation: 10600

There are two separate timeouts. One is for the query, and the other is for the transaction as a whole.

Lets say you have the query timeout at two minutes, and the transaction timeout at one minute. If the query runs in 30 seconds you're fine. If it runs in 90 seconds, you're not fine: it's within the query timeout, but the transaction will time out (with the helpful error below).

The transaction associated with the current connection has completed but has not been disposed. The transaction must be disposed before the connection can be used to execute SQL statements.

(This happens all the time because the default is in fact a minute, I believe).

Now let's say you have three queries within one transaction. Each query has a timeout of one minute, and the transaciton has a timeout of three minutes. If each query runs in 45 seconds, you're fine: each query completes within its timeout, and the transaction as a whole takes 2 minutes fifteen seconds which is within the three minute timeout.

However if query one takes 30 seconds, query two takes 45 seconds, and query three takes 90 seconds, you're in trouble again, because query three overran its timeout of a minute. Note that the transaction timeout didn't come into play.

Upvotes: 1

Related Questions