title: Concurrent reads using SQLFileStream?
body: <p>Using .Net 4.52 and SQL Server 2014 with FILESTREAM</p>
  
  <p>We have a webservice that is failing on concurrent reads with "System.InvalidOperationException: The process cannot access the file specified because it has been opened in another transaction."</p>
  
  <p>I have isolated the code to reproduce the failure in a test program that spawns 10 concurrent tasks that read the same data with IsolationLevel.ReadCommitted and IO.FileAccess.Read. My understanding is thatthere will be a shared lock both in the database and the file system and there should be no "blocking".</p>
  
  <p>With a single task, the code works consistently. Sometimes it works with 2-3 tasks. With 10 tasks the code fails nearly consistently - every once in a while it works. I have considered that other programmers might be accessing the data since the database is on one of our development servers, but that wouldn't explain the nearly consistent failure with 10 tasks.</p>
  
  <p>Any suggestions as to what could be causing the failure would be greatly appreciated!</p>
  
  <p>The code driving the test:</p>
  
  <pre><code>    Dim profileKey As Guid = New Guid("DC3F1949-37DB-4D47-B204-0170FA4A40CD")
      Dim taskList As List(Of Task) = New List(Of Task)
      For x = 1 To 10
          Dim tsa As New TestSqlFileStream
          taskList.Add(Task.Run(Function() tsa.GetProfiles(profileKey, True)))
      Next
  
      Task.WaitAll(taskList.ToArray)
  </code></pre>
  
  <p>The class under test:</p>
  
  <p>Public Class TestSqlFileStream</p>
  
  <pre><code>Public Function GetProfiles(profileKey As Guid, getSmallVersionOfImage As Boolean) As List(Of Profile)
  
      Dim retProfiles = New List(Of Profile)
  
      Using conn As New SqlConnection("server=blah,1435; initial catalog=blah;Trusted_Connection=Yes;")
  
          conn.Open()
  
          Dim cmd = conn.CreateCommand()
          Dim iso As IsolationLevel = IsolationLevel.ReadCommitted
  
          cmd.Transaction = conn.BeginTransaction(iso)
  
          Try
              cmd.CommandText = "GetProfiles"
              cmd.CommandType = CommandType.StoredProcedure
              cmd.Parameters.Add(New SqlParameter("@profileKey", SqlDbType.UniqueIdentifier)).Value = profileKey
  
              Using reader As SqlDataReader = cmd.ExecuteReader
                  retProfiles = MapGetProfiles(reader, getSmallVersionOfImage)
              End Using
  
              cmd.Transaction.Commit()
  
          Catch ex As Exception
              cmd.Transaction.Rollback()
              Throw
          Finally
              conn.Close()
          End Try
  
      End Using
  
      Return retProfiles
  
  End Function
  
  Public Function MapGetProfiles(reader As SqlDataReader, getSmallVersionOfImage As Boolean) _
                         As List(Of Profile)
  
      Dim profiles As New List(Of Profile)
      Dim transactionToken As Byte()
      Try
          While reader.Read()
  
              Dim profile As New ServiceTypes.SubTypes.Profile
  
              profile.ParentKey = reader("ParentKey")
              profile.ProfileKey = reader("ProfileKey")
              profile.ProfileType = ConvertToProfileType(reader("ProfileType"))
              If reader("Active") Is Nothing Then profile.Active = False Else profile.Active = reader("Active")
              If IsDBNull(reader("Data")) Then profile.Data = Nothing Else profile.Data = reader("Data")
  
              Dim imagePath
              If getSmallVersionOfImage Then imagePath = reader("ImageThumbnailPath") Else imagePath = reader("ImagePath")
              transactionToken = DirectCast(reader("transactionContext"), Byte())
  
              If Not IsDBNull(imagePath) Then
  
                  If Not transactionToken.Equals(DBNull.Value) Then
                      LoadImage(profile, imagePath, transactionToken)
                  End If
  
              End If
  
              profiles.Add(profile)
  
          End While
  
      Catch ex As Exception
  
          Throw
      Finally
          reader.Close()
      End Try
  
  
      Return profiles
  
  End Function
  
  Public Sub LoadImage(ByRef profile As Profile, image As String, transactionContext As Byte())
  
      Using sqlFileStream = New SqlFileStream(image, transactionContext, IO.FileAccess.Read, FileOptions.SequentialScan, 0)                                    
          Dim retrievedImage = New Byte(sqlFileStream.Length - 1) {}
          sqlFileStream.Read(retrievedImage, 0, sqlFileStream.Length)
          profile.Image = retrievedImage
          sqlFileStream.Close()
  
      End Using
  
  End Sub
  
  Private Function ConvertToProfileType(profileType As String) As ProfileType
      Dim type = ServiceTypes.SubTypes.ProfileType.None
      Select Case profileType
          Case Nothing
              type = ServiceTypes.SubTypes.ProfileType.None
      End Select
      Return type
  End Function
  </code></pre>
  
  <p>End Class</p>
  
  <p><strong>Update:</strong> I have looked at this question but the issue is different because they were splitting to parallel within a transaction: <a href="https://stackoverflow.com/questions/30177583/threading-and-sqlfilestream-the-process-cannot-access-the-file-specified-becaus">Threading and SqlFileStream. The process cannot access the file specified because it has been opened in another transaction</a>
  In my example each task starts its own transaction.</p>
  
  <p><strong>Update2</strong> When I stop at a breakpoint within the transaction and run DBCC OPENTRAN in a query window the result is "No active open transactions" It seems like SqlConnection.BeginTransaction is not actually opening a transaction in the database.</p>
  
  <p><strong>Update3</strong> Also read from transaction log (after naming the transaction):</p>
  
  <pre><code>Use myDB
  GO
  select top 1000 [Current LSN],
         [Operation],
         [Transaction Name],
         [Transaction ID],
         [Transaction SID],
         [SPID],
         [Begin Time]
  FROM   fn_dblog(null,null)
  order by [Begin Time] desc
  </code></pre>
  
  <p>No transaction of the name I provided is showing in the log.</p>