[SOLVED] Concurrent reads using SQLFileStream?

Using .Net 4.52 and SQL Server 2014 with FILESTREAM

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."

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".

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.

Any suggestions as to what could be causing the failure would be greatly appreciated!

The code driving the test:

    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)

The class under test:

Public Class TestSqlFileStream

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

End Class

Update: I have looked at this question but the issue is different because they were splitting to parallel within a transaction: Threading and SqlFileStream. The process cannot access the file specified because it has been opened in another transaction In my example each task starts its own transaction.

Update2 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.

Update3 Also read from transaction log (after naming the transaction):

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

No transaction of the name I provided is showing in the log.