Was the transaction (process ID 56) locked during the lock?

Question

Here I work with Linq to sql. My table has more than 30,000 rows .

I used the following query to retrieve a record from a 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(); } 

For some time, my request is successful or for some time when I received an Error: Transaction (process ID 56) was blocked when locking | communication buffer resources with another process and was selected as a victim of deadlock. Restart the transaction.

Thanks in advance...

Answer 1

It looks like you have a general lock lock issue while scanning the / clustindex table and updating / deleting at the same time

Look at your final query and at its paln - if it has such a scan - try to avoid it with the right index or hack it for use (NOLOCK). But nolock hint is not suitable if you need accurate and consistent data without possible phantom lines

And - first of all, look at the deadlock chart!