在什么情况下是一个SqlConnection的自动环境的TransactionScope交易征？ [英] Under what circumstances is an SqlConnection automatically enlisted in an ambient TransactionScope Transaction?

问题描述

这是什么意思为SqlConnection的是在一个交易征？这仅仅意味着命令我的连接上执行将参与交易？

如果是这样，在什么情况下是的SqlConnection的自动的士兵在周围的TransactionScope交易？

见code意见的问题。我的猜测对每个问题的回答如下括号中的每一个问题。

方案1：打开连接内部的事务范围

 使用（TransactionScope的范围=新的TransactionScope（））
使用（SqlConnection的康恩= ConnectToDB（））
{
    // Q1：是连接自动交易征？ （是？）
    //
    // Q2：如果我打开（和运行命令）现在是第二个连接，
    //用相同的连接字符串，
    //什么，如果有的话，是于第一这个第二连接的关系？
    //
    // Q3：请问本次连接的自动入伍
    //在当前的事务范围使该交易将
    //升级为分布式事务？ （是？）
}
 

方案2：使用连接内部的事务范围之外吧被打开了

  //假设没有环境交易活跃，现在
SqlConnection的new_or_existing_connection = ConnectToDB（）; //或传递的方法参数
使用（TransactionScope的范围=新的TransactionScope（））
{
    //创建事务范围的连接之前打开
    // Q4：如果我开始在连接上执行命令，现在，
    //将自动成为登记在当前事务的范围？ （没有？）
    //
    // Q5：如果没有登记，将命令我现在的连接上执行
    //参与环境事务？ （没有？）
    //
    // Q6：如果此连接命令
    //不参与当前事务，他们将致力于
    //即使回滚当前事务范围有多大？ （是？）
    //
    //如果我的想法是正确的，所有上述令人不安，
    //因为它看起来像我执行命令
    //在事务范围内，但实际上，我一点都没有，
    //直到我做了以下...
    //
    //现在争取在当前事务中现有的连接
    conn.EnlistTransaction（Transaction.Current）;
    //
    // Q7：请问上述方法明确地争取pre-现有连接
    //在当前环境事务，以便命令我
    //现在在连接上执行参加
    //环境事务？ （是？）
    //
    // Q8：如果现有的连接已登记在一个事务中
    //当我打电话给上面的方法，会发生什么？可能的错误被抛出？ （大概？）
    //
    // Q9：如果现有的连接已登记在一个事务中
    //我没有叫上面的方法来争取它，将任何命令
    //我就可以执行参加它现有的事务，而不是
    //当前事务的范围。 （是？）
}
 

解决方案

我因为问这个问题做了一些测试，发现大部分如果不是全部的我自己的答案，因为没有人回答。请让我知道如果我错过了什么。

Q1。是，除非争取= FALSE在连接字符串中指定。连接池找到一个可用连接。一个可用的连接是一个不是在多数民众赞成登记在同一交易的交易或者一个登记。

Q2。第二个连接是一个独立的连接，参与同一个事务。我不知道关于这两个连接命令的互动，因为他们正在运行针对同一数据库，但是我觉得如果命令是在同一时间发布了这两种可能发生的错误：错误像<一个href="http://stackoverflow.com/questions/2858750/what-is-the-reason-of-transaction-context-in-use-by-another-session/2885059#2885059">"Transaction上下文中使用的另一个会话

Q3。是，它就会升级为分布式事务，所以争取多个连接，即使采用相同的连接字符串，使其成为分布式事务，它可以通过检查确认对于一个非空GUID的Transaction.Current.TransactionInformation.DistributedIdentifier。 *更新：我读的地方，这是固定在SQL Server 2008中，这样MSDTC不使用时，相同的连接字符串使用两个连接（只要两个连接不上同时打开）。这可以让你打开一个连接和交易，这可以通过打开连接尽可能晚，一旦关闭它们尽可能更好地利用连接池之内关闭它多次。

Q4。第一个连接打开时没有事务范围的活跃，将不会自动登记在一个新创建的事务范围。

Q5。第除非你打开的事务范围的连接，或者登记在范围内的现有连接，但基本上是没有成交。您的连接必须自动或手动在事务中登记范围，以便为您的指令参与交易。

Q6。是，在连接不参与事务致力于为发行，即使code恰好有在得到回滚事务范围块执行的命令。如果连接不在当前事务范围内征，它不参与交易，因此提交或回滚事务将对在交易范围内没有登记的连接上发出的命令没有任何影响......作为<一个href="http://stackoverflow.com/questions/1707566/data-committed-even-though-system-transactions-transactionscope-commit-not-call">this小伙发现。这是一个很艰巨，除非你理解了自动征用过程中发现：它发生，只有当打开连接的在的活动事务范围

。

Q7。是。一个现有的连接可以通过调用EnlistTransaction（Transaction.Current）明确登记在当前事务的范围。您也可以登记在交易上的一个单独的线程的连接使用DependentTransaction，但像以前一样，我不知道如何参与同一个事务中针对同一数据库的两个连接会发生相互作用，......并且可能会出现错误，当然第二次征的连接会导致交易升级为分布式事务。

Q8。的错误可能会被抛出。如果TransactionScopeOption.Required使用，并连接已登记在事务范围内的事务，那么就没有错误;其实，还有的范围内创建没有新的交易，交易数量（@@ TRANCOUNT）不会增加。但是，如果你使用TransactionScopeOption.RequiresNew，那么你在试图争取在新的事务范围事务的连接得到一个有用的错误消息：连接目前拥有事务中登记完成当前的事务，然后重试。是的，如果你完成连接已登记在交易，你可以放心地登记在一个新的事务中的连接。 更新：如果您$连接，当您试图在一个新的事务范围的事务，以谋求一个稍微不同的错误被抛出页上$ pviously所谓的BeginTransaction：可以在事务无法登记，因为本地事务正在进行的连接。完成本地事务和重试。在另一方面，你可以安全地调用的BeginTransaction上的SqlConnection而其在交易范围内事务中登记，并且将实际增加@@ TRANCOUNT接一个，使用不同的嵌套事务范围所需的选项，这不会导致它增加。有趣的是，如果你之后再去创建所需的选项另一个嵌套事务范围，你就不会得到一个错误，因为没有什么变化已经产生了积极的事务范围的事务（还记得@@ TRANCOUNT的结果不增加当一个事务范围交易已经生效，使用所需的选项）。的

Q9。是。命令参与任何事务的连接被征用的，不管活动事务的范围是在C＃code在什么

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?

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.

Scenario 1: Opening connections INSIDE a transaction scope

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?)
}

Scenario 2: Using connections INSIDE a transaction scope that were opened OUTSIDE of it

//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. 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. 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. 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. *Update: I read somewhere that this is fixed in SQL Server 2008, so that MSDTC is not used when the same connection string is used for both connections (as long as both connections are not open at the same time). That allows you to open a connection and close it multiple times within a transaction, which could make better use of the connection pool by opening connections as late as possible and closing them as soon as possible.

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

Q5. 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. 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. 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. 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. 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. 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屋！