How do I 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 which uses LINQ to SQL. My understanding is that this will affect all select statements globally.

Do I put this in my DAL which contains the context object? If so, how?

Thanks! Mark

Answer 1

Try setting READ COMMITTED SNAPSHOT on the entire database.

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

Answer 2

Linq, strictly speaking, is not a database query language. It is a domain query language that can be translated to a DB query language like SQL. As such, you cannot use just Linq to set a database isolation level.

I would look at the tools your ORM gives you; most, at some level, involve an ADO-style SQLConnection and SQLTransactions. You should be able to expose these given your ORM's "session" object, in order to set isolation levels and execute other non-DML database commands.

Answer 3

You can do this on a per DataContext / unit of work basis like this:

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 and committing and disposal of the connection and transaction away, but this example will work.

Note that this will not set the isolation level globally, just for the LINQ statements that are executed within the context of that particular DataContext class.

Answer 4

I like the ideas in this article for creating a base class that will set the desired transaction isolation level for you. In my view, this feature should have been included in the framework.

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