TransactionScope maximumTimeout

Question

I use TransactionScope in this code:

private void ExecuteSP()
{
    bool IsComplete = false;
    SqlCommand sqlComm = null;
    //6 hours!!!
    TimeSpan ts1 = new TimeSpan(6, 0, 0);
    try
    {
        using (TransactionScope t = new TransactionScope(TransactionScopeOption.RequiresNew, ts1))
        {
            using (SqlConnection sqlConn = new SqlConnection(GetConnectionString()))
            {
                //open sql connection
                sqlConn.Open();
                try
                {
                    //create new sqlCommand
                    sqlComm = new SqlCommand();
                    for (int i = 1; i <= 2; i++)
                    {
                        IsComplete = true;
                        //This command takes 15 minutes
                        sqlComm.CommandText = "exec TestSp";
                        sqlComm.Connection = sqlConn;
                        sqlComm.CommandType = CommandType.Text;
                        sqlComm.CommandTimeout = 18000;
                        //Executing my command
                        int j = sqlComm.ExecuteNonQuery();                       
                    }
                    //End
                    t.Complete();
                }
                catch (Exception ex)
                {
                    IsComplete = false;
                    string Message = ex.Message;
                }
                finally
                {
                    if (sqlComm != null)
                        sqlComm.Dispose();                 
                }
            }
        }
    }
    catch (Exception ex)
    {
        string messagee = ex.Message;
        //do something
    }
    finally
    {
        MessageBox.Show("Finsh");
    }
}

It's happens after one execution (sqlCommand.ExecuteNonQuery();) that take more than 10 minutes be execute. I don't get any excpetion in in this point by in the next excection i get this exception:

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.

It's because that System.Transactions.TransactionManager.MaximumTimeout is set to TimeSpan of 10 Minutes.

I search and I been found that maybe it related to "System.Transactions-->machine.config's maxTimeout" but i get an exception after change my config to this file :

<?xml version="1.0"?>
<configuration> 
  <system.transactions>
    <machineSettings maxTimeout="10:00:00"/>
  </system.transactions> 
  <appSettings>    
    <add key="FileName" value="MyFileName" />
    <add key="MySpace" value="5 MB" />       
    <add key="ClientSettingsProvider.ServiceUri" value="" />        
  </appSettings>      
</configuration>

when i try to get System.Transactions.TransactionManager.MaximumTimeout in run time after i changed the config file i get this exception:

"Configuration system failed to initialize"

Does anybody have any clue how to solve this issue?

(General note about my case: i need to do stored procedure that takes about 20 minutes because i need to convert table that contains int to bigint in SQL (int = 32bit, bigint =64 bit). I need to create new tables and insert the data from old table to the new table with int64. The table connected by ID to other 4 tables, each one contains more than 20 million rows and also binding,indexing and more. I can't split this procedure to small stored procedure so i need to change the maximum timeout to one hour or more, 10 minutes is not enough!).

Answer 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));

Answer 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:

• https://blogs.msdn.com/b/dotnetinterop/archive/2005/12/16/504604.aspx
• http://blogs.msdn.com/b/ajit/archive/2008/06/18/override-the-system-transactions-default-timeout-of-10-minutes-in-the-code.aspx