TransactionScope 最大超时 [英] 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 分钟.在这一点上我没有得到任何异常，在下一个异常中我得到这个异常:

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's 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:

配置系统初始化失败"

有人知道如何解决这个问题吗?

(关于我的案例的一般说明:我需要执行大约需要 20 分钟的存储过程，因为我需要将包含 int 的表转换为 SQL 中的 bigint(int = 32bit，bigint = 64 位).我需要创建新表并使用 int64 将旧表中的数据插入到新表中.该表通过 ID 连接到其他 4 个表，每个表包含超过 2000 万行以及绑定、索引等.我无法将此过程拆分为小的存储过程，因此我需要将最大超时更改为一小时或更长时间，10 分钟是不够的！)

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

你可以这样使用它:

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

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