CODEWITHSUNDEEP
c#sqlsql-serverlinq-to-sqlazure-sql-database
ABC
ABC

Reputation: 695

Deadlock using linq to sql select

I'm using linq to sql and running a direct SQL query - select statement - which queries a XML column using (XQuery). Multiple such queries are run in parallel and I'm getting deadlock exception.

Does select acquire some sort of lock when running? There are also updates happening in parallel with Serializable isolation level.

Any pointers how to get this problem resolved could be helpful. How do I specify NOLOCK in Linq To SQL?

Upvotes: 2

Views: 4610

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294407

Why are you getting a deadlock? SELECT on its own cannot deadlock with another SELECT. There must be more happening. Capture the deadlock XML graph and post it here so we can help you analyze it. Once we have the deadlock understanding we can recommend a solution, which is almost always a required index which you're missing.

Do NOT enable dirty reads (NOLOCK hints or read uncommitted isolation level), that is not the right approach. Dirty reads are inconsistent reads and return incorrect data. If needed, row versioning-based reads may be required, that's how this very site once solved it's issues.

Upvotes: 3

Mayank Pathak
Mayank Pathak

Reputation: 3681

I checked it on Scott Hanselman's Blog post. An example to try it like this 

ProductsNewViewData viewData = new ProductsNewViewData();
using (var t = new TransactionScope(TransactionScopeOption.Required,
new TransactionOptions { 
    IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted 
}))
{
  viewData.Suppliers = northwind.Suppliers.ToList();
  viewData.Categories = northwind.Categories.ToList();
}

Hope its helpful for you. read it more on his blog post.

Upvotes: 3

Related Questions