使用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;
}
}
仍然值得一个较少的黑客解决方案:)
