SQL transaction not closed when rolling back a file operation fails

[NielsFestjens]

We're using ChinDo TxFileManager to be able to rollback file operations when we're rolling back our SQL transaction. This works fine, except when rolling back a file throws an exception (because the file is locked or no longer exists). When that happens, the SQL transaction is kept alive even after we leave the scope, and there's no Transaction left to dispose.

I've reproduced the problem in a small Console Application, where I move the file without the TxFileManager to get the rollback to fail.
I let the console run for a minute afterwards to replicate the behaviour we have in our web application.
As long as the application is alive, the transaction is not rolled back.

using System;
using System.Data.SqlClient;
using System.IO;
using System.Threading;
using System.Transactions;
using ChinhDo.Transactions;

namespace TransactionRollback
{
    class Program
    {
        static void Main(string[] args)
        {
            Test();
            Thread.Sleep(60 * 1000);
        }

        public static void Test()
        {
            File.WriteAllText(@"C:\temp\txFiles\MyFile.txt", "test"); // generate a test file.
            try
            {
                var scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted });
                try
                {
                    // start a connection within the TransactionScope and execute a command that locks a row.
                    using var connection = new SqlConnection("Data Source=.;Initial Catalog=MyDatabase;Integrated Security=true");
                    connection.Open();
                    var command = connection.CreateCommand();
                    command.CommandText = @"Update Document SET Path='C:\temp\txFiles\MyFile2.txt' WHERE Id=1";
                    command.ExecuteNonQuery();

                    // use the transactional FileManager (from ChinDo) to move the file we created.
                    var fileManager = new TxFileManager();
                    fileManager.Move(@"C:\temp\txFiles\MyFile.txt", @"C:\temp\txFiles\MyFile2.txt");

                    // delete the moved file without the transactional FileManager so the rollback will fail.
                    File.Delete(@"C:\temp\txFiles\MyFile2.txt");
                }
                finally
                {
                    try
                    {
                        // this throws an exception because it tried to rollback a file that doesn't exist.
                        // this leaves the actual SQL transaction alive.
                        scope.Dispose();
                    }
                    catch (Exception rollbackException)
                    {
                        Console.WriteLine(rollbackException);
                    }
                }
            }
            catch (Exception exception)
            {
                Console.WriteLine(exception);
            }
        }
    }
}

I don't think the rollback should throw errors when it fails to perform a rollback action, because that messes up the distributed transaction.

My current solution is to get the SPID before I rollback, and if the rollback fails I send a command in a new connection to kill that SPID, but that seems sketchy at best.

[chinhdo]

Hello @NielsFestjens Let me think about this some more. I think we are between a rock and a hard place when this happens.