LinqToSql. Deadlock while updating a row. Parallel.For

Question

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!

Thank you!

How many records are you updating/inserting here? – Marc Gravell Dec 26 '11 at 08:34 — Marc Gravell, Dec 26 '11 at 08:34
differently from 3,000 to 10,000. – Jin Dec 26 '11 at 12:16 — Jin, Dec 26 '11 at 12:16

score 2 · Accepted Answer · answered Dec 26 '11 at 13:41

2

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

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

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.

answered Dec 26 '11 at 13:41

Marc Gravell

1,026,079
266
2,566
2,900

Now try to use SqlBulkCopy. And the data are best kept in a datatable and then upload? – Jin Dec 26 '11 at 13:58
@Jin can you rephrase? I don't quite understand your comment. But yes: DataTable is one way to throw data at a SqlBulkCopy instance. But it doesn't need to be in DataTable before that. – Marc Gravell Dec 26 '11 at 13:59
I read about SqlBulkCopy. It only adds to the base? About the update did not find the information. – Jin Dec 26 '11 at 14:26
@Jin yes, which is why you insert into a STAGING table (as I mentioned), and **then** do the insert/update to to main tables. – Marc Gravell Dec 26 '11 at 14:31
Well I have not all data is taken from one place. I have another column with the data row that are added to another function. Sorry for the misunderstanding. I am Russian and my English I know is perfect. If you could give an example. I am very grateful for your help. – Jin Dec 26 '11 at 14:44
@Jin your English is better than my Russian! You should be able to construct a DataTable with all the columns you need..? – Marc Gravell Dec 26 '11 at 15:04

score 0 · Answer 2 · edited May 23 '17 at 12:27

0

I guess it could be same problem I described in this question Deadlock on SELECT/UPDATE. It is not the problem with linq to sql. The problem with linq to sql is that you can't easily perform select with updlock.

edited May 23 '17 at 12:27

Community

1
1

answered Dec 26 '11 at 08:45

empi

15,755
8
62
78

Yes, but without the isolation-level, the first select should be isolated and release any read-locks after the select; also, without "Serializable" it shouldn't be a key-range lock. Not quite the same scenario, IMO – Marc Gravell Dec 26 '11 at 08:52
I do not understand how to implement. I am with this problem for a week already struggling. Tell me, please. – Jin Dec 26 '11 at 12:22
Do you have to do it in parallel? Your problem occurs because multiple threads are dealing with data in same table. Performing operation on single thread is the easiest workaround. Otherwise I would write stored procedure that will use select with updlock and then add or update. – empi Dec 26 '11 at 18:28

LinqToSql. Deadlock while updating a row. Parallel.For

2 Answers2

Linked