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

Answer 1

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!