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

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.