LinqToSql.更新行时出现死锁.并行 [英] LinqToSql. Deadlock while updating a row. Parallel.For

问题描述

我有问题.我正在尝试使用并行更新数据库库.这是代码:

I have a problem. I'm trying to update the database base using parallel. Here is the code:

Parallel.For(rCnt, range.Rows.Count + 1, (jrCnt, loopState) =>
{
    var prcI = new Price(); // new 

    /*bla bla bla bla - bla bla - bla bla - bla */

    if ((!string.IsNullOrEmpty(prcI.name)) && (prcI.prc != 0)) // process add or update
    {
        prcI.company = nameprice;
        prcI.date = datatimeselect.Text; 

        Accessor.AddProductUpdateProduct(prcI); // main func

            /*bla bla bla bla - bla bla - bla bla - bla bla - bla  */
    }

这是要更新的功能代码字段:

Here's the function code field to update:

public static bool AddProductUpdateProduct(Price price)
    {
        bool add = false;
        var db = new PriceDataContext();

        var matchedprod =
           db.Price.Single(x => x.name == price.name && x.date != price.date && x.company == price.company); // find match

        if (matchedprod != null) // match FOUnDE
        {
            if (matchedprod.prc != price.prc)
            {
                matchedprod.date = price.date;
                matchedprod.prc = price.prc;
            }
            else
            {
                matchedprod.date = price.date;
            }
            db.SubmitChanges(); // DEADLOCK is her!!!
        }
        /*bla - bla bla - bla bla - bla bla - bla bla - bla */
    }

当我创建一条记录时，一切都很好！

When I create a record that all is well!

谢谢！

推荐答案

记录数在3000到10000之间(注释)，我将在这里查看一个使用SqlBulkCopy将数据推入临时表的解决方案(即看起来与您要处理的数据相似的表，但不是核心模型的一部分).这是将大量数据获取到服务器的最有效方法(尽管您可能还会查看表值参数).

With a record count between 3000 and 10000 (comments) I would be looking at a solution here that used SqlBulkCopy to push the data into a staging table (i.e. a table that looks similar to the data you are manipulating, but not part of your core model). This is the most efficient way of getting a bulk set of data to the server (although you might also look at table valued parameters).

然后使用服务器上的数据，我将执行一个更新(内部联接)和一个插入(不存在)，或者执行一个"upsert"(在SQL Server 2008及更高版本中可用).

With the data at the server, I would then do either one update (inner join) and one insert (where not exists), or a single "upsert" (available in SQL Server 2008 and above).

这将减少应用程序服务器上的CPU，网络和数据库资源.另外，由于插入/更新仅涉及一个SPID，因此没有死锁的风险.

This uses less CPU at the app-server, less network, and less database resources. Also, since only one SPID is involved in the insert/update there is no risk of deadlock.

这篇关于LinqToSql.更新行时出现死锁.并行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！