c# – 使用交易镜像时停止交易分配的推荐做法

使用TransactionScope对象设置不需要跨函数调用传递的隐式事务是非常好的！但是,如果连接已打开,而另一个已经打开,则事务协调器将静默地升级要分发的事务(需要MSDTC服务才能运行并占用更多的资源和时间).

所以,这很好：

using (var ts = new TransactionScope())
        {
            using (var c = DatabaseManager.GetOpenConnection())
            {
                // Do Work
            }
            using (var c = DatabaseManager.GetOpenConnection())
            {
                // Do more work in same transaction using different connection
            }
            ts.Complete();
        }

但这会升级??交易：

using (var ts = new TransactionScope())
        {
            using (var c = DatabaseManager.GetOpenConnection())
            {
                // Do Work
                using (var nestedConnection = DatabaseManager.GetOpenConnection())
                {
                    // Do more work in same transaction using different nested connection - escalated transaction to distributed
                }
            }
            ts.Complete();
        }

有没有推荐的做法,以避免以这种方式升级的交易,同时仍然使用嵌套连接？

目前我可以想出的最好的方法是使用ThreadStatic连接,并重用如果Transaction.Current被设置,就像这样：

public static class DatabaseManager
{
    private const string _connectionString = "data source=.sql2008; initial catalog=test; integrated security=true";

    [ThreadStatic]
    private static SqlConnection _transactionConnection;

    [ThreadStatic] private static int _connectionNesting;

    private static SqlConnection GetTransactionConnection()
    {
        if (_transactionConnection == null)
        {
            Transaction.Current.TransactionCompleted += ((s,e) =>
            {
                _connectionNesting = 0;
                if (_transactionConnection != null)
                {
                    _transactionConnection.Dispose();
                    _transactionConnection = null;
                }
            });

            _transactionConnection = new SqlConnection(_connectionString);
            _transactionConnection.Disposed += ((s,e) =>
            {
                if (Transaction.Current != null)
                {
                    _connectionNesting--;
                    if (_connectionNesting > 0)
                    {
                        // Since connection is nested and same as parent,need to keep it open as parent is not expecting it to be closed!
                        _transactionConnection.ConnectionString = _connectionString;
                        _transactionConnection.Open();
                    }
                    else
                    {
                        // Can forget transaction connection and spin up a new one next time one's asked for inside this transaction
                        _transactionConnection = null;
                    }
                }
            });
        }
        return _transactionConnection;
    }

    public static SqlConnection GetOpenConnection()
    {
        SqlConnection connection;
        if (Transaction.Current != null)
        {
            connection = GetTransactionConnection();
            _connectionNesting++;
        }
        else
        {
            connection = new SqlConnection(_connectionString);
        }
        if (connection.State != ConnectionState.Open)
        {
            connection.Open();
        }
        return connection;
    }
}

编辑：所以,如果答案是重用同一个连接,当它嵌套在一个事务镜像中时,像上面的代码一样,我想知道处理这个连接中间事务的含义.

只要看到(使用Reflector检查代码),连接的设置(连接字符串等)将被重置,并且连接被关闭.所以(理论上),重新设置连接字符串并在后续调用中打开连接,应该“重用”连接并防止升级(我的初始测试与此同步).

它确实看起来有点黑客,但是我确信必须有一个最佳实践的地方说,一个人不应该继续使用一个对象后,被处置！

然而,由于我无法对密封的SqlConnection进行子类化,并且希望保持与事务无关的连接池友好方法,所以我努力(但会很高兴)看到更好的方法.

此外,意识到如果应用程序代码尝试打开嵌套连接(在大多数情况下是不必要的,在我们的代码库中),我可以通过抛出异常来强制非嵌套连接

public static class DatabaseManager
{
    private const string _connectionString = "data source=.sql2008; initial catalog=test; integrated security=true; enlist=true;Application Name='jimmy'";

    [ThreadStatic]
    private static bool _transactionHooked;
    [ThreadStatic]
    private static bool _openConnection;

    public static SqlConnection GetOpenConnection()
    {
        var connection = new SqlConnection(_connectionString);
        if (Transaction.Current != null)
        {
            if (_openConnection)
            {
                throw new ApplicationException("Nested connections in transaction not allowed");
            }

            _openConnection = true;
            connection.Disposed += ((s,e) => _openConnection = false);

            if (!_transactionHooked)
            {
                Transaction.Current.TransactionCompleted += ((s,e) =>
                {
                    _openConnection = false;
                    _transactionHooked = false;
                });
                _transactionHooked = true;
            }
        }
        connection.Open();
        return connection;
    }
}

仍然值得一个较少的黑客解决方案:)