Under what circumstances is an SqlConnection automatically enlisted in an ambient TransactionScope Transaction?

Question

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

Solution 1

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: Is connection automatically enlisted in transaction?

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: 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: Will this second connection's automatic enlistment in the current transaction scope cause the transaction to be escalated to a distributed transaction?

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: 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: 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: 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: 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. 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: If the existing connection was already enlisted in a transaction when I called the above method, what would happen? Might an error be thrown?

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: 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?

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

Solution 2

Nice work Triynko, your answers all look quite accurate and complete to me. Some other things I would like to point out:

(1) Manual enlistment

In your code above, you (correctly) show manual enlistment like this:

using (SqlConnection conn = new SqlConnection(connStr))
{
    conn.Open();
    using (TransactionScope ts = new TransactionScope())
    {
        conn.EnlistTransaction(Transaction.Current);
    }
}

However, it is also possible to do it like this, using Enlist=false in the connection string.

string connStr = "...; Enlist = false";
using (TransactionScope ts = new TransactionScope())
{
    using (SqlConnection conn1 = new SqlConnection(connStr))
    {
        conn1.Open();
        conn1.EnlistTransaction(Transaction.Current);
    }

    using (SqlConnection conn2 = new SqlConnection(connStr))
    {
        conn2.Open();
        conn2.EnlistTransaction(Transaction.Current);
    }
}

There is another thing to note here. When conn2 is opened, the connection pool code doesn't know that you want to later enlist it in the same transaction as conn1, which means that conn2 is given a different internal connection than conn1. Then when conn2 is enlisted, there are now 2 connections enlisted so the transaction must be promoted to MSDTC. This promotion can only be avoided by using automatic enlistment.

(2) Before .Net 4.0, I highly recommend setting "Transaction Binding=Explicit Unbind" in the connection string. This issue is fixed in .Net 4.0, making Explicit Unbind totally unnecessary.

(3) Rolling your own CommittableTransaction and setting Transaction.Current to that is essentially the same thing as what TransactionScope does. This is rarely actually useful, just FYI.

(4) Transaction.Current is thread-static. This means that Transaction.Current is only set on the thread that created the TransactionScope. So multiple threads executing the same TransactionScope (possibly using Task) is not possible.

Solution 3

One other bizarre situation we've seen is that if you construct an EntityConnectionStringBuilder it will muck with TransactionScope.Current and (we think) enlist in the transaction. We've observed this in the debugger, where TransactionScope.Current's current.TransactionInformation.internalTransaction shows enlistmentCount == 1 before constructing, and enlistmentCount == 2 afterward.

To avoid this, construct it inside

using (new TransactionScope(TransactionScopeOption.Suppress))

and possibly outside the scope of your operation (we were constructing it every time we needed a connection).

Comments

[Triynko]

After writing the answer to Q8, I realize this stuff is starting to look as complicated as the rules for Magic: The Gathering! Except this is worse, because the TransactionScope documentation does not explain any of this.

[Randy Levy]

For Q3, are you opening two connections at the same time using the same connection string? If so, then that will be a Distributed Transaction (even with SQL Server 2008)

[Triynko]

No. I editing the post to clarify. My understanding is that having two connections open at the same time will always cause a distributed transaction, regardless of the SQL Server version. Before SQL 2008, opening only one connection at a time, with the same connection string would still cause a D.T., but with SQL 2008, opening one connection at time (never having two open at once) with the same connection string will not cause a D.T.

[Triynko]

I just tested this scenario, and it seems to work as you describe. Additionally, even if you use automatic enlistment, if you call "SqlConnection.ClearAllPools()" before opening the second connection, then it gets escalated to a distributed transaction.

[Triynko]

If this is true, then there can only ever be a single "real" connection involved in a transaction. The ability to open, close, and reopen a connection enlisted in a TransactionScope transaction without escalating to a distributed transaction is then really an illusion created by the connection pool, which would normally leave the disposed connection open, and return that same exact connection if re-opened for automatic enlistment.

[Triynko]

So what you're really saying is that if you sidestep the automatic enlistment process, then when you go to re-open a new connection inside a transaction scope transaction (TST), instead of the connection pool grabbing the correct connection (the one originally enlisted in the TST), it quite appropriately grabs a completely new connection, which when manually enlisted, causes the TST to escalate.

[Triynko]

Anyway, that's exactly what I was hinting at in my answer to Q1 when I mentioned that it's enlisted unless "Enlist=false" is specified in the connection string, then talked about how the pool finds a suitable connection.

[Triynko]

As far as multi-threading goes, if you visit the link in my answer to Q2, you'll see that while Transaction.Current is unique to each thread, you can easily acquire the reference in one thread and pass it to another thread; however, accessing a TST from two different threads results in a very specific error "Transaction context in use by another session". To multi-thread a TST, you must create a DependantTransaction, but at that point it must be a distributed transaction, because you need a second independent connection to actually run simultaneous commands and MSDTC to coordinate the two.

[Jared Moore]

Yes you are exactly correct. Just to clarify, when the second connection is opened, the pool will try to use an existing pooled connection that was opened and then closed outside of the transaction. In other words, if you open 2 connections and then close them before creating the TransactionScope, then the above code will only use a total of 2 connections.

[Jared Moore]

To clarify your answer for Q2, the two commands should run fine if they are done sequentially on the same thread.

[Jared Moore]

Thanks for the info about DependentTransaction, that is very useful. Multithreading can still be a concern if you only have one connection, e.g. if you are using Task, await, etc.

[Jared Moore]

I just reread your main post and saw that you already described the pooling behaviour beautifully in Q1. :)

[Triynko]

True, if you want to avoid a distributed transaction, multi-threading is not an option as far as I can tell. After a connection is enlisted in a transaction, any subsequent re-opening of the connection must be done via automatic enlistment by opening it with "Enlist=true" (the default) within the transaction scope block in the same thread, so the pool returns that same exact connection. Any other method will either result in an error or the addition of a new connection to the transaction and thus escalation to a distributed transaction.

[pseudocoder]

On the Q3 promotion issue for identical connection strings in SQL 2008, here is the MSDN citation: msdn.microsoft.com/en-us/library/ms172070(v=vs.90).aspx

[Bruno Martinez]

@Triynko Please repeat the questions before the answers.

[Ranvijay Singh]

stackoverflow.com/questions/39410682/… Please look my issue, any solution?

[PanKak]

Very thorough answer!!! For completeness if you want to bypass transaction completely and nest a dapper query enclose the dapper code inside a new TransactionScope like this: using (var scope = new TransactionScope(TransactionScopeOption.Suppress))

[peter]

@JaredMoore I'm wondering the two versions you provided (enlist on and off) have exactly the same behavior?

[Jared Moore]

No, they do not. The first example only uses 1 underlying connection, the second uses 2 underlying connections and therefore is promoted to a distributed (which is usually bad, since it is slower and more complex). Avoiding distributed transactions in this way is the purpose of Enlist=true.

[Nour]

+1 for how organized the question is, and +1 for the excellent work on the finding the answers. Also, I have one more question, in scenario 2. After doing: conn.EnlistTransaction( Transaction.Current ); Can I assume safely, that any commands executed prior to this line, would be already committed, and the new enlisted transaction has no effect on older commands? (I guess yes).