How to implement this command to prevent deadlocks with LINQ to SQL?

Question

I would like to implement SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in my project that uses LINQ to SQL. I understand that this will affect all selected operators globally.

Am I putting this in my DAL that contains the context object? If so, how?

Thanks! Mark

Answer 1

You can do this based on a DataContext / unit of work as follows:

 using (var con = new SqlConnection(constr)) { con.Open(); using (var tran = new con.BeginTransaction(IsolationLevel.ReadUncommitted)) { using (var db = new MyDataContext(con)) { // You need to set the transaction in .NET 3.5 (not in 4.0). db.Transaction = tran; // Do your stuff here. db.SubmitChanges(); } tran.Commit(); } } 

Of course, you can abstract the creation, commit, and deletion of the connection and transaction, but this example will work.

Note that this will not set the isolation level globally, only for LINQ statements that execute in the context of this particular DataContext class.

Answer 2

Try setting up READ COMMITTED SNAPSHOT in the entire database.

See here: http://www.codinghorror.com/blog/2008/08/deadlocked.html

Answer 3

Linq, strictly speaking, is not a database query language. This is a domain query language that can be translated into a database query language, such as SQL. Thus, you cannot only use Linq to set the database isolation level.

I would look at the tools that ORM gives you; most, at some level, include SQLConnection and ADT-style SQLTransactions. You must be able to expose these "session" ORM objects in order to set isolation levels and execute other database commands other than DML.

Answer 4

I like the ideas in this article to create a base class that will set the desired level of transaction isolation for you. In my opinion, this function should have been included in the structure.

http://www.codeproject.com/KB/linq/LINQ_Transactions.aspx