TransactionScope的maximumTimeout [英] TransactionScope maximumTimeout

问题描述

我使用的TransactionScope在此代码：

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

这是一个执行后，会发生（sqlCommand.ExecuteNonQuery（） ;）这需要超过10分钟即可执行。我不要在未来excection得到任何错误时抛出，在这一点上，我得到这个异​​常：

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.

这是因为System.Transactions.TransactionManager.MaximumTimeout设置为时间跨度为10分钟。

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

我搜索和我发现，也许是与System.Transactions的 - > machine.config中的maxTimeout，但我得到一个异常我的配置修改此文件后：

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>

当我尝试在运行时获得System.Transactions.TransactionManager.MaximumTimeout后，我改变了配置文件我得到这个异​​常：

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

配置系统初始化失败

没有任何人有任何线索如何解决这个问题

（关于我的情况下，一般注：
I需要做的存储过程，大约需要20分钟，因为我需要转换包含int类型的SQL（INT = 32位，BIGINT = 64位）为bigint表。我需要创建新表并插入旧表中的数据新表的Int64。按ID等4个表连接的表，每一个包含20多万行，也结合，索引等等。我不能在此过程拆分为较小的存储过程，所以我需要改变的最大超时一个小时以上，十几分钟是不够的！）。

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

推荐答案

如果你不害怕使用反射，你实际上可以以编程方式覆盖的最大超时。此代码不能保证是面向未来的，但它的工作原理为.NET 4.0。

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

这篇关于TransactionScope的maximumTimeout的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！