我正在提取 SQL 文件表中文件的内容。如果我不使用并行，则以下代码有效。

同时(并行)读取 sql 文件流时出现以下异常。

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

长话短说:

在 Parallel.ForEach 中从 FileTable(使用 GET_FILESTREAM_TRANSACTION_CONTEXT)读取文件时出现上述异常。

供您试用的示例代码:

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:

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

最佳答案

交易没有流入Parallel.ForEach ，您必须手动将交易带入。

//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();
}

我也是从List<ExtractedContent>切换过来的至 ConcurrentQueue<ExtractedContent>因为不允许您调用 .Add(同时来自多个线程的列表。

关于c# - 线程和 SqlFileStream。进程无法访问指定的文件，因为它已在另一个事务中打开，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/30177583/