使用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;
}
}
编辑:因此,如果答案是在Transactionscope嵌套中重用同一连接,就像上面的代码所做的那样,我想知道在事务处理过程中处理此连接的后果。
据我所见(使用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;
}
}
仍然希望有一个更不用"hack"的解决方案 :)