线程和SqlFileStream。因为它已经在另一个事务中被打开的进程无法访问指定的文件 [英] Threading and SqlFileStream. The process cannot access the file specified because it has been opened in another transaction

问题描述

我抽取SQL文件表中的文件内容。如果我不使用并行下面的代码工作。

I am extracting content of the Files in SQL File Table. The following code works if I do not use Parallel.

我收到以下异常，同时读取SQL文件流时（并行）。

该进程无法访问指定的，因为它在其他事务已打开的文件。

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

在读取从的FileTable文件中并行（使用GET_FILESTREAM_TRANSACTION_CONTEXT） 。.ForEach我得到了上面的异常

TL;DR:

When reading a file from FileTable (using GET_FILESTREAM_TRANSACTION_CONTEXT) in a Parallel.ForEach I get the above exception.

https://gist.github.com/NerdPad/6d9b399f2f5f5e5c6519

提取附件，并提取内容：

var documents = new List<ExtractedContent>();
using (var ts = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
    var attachments = await dao.GetAttachmentsAsync();

    // Extract the content simultaneously
    // documents = attachments.ToDbDocuments().ToList(); // This works
    Parallel.ForEach(attachments, a => documents.Add(a.ToDbDocument())); // this doesn't

    ts.Complete();
}

DAO读取文件表：

public async Task<IEnumerable<SearchAttachment>> GetAttachmentsAsync()
{
    try
    {
        var commandStr = "....";

        IEnumerable<SearchAttachment> attachments = null;
        using (var connection = new SqlConnection(this.DatabaseContext.Database.Connection.ConnectionString))
        using (var command = new SqlCommand(commandStr, connection))
        {
            connection.Open();

            using (var reader = await command.ExecuteReaderAsync())
            {
                attachments = reader.ToSearchAttachments().ToList();
            }
        }

        return attachments;
    }
    catch (System.Exception)
    {
        throw;
    }
}

为每个文件创建对象：
对象包含对GET_FILESTREAM_TRANSACTION_CONTEXT

public static IEnumerable<SearchAttachment> ToSearchAttachments(this SqlDataReader reader)
{
    if (!reader.HasRows)
    {
        yield break;
    }

    // Convert each row to SearchAttachment
    while (reader.Read())
    {
        yield return new SearchAttachment
        {
            ...
            ...
            UNCPath = reader.To<string>(Constants.UNCPath),
            ContentStream = reader.To<byte[]>(Constants.Stream) // GET_FILESTREAM_TRANSACTION_CONTEXT() 
            ...
            ...
        };
    }
}

阅读使用SqlFileStream文件：
异常这里抛出

public static ExtractedContent ToDbDocument(this SearchAttachment attachment)
{
    // Read the file
    // Exception is thrown here
    using (var stream = new SqlFileStream(attachment.UNCPath, attachment.ContentStream, FileAccess.Read, FileOptions.SequentialScan, 4096))
    {
        ...
        // extract content from the file
    }

    ....
}

更新1：

根据这个文章看起来它可能是一个隔离级别问题。有没有人遇到了类似的问题？

Update 1:

According to this article it seems like it could be an Isolation level issue. Has anyone ever faced similar issue?

推荐答案

不于流动交易 Parallel.ForEach ，您必须手动带来的交易。

The transaction does not flow in to the Parallel.ForEach, you must manually bring the transaction in.

//Switched to a thread safe collection.
var documents = new ConcurrentQueue<ExtractedContent>();
using (var ts = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
    var attachments = await dao.GetAttachmentsAsync();
    //Grab a reference to the current transaction.
    var transaction = Transaction.Current;
    Parallel.ForEach(attachments, a =>
    {
        //Spawn a dependant clone of the transaction
        using (var depTs = transaction.DependentClone(DependentCloneOption.RollbackIfNotComplete))
        {
            documents.Enqueue(a.ToDbDocument());
            depTs.Complete();
        }
    });

    ts.Complete();
}

我也是从列表与LT开关; ExtractedContent> 到 ConcurrentQueue< ExtractedContent> ，因为你不能调用。新增（从多个名单上线程同时

I also switched from List<ExtractedContent> to ConcurrentQueue<ExtractedContent> because you are not allowed call .Add( on a list from multiple threads at the same time.

这篇关于线程和SqlFileStream。因为它已经在另一个事务中被打开的进程无法访问指定的文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！