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!).

Stored procedures that take 20 minutes? ***That's*** the problem. — Grant Thomas, Jun 19 '11 at 11:18
An atomic operation needs to be atomic, whether it takes a long time or not. Sure, Mr. D. in many cases you could architect your app to perform operations in memory or some other cache, and then execute all transactions against the database as quickly as possible, but that's often impractical. If you're using the database as your source of information, and your transaction involves making changes to that data model (such as through a DataContext subclass) that future calculations in the transaction will depend on, then you many legitimately end up with a transaction that takes > 10 minutes. — Triynko, Nov 22 '11 at 02:08

score 32 · Answer 1 · edited Apr 08 '16 at 08:44

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

Just trying this out, looks like there is a bug in the example code. 'timeout' needs to be passed to SetValue for maximumTimeout rather than Timespan.FromMinutes(30). — Adrian Russell, Oct 06 '14 at 03:06

score 12 · Answer 2 · answered Jun 19 '11 at 14:07

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:

Thanks but still, because the defaultSetting is limited by maxTimeout and i tried to had the maxTimeout to my machineSetting (machine.config)i still get the exception: "Configuration system failed to initialize" (inside exception : "{"It is an error to use a section registered as allowExeDefinition='MachineOnly' beyond machine.config. (C:\\...Aplication{ath\ApplicationName.exe.Config line 5)"}". How can i solve i this? Also if i don't want to change other application setting, only in my application.... is there a way to do it? — user436862, Jun 19 '11 at 21:13
Again, it *looks* like you are trying to put the machineSetting-Element in your local configuration file, it even says in the exception message ('C:\\...Aplication{ath\ApplicationName.exe.Config line 5'). Put it in your 'machine.config' file only (see the first paragraph of my answer) and you should be good. — Christian.K, Jun 20 '11 at 05:22

score 0 · Answer 3 · edited Jul 02 '13 at 13:56

0

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.

edited Jul 02 '13 at 13:56

Robert

5,278
43
65
115

answered Jul 02 '13 at 13:37

Abhijit Kumar

11
1

score 0 · Answer 4 · answered Jun 19 '11 at 11:19

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

Thanks but still i get the same exception... Any other idea? — user436862, Jun 19 '11 at 20:58

score 0 · Answer 5 · answered Jun 19 '11 at 11:25

0

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.

answered Jun 19 '11 at 11:25

Grant Thomas

44,454
10
85
129

I tried but still the same exception occur in the initialization: "Configuration system failed to initialize" ... I really tried everything with not success... Any ideas? – user436862 Jun 19 '11 at 21:01
2

I was getting the same configuration exception, but when I moved the **system.transactions** tag to the bottom of my machine.config everything worked as expected! – Thomas C. G. de Vilhena Apr 18 '13 at 11:32
Thank you @Thomas C. G. de Vilhena moving the system.transactions tag to the bottom has worked for me as well! – 03Usr Apr 29 '13 at 15:21

score -3 · Answer 6 · answered Sep 10 '13 at 18:26

Try change the machine.config property allowExeDefinition for "MachineToApplication":

<sectionGroup name="system.transactions" type="System.Transactions.Configuration.TransactionsSectionGroup, System.Transactions, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089, Custom=null">
            <section name="defaultSettings" type="System.Transactions.Configuration.DefaultSettingsSection, System.Transactions, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089, Custom=null"/>
            <section name="machineSettings" type="System.Transactions.Configuration.MachineSettingsSection, System.Transactions, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089, Custom=null" allowDefinition="MachineOnly" allowExeDefinition="MachineToApplication" />
</sectionGroup>

And after, alter you web.config/app.config:

<?xml version="1.0" encoding="utf-8"?>
   <configuration>
    ...
    <system.transactions>
        <machineSettings maxTimeout="00:00:00" ></machineSettings>
      </system.transactions>
    </configuration>

A value of 00:00:00 (or zero) in property "maxTimeout" is interpreted as infinity.

Regards,

TransactionScope maximumTimeout

6 Answers6

Linked