MSDTC transaction being promoted to distributed on query executed outside of transaction scope

Question

I have a SQL Server 2008 database with two tables, a .NET 4.5 application using a TransactionScope with two queries to the database, each using their own separate connection which shares a single connection string.

This code that works fine:

internal static void Main(string[] args)
{
    string connStr = string.Format(
        "Data Source={0};Initial Catalog={1};" +
        "Integrated Security=True", "My DB Server", "My Database");

    using (var scope = new TransactionScope())
    {
        string query1 = "(actual query not relevant)";
        var ds = GetSqlServerDataSet(query1, connStr);
        string query2 = "(actual query not relevant)";
        var ds1 = GetSqlServerDataSet(query2, connStr);
        scope.Complete();
    }
}

private static System.Data.DataSet GetSqlServerDataSet(string usingQuery, 
    string usingConnectionString, 
    params System.Data.SqlClient.SqlParameter[] withParameters)
{
    using (var ds = new System.Data.DataSet())
    using (var conn = new System.Data.SqlClient.SqlConnection(usingConnectionString))
    using (var command = new System.Data.SqlClient.SqlCommand(usingQuery, conn))
    {
        command.Parameters.AddRange(withParameters);

        using (var adapter = new System.Data.SqlClient.SqlDataAdapter(command))
        {
            adapter.Fill(ds);
        }

        return ds;
    }
}

Now, if I throw an exception inside the scope to test roll-back, that's when stuff gets strange. I'm using a referenced class library I've written to persist info about exceptions to a database. It's the same type of setup -- same SQL Server, .NET version, identical ADO.NET code, etc. It's just writing to a different database.

Here's how it works: I've added this method to my app:

private static void HandleUnhandledException(Object sender, System.UnhandledExceptionEventArgs e)
{
    ExceptionHandling.Core.Main.ProcessException((Exception) e.ExceptionObject);
    Environment.Exit(0);
}

and I've added this line to the top of my Main method:

AppDomain.CurrentDomain.UnhandledException += HandleUnhandledException;

Now when I throw an exception, e.g. throw new Exception("blah"); at the bottom of Main right before scope.Complete(), it automatically jumps to HandleUnhandledException, and the rest happens as I've described above. This results in System.Transactions.TransactionManagerCommunicationException with message:

Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool.

This happens on the call to Connection.Open() in my class library.

So what's happening here? I know what the error is telling me, and I know how to fix it. My question is why is this happening in the first place? I thought the using statement would take care of rolling back the transaction before it hits my HandleUnhandledException method, so there shouldn't be two transactions involved here. Or am I wrong?

Answer 1

It happens because when your HandleUnhandledException method runs - you are still inside using block and it's finally part has not been run yet. It's easy to verify with this code:

class Program {
    static void Main(string[] args) {
        AppDomain.CurrentDomain.UnhandledException += OnUnhandledException;
        try {
            throw new Exception("test");
        }
        finally {
            Console.WriteLine("finally");
        }
    }

    private static void OnUnhandledException(object sender, UnhandledExceptionEventArgs e) {
        Console.WriteLine("handle exception");
    }
}

Which outputs:

handle exception
Unhandled exception: System.Exception: test ...
finally

You may want to read this question (and comments, from Eric Lipper for example) for discussion of related behavior. But I think reasons are not very relevant to your question - we can say it just works like this.

Given that information it's clear why you observe your exception - you are still inside uncommited transaction scope and trying to open connection to different database.

One solution that comes to mind in your case is check if we are inside transaction and suppress it if yes (because you anyway don't want your logging code to affect ambient transaction in any way):

private static void HandleException(Exception ex) {
    TransactionScope scope = null;
    if (Transaction.Current != null)
        scope = new TransactionScope(TransactionScopeOption.Suppress);
    try {
        // do stuff
    }
    finally {
        scope?.Dispose();
    }
}

Or maybe just always run inside supressed TransactionScope.