Under What Circumstances Is an SQLconnection Automatically Enlisted in an Ambient Transactionscope Transaction

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

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.

Does SqlBulkCopy Enlist in Ambient Transaction?

I've done some testing, and it appears that SqlBulkCopy does, in fact, honor an ambient transaction, at least in .Net 4.5.

To test, I did a SqlBulkCopy operation while debugging, and verified that the rows made it to the database (via a NOLOCK/dirty read query). I then threw an exception on the next line of code, and allowed the TransactionScope to roll back. I then verified that the rows were no longer in the database.

Why is Sqlserver enlisting in transaction when Enlist=False?

The SqlConnection connection string parameter controls System.Transaction automatic enlistment by SqlConnection. But EF Core also examines and enlists in the ambient transaction.

IE EF does not rely on the ADO.NET DbConnection to detect and enlist in ambient transactions. You can supress the ambient Transaction with a nested TransactionScope:

using (var noTran = new TransactionScope(TransactionScopeOption.Suppress))
{
    using var db = new Db();
    //. . .
    noTran.Complete();
}

What exactly is connection enlistment in C#

Is it safe to assume that an enlisted connection, will NEVER be used
in another transaction as long as the transaction exists, even though
it's closed?

Yes.

Is there any documentation explaining if our assumptions that this is so?

You quoted it: "Connections are drawn from the pool and assigned based on transaction context."

How can a connection NOT be available if it is only used in the enlisted transaction? What can be the case for this?

It can be in-use. eg in a TransactionScope:

using (var con = new SqlConnection(...))
{
   con.Open();
   using (var con2 = new SqlConnection(...))  
   {
     con2.Open(); //con is not available, as it's open and in-use so a new connection will be opened and enlisted
   }
}

Is it important to Open a sql connection in the transactionscope

It is a MUST to open the connection within the TransactionScope to ensure that the connection is enrolled in the transaction.

This is found in the comment just above the connection.Open in this MSDN example.

---

Related Topics