Transaction (Process ID 56) was deadlocked on lock?

Question

Here I am working with Linq to sql I have more then 30000 row in my table.

I used following query for fetching record from database :

IEnumerable<DealsDetails> DD = (from D in DealDbContext1.Deals
                                            where D.Address == City && (D.DealTime >= DateTime.Now || D.DealTime == dealcheck) && PriceMax >= D.DealPrice && D.DealPrice >= PriceMin && DisCountMax >= D.SavingsRate && D.SavingsRate >= DiscountMin && (D.DealTime >= DateTime.Now.AddDays(TimeMin) && D.DealTime <= DateTime.Now.AddDays(TimeMax) || D.DealTime == dealcheck)
                                            select new DealsDetails(
                                                lst,
                                                D.DealId,
                                                D.DealHeadline,
                                                D.DealCategory,
                                                D.BuyPrice,
                                                D.DealPrice,
                                                D.SavingsRate,
                                                D.SavingAmount,
                                                D.RelatedWebsite,
                                                D.Address,
                                                string.Empty,
                                                D.DealImage,
                                                string.Empty,
                                                string.Empty,
                                                D.Time, D.CurrentTime, D.DealTime,
                                               D.Location, string.Empty, string.Empty, D.Latitude, D.Longitude, D.Islocal, D.VendorMail, D.MerchantInfo, D.Review, D.HowItWork, D.DealUrl
                                                ));



if (lstSite.Count > 0 && lstSite[0] != "AllDeals")
                {
                    DD = DD.Where(D => D.RelatedWebsite.Split(',').Where(x => lstSite.Contains(x)).Any()); //.Where(row => row.Category.ToList().Where(x => lst.Contains(x)).Any()).ToList();
            }

Some time my query run successfully or some time I got Error : Transaction (Process ID 56) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Thanks in advance...

There'll be a deadlock entry in SQL Server's error log, could you post that? If not, turn on traceflag 1204 or 1222 — Andomar
What version of SQL Server? If 2008 see if you can get the deadlock graph from the default extended events session. Otherwise set up to capture this next time. — Martin Smith
Sql server's error log : "The client was unable to reuse a session with SPID 81, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message." — Yashwant Kumar Sahu

Oleg Dok Oleg Dok · Accepted Answer · 2012-01-21T17:55:37

Looks like you have general deadlock locking issue with simultaneous table/clustindex scan and update/delete

Look at your resulting query and at its paln - if it have such a scan - try to avoid it with proper indexing or hack it to use (NOLOCK) hint. But nolock hint is not good if you need exact and consistent data without possible phantom rows

And - first of all look at deadlock graph!

Transaction (Process ID 56) was deadlocked on lock?

1 Answers