使用事务范围时停止事务升级为分布式的推荐做法 [英] Recommended practice for stopping transactions escalating to distributed when using transactionscope
问题描述
使用 TransactionScope 对象来设置不需要跨函数调用传递的隐式事务非常棒！但是，如果一个连接打开而另一个连接已经打开，事务协调器会悄悄地将要分发的事务升级(需要运行 MSDTC 服务并占用更多资源和时间).
Using the TransactionScope object to set up an implicit transaction that doesn't need to be passed across function calls is great! However, if a connection is opened whilst another is already open, the transaction coordinator silently escalates the transaction to be distributed (needing MSDTC service to be running and taking up much more resources and time).
所以，这很好:
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();
}
是否有推荐的做法来避免以这种方式升级事务，同时仍然使用嵌套连接?
Is there a recommended practise to avoid escalating transactions in this way, whilst still using nested connections?
目前我能想到的最好方法是使用 ThreadStatic 连接并在设置 Transaction.Current 时重用它，如下所示:
The best I can come up with at the moment is having a ThreadStatic connection and reusing that if Transaction.Current is set, like so:
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;
}
}
因此，如果答案是在嵌套在事务范围内时重用相同的连接，如上面的代码所做的那样，我想知道在事务中处理此连接的含义.
So, if the answer is to reuse the same connection when it's nested inside a transactionscope, as the code above does, I wonder about the implications of disposing of this connection mid-transaction.
据我所知(使用 Reflector 检查代码)，连接的设置(连接字符串等)被重置，连接被关闭.因此(理论上)，重新设置连接字符串并在后续调用中打开连接应该重用"连接并防止升级(我的初始测试同意这一点).
So far as I can see (using Reflector to examine code), the connection's settings (connection string etc.) are reset and the connection is closed. So (in theory), re-setting the connection string and opening the connection on subsequent calls should "reuse" the connection and prevent escalation (and my initial testing agrees with this).
虽然它看起来有点老套……而且我确信某处必须有一个最佳实践，规定人们不应在处理完对象后继续使用它！
It does seem a little hacky though... and I'm sure there must be a best-practise somewhere that states that one should not continue to use an object after it's been disposed of!
但是，由于我无法对密封的 SqlConnection 进行子类化，并且想要维护与事务无关的连接池友好方法，因此我努力(但会很高兴)寻找更好的方法.
However, since I cannot subclass the sealed SqlConnection, and want to maintain my transaction-agnostic connection-pool-friendly methods, I struggle (but would be delighted) to see a better way.
此外，我意识到如果应用程序代码尝试打开嵌套连接(在我们的代码库中在大多数情况下是不必要的)，我可以通过抛出异常来强制非嵌套连接
Also, realised that I could force non-nested connections by throwing exception if application code attempts to open nested connection (which in most cases is unnecessary, in our codebase)
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;
}
}
仍然会重视一个不那么笨拙的解决方案:)
Would still value a less hacky solution :)
推荐答案
事务升级的主要原因之一是当一个事务涉及多个(不同)连接时.这几乎总是升级为分布式事务.而且确实很痛苦.
One of the primary reasons for transaction escalation is when you have multiple (different) connections involved in a transaction. This almost always escalates to a distributed transaction. And it is indeed a pain.
这就是我们确保所有事务都使用单个连接对象的原因.有几种方法可以做到这一点.大多数情况下，我们使用线程静态对象来存储连接对象，而我们做数据库持久化工作的类使用线程静态连接对象(当然是共享的).这可以防止使用多个连接对象并消除了事务升级.您也可以通过简单地在方法之间传递连接对象来实现这一点，但这并不那么干净，IMO.
This is why we make sure that all our transactions use a single connection object. There are several ways to do this. For the most part, we use the thread static object to store a connection object, and our classes that do the database persistance work, use the thread static connection object (which is shared of course). This prevents multiple connections objects from being used and has eliminated transaction escalation. You could also achieve this by simply passing a connection object from method to method, but this isn't as clean, IMO.
这篇关于使用事务范围时停止事务升级为分布式的推荐做法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
