TransactionScope MaximumTimeout

March 27, 2023 Post a Comment

I use TransactionScope in this code: private void ExecuteSP() { bool IsComplete = false; SqlCommand sqlComm = null; //6 hours!!! TimeSpan ts1 = new TimeSpan(6, 0, 0

Solution 1:

If you aren't afraid of using reflection, you can actually override the maximum timeout programmatically. This code isn't guaranteed to be future-proof, but it works as of .NET 4.0.

public static class TransactionmanagerHelper
{
    public static void OverrideMaximumTimeout(TimeSpan timeout)
    {
        //TransactionScope inherits a *maximum* timeout from Machine.config.  There's no way to override it from
        //code unless you use reflection.  Hence this code!
        //TransactionManager._cachedMaxTimeout
        var type = typeof(TransactionManager);
        var cachedMaxTimeout = type.GetField("_cachedMaxTimeout", BindingFlags.NonPublic | BindingFlags.Static);
        cachedMaxTimeout.SetValue(null, true);

        //TransactionManager._maximumTimeout
        var maximumTimeout = type.GetField("_maximumTimeout", BindingFlags.NonPublic | BindingFlags.Static);
        maximumTimeout.SetValue(null, timeout);
    }
}

You can use it like this:

            TransactionmanagerHelper.OverrideMaximumTimeout(TimeSpan.FromMinutes(30));

Solution 2:

You cannot specify the machineSettings in your own configuration file, but you need to actually change/add the machineSettings\maxTimeout in the machine.config file of the computer.

There is one instance of this file for every 32/64 bit and CLR version combination on your computer. For example, the 32 bit version's file for .NET 2.0 is located in the %windir%\Microsoft.NET\Framework\v2.0.50727\CONFIG directory. The file for a 64 bit .NET 2.0 application is in the %windir%\Microsoft.NET\Framework64\v2.0.50727\CONFIG directory. Likewise, if you are using .NET 4.0 you need to change the file in the v4.0.30319\Config subdirectory.

Note that by changing this file (as the name should imply) you change the maximum timeout for every transaction on your box. So be careful what you set here. In your example, you would have changed the timeout to 10 (!) hours.

Overly long transaction timeouts can be an issue, because sometimes a deadlock cannot be detected until the timeout has been reached. So sometimes such situations cannot be detected in a timely manner. There are other reasons why long running transactions should be avoided, but this is really out of the scope of this question/answer.

Anyway, individual applications can still set their own maximum timeout, which however is always capped by the one in the machine.config file, using the system.transactions section in their own configuration file:

  <system.transactions>
    <defaultSettings timeout="22:00:00"/>
  </system.transactions>

Note that the element name here is defaultSettings not machineSettings.

You may also check the following links for more information:

Solution 3:

One thing I did notice from your code differing from the example MS provide is that you complete your transaction before you complete your SQL work.

eg,

 t.Complete();

comes before

            if (sqlComm != null)
                sqlComm.Dispose();

your t.complete(); really needs to move down to the end of your sql segment. I havent run this to prove it works, but it makes sense in that you have a transaction section that then has further work after you told it it was complete.

I used this as reference: http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

Solution 4:

Try swapping the order of elements in your configuration file so that appSettings comes before system.transactions, like so:

<appSettings>    
  ...     
</appSettings> 
<system.transactions>
  ...
</system.transactions>

Also, if you have configSections in there, do the likewise.

Solution 5:

The default value is:

Transaction Binding=Implicit Unbind.

Implicit Unbind causes the connection to detach from the transaction when it ends. In few cases, people use:

Binding=Explicit Unbind

You can check this in your case.

Spark Sql Blog