在什么情况下 SqlConnection 会自动加入环境 TransactionScope 事务? [英] Under what circumstances is an SqlConnection automatically enlisted in an ambient TransactionScope Transaction?

问题描述

SqlConnection 在事务中登记"是什么意思?是不是意味着我在连接上执行的命令会参与到事务中?

What does it mean for an SqlConnection to be "enlisted" in a transaction? Does it simply mean that commands I execute on the connection will participate in the transaction?

如果是这样，在什么情况下 SqlConnection 会自动加入环境 TransactionScope 事务?

If so, under what circumstances is an SqlConnection automatically enlisted in an ambient TransactionScope Transaction?

查看代码注释中的问题.我对每个问题的答案的猜测跟在括号中的每个问题之后.

See questions in code comments. My guess to each question's answer follows each question in parenthesis.

using (TransactionScope scope = new TransactionScope())
using (SqlConnection conn = ConnectToDB())
{   
    // Q1: Is connection automatically enlisted in transaction? (Yes?)
    //
    // Q2: If I open (and run commands on) a second connection now,
    // with an identical connection string,
    // what, if any, is the relationship of this second connection to the first?
    //
    // Q3: Will this second connection's automatic enlistment
    // in the current transaction scope cause the transaction to be
    // escalated to a distributed transaction? (Yes?)
}

场景 2:在事务范围内使用连接，但在事务范围外打开

//Assume no ambient transaction active now
SqlConnection new_or_existing_connection = ConnectToDB(); //or passed in as method parameter
using (TransactionScope scope = new TransactionScope())
{
    // Connection was opened before transaction scope was created
    // Q4: If I start executing commands on the connection now,
    // will it automatically become enlisted in the current transaction scope? (No?)
    //
    // Q5: If not enlisted, will commands I execute on the connection now
    // participate in the ambient transaction? (No?)
    //
    // Q6: If commands on this connection are
    // not participating in the current transaction, will they be committed
    // even if rollback the current transaction scope? (Yes?)
    //
    // If my thoughts are correct, all of the above is disturbing,
    // because it would look like I'm executing commands
    // in a transaction scope, when in fact I'm not at all, 
    // until I do the following...
    //
    // Now enlisting existing connection in current transaction
    conn.EnlistTransaction( Transaction.Current );
    //
    // Q7: Does the above method explicitly enlist the pre-existing connection
    // in the current ambient transaction, so that commands I
    // execute on the connection now participate in the
    // ambient transaction? (Yes?)
    //
    // Q8: If the existing connection was already enlisted in a transaction
    // when I called the above method, what would happen?  Might an error be thrown? (Probably?)
    //
    // Q9: If the existing connection was already enlisted in a transaction
    // and I did NOT call the above method to enlist it, would any commands
    // I execute on it participate in it's existing transaction rather than
    // the current transaction scope. (Yes?)
}

推荐答案

自从提出这个问题以来，我已经做了一些测试，并且我自己找到了大部分(如果不是全部)答案，因为没有其他人回答.如果我遗漏了什么，请告诉我.

I've done some tests since asking this question and found most if not all answers on my own, since no one else replied. Please let me know if I've missed anything.

Q1:连接会自动加入事务吗?

Q1: Is connection automatically enlisted in transaction?

是，除非在连接字符串中指定了 enlist=false.连接池找到一个可用的连接.可用连接是未在事务中登记或在同一事务中登记的连接.

Yes, unless enlist=false is specified in the connection string. The connection pool finds a usable connection. A usable connection is one that's not enlisted in a transaction or one that's enlisted in the same transaction.

Q2:如果我现在使用相同的连接字符串打开(并运行命令)第二个连接，那么第二个连接与第一个连接的关系是什么(如果有)?

Q2: If I open (and run commands on) a second connection now, with an identical connection string, what, if any, is the relationship of this second connection to the first?

第二个连接是独立连接，参与同一个事务.我不确定这两个连接上命令的交互，因为它们是针对同一个数据库运行的，但我认为如果同时在两个连接上发出命令可能会发生错误:另一个会话正在使用事务上下文"

The second connection is an independent connection, which participates in the same transaction. I'm not sure about the interaction of commands on these two connections, since they're running against the same database, but I think errors can occur if commands are issued on both at the same time: errors like "Transaction context in use by another session"

Q3:第二个连接在当前事务范围内的自动登记是否会导致事务升级为分布式事务?

Q3: Will this second connection's automatic enlistment in the current transaction scope cause the transaction to be escalated to a distributed transaction?

是的，它会升级为分布式事务，因此，即使使用相同的连接字符串，征用多个连接也会导致它成为分布式事务，这可以通过在 Transaction.Current.TransactionInformation.DistributedIdentifier.

Yes, it gets escalated to a distributed transaction, so enlisting more than one connection, even with the same connection string, causes it to become a distributed transaction, which can be confirmed by checking for a non-null GUID at Transaction.Current.TransactionInformation.DistributedIdentifier.

*更新:我在某处读到这在 SQL Server 2008 中已修复，因此当两个连接使用相同的连接字符串时不使用 MSDTC(只要两个连接未同时打开).这允许您在事务中多次打开和关闭连接，这样可以通过尽可能晚地打开连接并尽快关闭它们来更好地利用连接池.

Q4:如果我现在开始在连接上执行命令，它会自动加入当前事务范围吗?

Q4: If I start executing commands on the connection now, will it automatically become enlisted in the current transaction scope?

没有.在没有事务范围处于活动状态时打开的连接不会自动加入新创建的事务范围.

No. A connection opened when no transaction scope was active, will not be automatically enlisted in a newly created transaction scope.

Q5:如果没有加入，我在连接上执行的命令现在会参与环境事务吗?

Q5: If not enlisted, will commands I execute on the connection now participate in the ambient transaction?

没有.除非您在事务范围内打开连接，或者在范围内征用现有连接，否则基本上没有事务.您的连接必须自动或手动加入事务范围，以便您的命令参与事务.

No. Unless you open a connection in the transaction scope, or enlist an existing connection in the scope, there basically is NO TRANSACTION. Your connection must be automatically or manually enlisted in the transaction scope in order for your commands to participate in the transaction.

Q6:如果此连接上的命令没有参与当前事务，即使回滚当前事务范围，它们是否会被提交?

Q6: If commands on this connection are not participating in the current transaction, will they be committed even if rollback the current transaction scope?

是的，不参与事务的连接上的命令在发出时提交，即使代码恰好在已回滚的事务范围块中执行.如果连接未在当前事务范围内登记，则它不参与事务，因此提交或回滚事务将不会影响在未登记在事务范围内的连接上发出的命令...作为 这家伙发现了.除非您了解自动登记过程，否则很难发现这一点:它仅在在活动事务范围内打开连接时发生.

Yes, commands on a connection not participating in a transaction are committed as issued, even though the code happens to have executed in a transaction scope block that got rolled back. If the connection is not enlisted in the current transaction scope, it's not participating in the transaction, so committing or rolling back the transaction will have no effect on commands issued on a connection not enlisted in the transaction scope... as this guy found out. That's a very hard one to spot unless you understand the automatic enlistment process: it occurs only when a connection is opened inside an active transaction scope.

Q7:上述方法是否在当前环境事务中显式登记预先存在的连接，以便我在连接上执行的命令现在参与环境事务?

Q7: Does the above method explicitly enlist the pre-existing connection in the current ambient transaction, so that commands I execute on the connection now participate in the ambient transaction?

是的.通过调用 EnlistTransaction(Transaction.Current)，可以将现有连接显式纳入当前事务范围.您还可以使用 DependentTransaction 在事务中的单独线程上获取连接，但是像以前一样，我不确定针对同一数据库的同一事务中涉及的两个连接如何交互......并且可能会发生错误，并且当然，第二个登记连接会导致事务升级为分布式事务.

Yes. An existing connection can be explicitly enlisted in the current transaction scope by calling EnlistTransaction(Transaction.Current). You can also enlist a connection on a separate thread in the transaction by using a DependentTransaction, but like before, I'm not sure how two connections involved in the same transaction against the same database may interact... and errors may occur, and of course the second enlisted connection causes the transaction to escalate to a distributed transaction.

Q8:如果在我调用上述方法时，现有连接已经加入到事务中，会发生什么情况?会不会报错?

Q8: If the existing connection was already enlisted in a transaction when I called the above method, what would happen? Might an error be thrown?

可能会引发错误.如果使用了 TransactionScopeOption.Required，并且连接已经在事务范围事务中登记，则没有错误；事实上，没有为范围创建新事务，事务计数 (@@trancount) 也没有增加.但是，如果您使用 TransactionScopeOption.RequiresNew，则在尝试在新的事务范围事务中登记连接时会收到一条有用的错误消息:连接当前已登记事务.完成当前事务并重试."是的，如果您完成了登记连接的事务，您可以安全地在新事务中登记连接.

An error may be thrown. If TransactionScopeOption.Required was used, and the connection was already enlisted in a transaction scope transaction, then there is no error; in fact, there's no new transaction created for the scope, and the transaction count (@@trancount) does not increase. If, however, you use TransactionScopeOption.RequiresNew, then you get a helpful error message upon attempting to enlist the connection in the new transaction scope transaction: "Connection currently has transaction enlisted. Finish current transaction and retry." And yes, if you complete the transaction the connection is enlisted in, you can safely enlist the connection in a new transaction.

*更新:如果您之前在连接上调用了 BeginTransaction，当您尝试在新的事务范围事务中登记时会引发稍微不同的错误:无法在事务中登记"因为本地事务正在连接上进行.完成本地事务并重试."另一方面，您可以安全地调用 SqlConnection 上的 BeginTransaction 而它在事务范围事务中登记，这实际上会增加 @@trancount 加一，与使用嵌套事务范围的必需选项不同，后者不会导致它增加.有趣的是，如果您继续使用 Required 选项创建另一个嵌套事务范围，您将不会收到错误，因为已经有一个活动的事务范围事务(请记住 @@trancount 在事务范围事务已经处于活动状态并且使用了 Required 选项时不会增加).

*Update: If you previously called BeginTransaction on the connection, a slightly different error is thrown when you try to enlist in a new transaction scope transaction: "Cannot enlist in the transaction because a local transaction is in progress on the connection. Finish local transaction and retry." On the other hand, you can safely call BeginTransaction on the SqlConnection while its enlisted in a transaction scope transaction, and that will actually increase @@trancount by one, unlike using the Required option of a nested transaction scope, which does not cause it to increase. Interestingly, if you then go on to create another nested transaction scope with the Required option, you will not get an error, because nothing changes as a result of already having an active transaction scope transaction (remember @@trancount is not increased when a transaction scope transaction is already active and the Required option is used).

Q9:如果现有连接已经登记在事务中并且我没有调用上述方法来登记它，那么我对其执行的任何命令是否会参与其现有事务而不是当前事务范围?

Q9: If the existing connection was already enlisted in a transaction and I did NOT call the above method to enlist it, would any commands I execute on it participate in its existing transaction rather than the current transaction scope?

是的.无论 C# 代码中的活动事务范围是什么，命令都参与了连接登记的任何事务.

Yes. Commands participate in whatever transaction the connection is enlisted in, regardless of what the active transaction scope is in the C# code.

这篇关于在什么情况下 SqlConnection 会自动加入环境 TransactionScope 事务?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！