5/30/11

LINQ to SQL with NOLOCK

If your project uses LINQ to SQL for your database access, you may want to use NOLOCK to set the isolation level to ReadUncommitted on your select statements. This prevents the database engine from issuing locks on the tables that we are trying to select data from which helps increase concurrency and performance.


The area of concern is that LINQ does not understand query hints. The way this works with LINQ is by running your select statements under the context of a transaction scope as follows:



string type = string.Empty;
og-bit.com

using (DataContext ctx = new DataContext(ConnectionString))
{
  using (var tx = new System.Transactions.TransactionScope(TransactionScopeOption.Required,
         new TransactionOptions() { IsolationLevel = IsolationLevel.ReadUncommitted }))
  {
         var row = (from record in ctx.Log
                    where record.Id == 99999
                    select record).FirstOrDefault();
         type = row.Type;
  }
}

The code here creates a transaction scope and sets the isolation level to ReadUncommitted (NOLOCK). The select statement runs under the scope of the transaction, and it returns the value from a query. The using clause ensures that both the transactions as well as the data context are disposed once the code goes out of scope.


There is also the preferable option to use stored procedures to eliminate the need to use transaction scope. In the store procedure, you have the flexibility to use query hints.


I hope this helps.

og-bit.com