title: Parallel read of SQL Server FileStream not working
body: <p>I am trying to read the same SQL Server file stream in parallel threads, but am having no success.</p>
  
  <p>Mostly I get the following exception (although from time to time I get a other errors):</p>
  
  <blockquote>
    <p>System.InvalidOperationException: "The process cannot access the file specified because it has been opened in another transaction."</p>
  </blockquote>
  
  <p>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.</p>
  
  <p>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 <code>DependentTransaction</code> and copies the SQL Server file stream to a temporary file on the disk.</p>
  
  <p>If I change threadCount to 1, then the code works.</p>
  
  <p>Any idea why this fails?</p>
  
  <p>The code:</p>
  
  <pre><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 =&gt;
                  {
                      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;
      }
  }
  </code></pre>
  
  <p>kk</p>