R – How to determine whether SqlConnection is enlisted into a System.Transactions’ tx or not

.net-2.0ado.netmsdtcnettransactions

When we using a transation from System.Transactions (creating TransationScope for an instance) by default all Sql-connections (System.Data.SqlClient.SqlConnection) (but is't also the true for Oracle.DataAccess.OracleConnection) are enlisted on opening.
That's called auto-enlistment. Nice feature. But it can be turned off throught a connection string's parameter (enlist=false). In that case connection being opened wouldn't be enlisted.
But it can be enlisted manually later.
So my question is: for some given SqlConnection's instance how can I determine whether that connection is enlisted or not (into an System.Transaction).
I can look at connection string for the parameter. But this won't do because as I said connection could be enlisted manually.

Best Answer

The framework doesn't appear to allow that.

Perhaps we could discuss why you need to know this information? The TransactionScopeOptions give you some flexibility about when to create transactions.

However, refusing "no" for an answer, a little source browsing later and I've created this code, which DOES work. Note, that this code could cease to function at anytime with patches to the framework!!!!

    static bool IsEnlisted(SqlConnection sqlConnection)
    {
        object innerConnection = typeof(SqlConnection).GetField("_innerConnection", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.FlattenHierarchy).GetValue(sqlConnection);
        var enlistedTransactionField =
            EnumerateInheritanceChain(innerConnection.GetType())
            .Select(t => t.GetField("_enlistedTransaction", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.FlattenHierarchy))
            .Where(fi => fi != null)
            .First();
        object enlistedTransaction = enlistedTransactionField.GetValue(innerConnection);
        return enlistedTransaction != null;
    }

    static IEnumerable<Type> EnumerateInheritanceChain(Type root)
    {
        for (Type current = root; current != null; current = current.BaseType)
            yield return current;
    }

Again, this is making use of private variables and internal classes within the .NET framework. While it works today, it may not tomorrow.

Related Solutions

Sql – System.Transaction implicit transaction messing with the other connections

Use TransactionOptions.IsolationLevel

By default, it's serializable

TransactionOptions transactionoptions1 = new TransactionOptions();
transactionoptions1.IsolationLevel = IsolationLevel.ReadCommitted;
using (var scope = new TransactionScope(TransactionScopeOption.Required, transactionoptions1))
{
    ... make my 3 stored procedure calls ...

    scope.Complete();

    return returnCode;
}

.NET: how to disable promotion in System.Transactions

No, you can't disable transaction promotion via a property or configuration. The only way to disable transaction promotion is to avoid the conditions that cause the transaction to be promoted to a distributed transaction.

To avoid transaction management escalation you need to:

have SQL Server 2005 or higher as your database
if SQL Server 2005, use only one database connection for the lifetime of the transaction. (SQL Server 2008 will allow you to use multiple connections within a transaction without promotion occurring.)
only access one database
not pass your transaction across application domains

The reason for the rules are to ensure the ACID properties of the transaction.

As an example, let's assume for a minute that you could specify not to promote a transaction (as in your code snippet) but your code accesses two databases (I know -- your code only uses one database). So now you've specified not to use distributed transactions, you've started a transaction, and you've specified that two databases should both be within that one unit of work. There is a conflict between the ACID properties of the transaction and the desire to avoid a distributed transaction. It seems to me that there would be two ways to handle this: either promote the transaction to a distributed transaction (which you said you didn't want to do!) or throw an exception (since you can't guarantee the integrity of the transaction).

So the use of a DisablePromotion property wouldn't have much value since, for the application to not promote the transaction, you still need to follow the escalation rules.

If you really wanted, you could handle the DistributedTransactionStarted event and throw an exception when a distributed transaction is started. That would guarantee that your application is not using distributed transactions but that probably isn't what you want.

Best Answer

Related Solutions

Sql – System.Transaction implicit transaction messing with the other connections

.NET: how to disable promotion in System.Transactions

Related Topic