使用ExecuteNonQuery时在Parallel.Foreach中出现死锁? [英] Deadlock in Parallel.Foreach while using ExecuteNonQuery?

问题描述

使用Parallel.Foreach时遇到死锁错误. 我在数据表中有1000条记录，我创建了5个线程来处理它. 但是，当我运行此控制台应用程序时，在处理了一些记录之后，它将创建死锁，而不会处理其他记录. 这是我的代码:

I am facing deadlock error while using Parallel.Foreach. I have 1000 records in datatable and i'hv created 5 threads to process it. but when i'hv run this console application then after some records processed it will create a deadlock and no other records will process. Here is my code :

Parallel.ForEach(dt1.AsEnumerable(), new ParallelOptions { MaxDegreeOfParallelism = 5 }, dr =>
{
    cmd1.CommandText = $"Update AuditMessage set Status=1" +
        $" where SXAEASCoreAuditMessageID ='{Convert.ToString(dr["AuditMessageID"])}' ";
    cmd1.CommandType = CommandType.Text;
    cmd1.Transaction = trans;
    cmd1.ExecuteNonQuery();                          
});

推荐答案

为什么锁定?

您的问题可能不是数据库并发，而是每个线程使用的cmd1对象上的并发.

Your problem is probably not database concurrency, but concurrency on the cmd1 object, which you are using for every thread.

您需要为每个线程或每次迭代创建一个新的SqlCommand.

You need to create a new SqlCommand for each of the threads, or in every iteration.

Parallel.ForEach(dt1.AsEnumerable(), new ParallelOptions { MaxDegreeOfParallelism = 5 }, dr =>
    {
                  using(var cmd = new SqlCommand(connection))
                  {
                     cmd.CommandText = $"Update AuditMessage set Status=1 where SXAEASCoreAuditMessageID ='{Convert.ToString(dr["AuditMessageID"])}' ";
                     cmd.CommandType = CommandType.Text;
                     cmd.Transaction = trans;
                     cmd.ExecuteNonQuery();
                  }                          
    });

有关行/表锁定的一些基础知识.

如果您正在使用SQL Server，那么所产生的开销将超过并行处理的速度.修改的每一行都将由DBMS锁定，最终，当开销增加时，它将开始执行表锁定，无论如何您的线程仍将按顺序工作.

In case you are doing is on SQL Server, you are creating more overhead than speed out of this parallel processing. Every row modified will be locked by the DBMS, and eventually, when the overhead gets to high, it will commence a table lock, leaving your threads working sequentially anyway.

因此，在这里忘记了parallel.foreach，让数据库进行效率优化

您可以在列表中选择AuditMessageIds. 然后将其切成100个左右的片段，然后进行更智能的更新查询，例如将批量更新为状态1的查询.

You could select the AuditMessageIds into a List. Then cut it to pieces of 100 or so, and then make a smarter update query, like a query which does the update to status 1 in bulks..

where SXAEASCoreAuditMessageID IN ...

这篇关于使用ExecuteNonQuery时在Parallel.Foreach中出现死锁?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！