2

I am trying to read the same SQL Server file stream in parallel threads, but am having no success.

Mostly I get the following exception (although from time to time I get a other errors):

System.InvalidOperationException: "The process cannot access the file specified because it has been opened in another transaction."

I have searched the internet, and found just a few posts, but as I understand this is supposed to work. I'm using SQL Server 2008 R2.

I've simplified the code to the following: the main code opens a main transaction, and then runs 2 threads in parallel, each thread using a DependentTransaction and copies the SQL Server file stream to a temporary file on the disk.

If I change threadCount to 1, then the code works.

Any idea why this fails?

The code:

class Program
{
    private static void Main(string[] args)
    {
        string path = Path.Combine(Path.GetTempPath(), Path.GetRandomFileName());
        Directory.CreateDirectory(path);

        try
        {
            using (var transactionScope = new TransactionScope(TransactionScopeOption.Required))
            {
                TransactionInterop.GetTransmitterPropagationToken(Transaction.Current);

                const int threadCount = 2;
                var transaction = Transaction.Current;

                // Create dependent transactions, one for each thread
                var dependentTransactions = Enumerable
                    .Repeat(transaction.DependentClone(DependentCloneOption.BlockCommitUntilComplete), threadCount)
                    .ToList();

                // Copy the file from the DB to a temporary files, in parallel (each thread will use a different temporary file).
                Parallel.For(0, threadCount, i =>
                {
                    using (dependentTransactions[i])
                    {
                        CopyFile(path, dependentTransactions[i]);

                        dependentTransactions[i].Complete();
                    }
                });
                transactionScope.Complete();
            }
        }
        finally
        {
            if (Directory.Exists(path))
                Directory.Delete(path, true);
        }
    }

    private static void CopyFile(string path, DependentTransaction dependentTransaction)
    {
        string tempFilePath = Path.Combine(path, Path.GetRandomFileName());

        // Open a transaction scope for the dependent transaction
        using (var transactionScope = new TransactionScope(dependentTransaction, TransactionScopeAsyncFlowOption.Enabled))
        {
            using (Stream stream = GetStream())
            {
                // Copy the SQL stream to a temporary file
                using (var tempFileStream = File.OpenWrite(tempFilePath))
                    stream.CopyTo(tempFileStream);
            }

            transactionScope.Complete();
        }
    }

    // Gets a SQL file stream from the DB
    private static Stream GetStream()
    {
        var sqlConnection = new SqlConnection("Integrated Security=true;server=(local);initial catalog=DBName");
        var sqlCommand = new SqlCommand {Connection = sqlConnection};

        sqlConnection.Open();
        sqlCommand.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()";

        Object obj = sqlCommand.ExecuteScalar();
        byte[] txContext = (byte[])obj;

        const string path = "\\\\MyMachineName\\MSSQLSERVER\\v1\\DBName\\dbo\\TableName\\TableName\\FF1444E6-6CD3-4AFF-82BE-9B5FCEB5FC96";
        var sqlFileStream = new SqlFileStream(path, txContext, FileAccess.Read, FileOptions.SequentialScan, 0);

        return sqlFileStream;
    }
}

kk

Metheny
  • 1,112
  • 1
  • 11
  • 23
  • `TransactionScope` with dependent transactions is fancy-pantsy stuff. Consider breaking out an SQL Profiler to see what it's actually doing under the hood in terms of `BEGIN TRANSACTION` / `COMMIT` and sessions. Alternatively, if you don't really need the high level of abstraction it offers, consider using explicit transactions instead (as in, `connection.BeginTransaction`). Even if you *do* need it, it may be worth experimenting with that to see what will work. One connection can execute only one command, parallelism or not, and transactions across connections are fraught with peril. – Jeroen Mostert Feb 20 '18 at 17:28
  • Thank you for the quick reply. I need the whole code to be transactional and parallel. I simplified my original code that instead of copying to a temporary file, it processes the file in a rollbackable way. Correct me if I'm wrong but if I use BeginTransaction it will create a separate transaction for each thread, so if one of ythe threads fail, the others will not roll back. Also, in the code each thread opens a connection and runs a single command. – Metheny Feb 20 '18 at 18:03
  • 1
    The core of my remarks is this: no matter what the client code does, one physical SQL Server connection cannot process more than one command at a time (pseudo-parallelism like MARS excluded), and no transaction can span a physical connection, unless distributed. SQL Server is not at all concerned with how you arrange your threads, but if two threads run a command at the same time, they necessarily must use a different physical connection (not merely reuse the same pooled connection, as is normal). This means that, to make what you want possible, the .NET code has to do interesting things... – Jeroen Mostert Feb 20 '18 at 19:28
  • ...and I don't actually know what interesting things these are. So before I forget, have an upvote. I currently lack the time to properly dig in to this situation. – Jeroen Mostert Feb 20 '18 at 19:29
  • Actually in my case it is in fact a distributed transaction, in the original code every thread actually copies the file to a DB in different machines (via WCF operations), which is why I need it both transactional and parallel. Anyway, if this code is wrong, what would be the right way to achieve what I need (i.e. perform the multiple file copying in parallel and transactional)? – Metheny Feb 20 '18 at 21:54

0 Answers0