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

Question

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.

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.

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).