LINQ to SQL and NOLOCK Hints
In our SQL queries here at work, we use the NOLOCK hint a lot. Not locking rows during reads is very important to us, and our transactions are seldom rolled back so the queries generally return data that's accurate enough for the tasks at hand.
Now that we're starting to investigate LINQ to SQL, then, we're realising that the queries themselves don't allow any kind of hints. They're generated from your code and passed to the server without any intervention from you.
How, then, can we achieve the equivalent of WITH (NOLOCK) when using LINQ to SQL? The answer lies in transactions.
The NOLOCK hint is essentially the same as wrapping a query in a transaction whose "isolation level" is set to "read uncommitted". It means that the query doesn't care if stuff is in the process of being written to the rows it's reading from - it'll read that "dirty" data and return it as part of the result set.
Turns out that you can do the whole "read uncommitted" transaction thing using the old System.Transactions namespace introduced in .NET 2.0. Here's some sample code:
using (var txn = new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted
}
))
{
// Your LINQ to SQL query goes here
}
So I'm creating a new TransactionScope object and telling it to use a read-uncommitted isolation level. The query within the "using" statement now acts as if all its tables were reading with the NOLOCK hint.
That's pretty handy to know! Of course, I'm still on the cusp of moving to a stored-procedure-based system where I can tailor the queries myself. Anyone got any opinions on that front?
# Trackback from DotNetKicks.com on 29/02/2008 10:09 AM
# Trackback from Really nice solution to deadlocks in sql azure « Bradley Smith on 28/04/2011 4:23 PM
Comments
# Andrew Tobin
19/02/2008 12:24 PM
Worth noting is that LINQ doesn't actually make the call to SQL until you are using the collection you are returning.
IE you can make a:
using (as above)
{
var records = ... LINQ Query;
}
//The actual sql query will be called here outside the collection.
foreach(record in records)
{
_myRecordSet.Add(record);
}
That means that it will be outside the scope of the transaction and instead you want to use whatever step you are actually fetching the data for within the using like so:
using (as above)
{
var records = ... LINQ Query;
//This will execute the query in the scope of the transaction.
foreach(record in records)
{
_myRecordSet.Add(record);
}
}
I could be wrong about this, but I've got a feeling that with the late calling of the LINQ it will behave this way.
Andrew
# Adam
22/07/2008 10:10 AM
Does this actually cause the generated SQL code to perform the same way, I mean does it actually cause the generated sql to include the nolock hint ?
or does it achieve this level of isolation by changing the connection object in some way ?
I guess it's time for me to do some digging.
# mabster
22/07/2008 10:39 AM
Hi Adam,
Yeah, this won't change the SQL. It wraps the statement in a transaction with an isolation level such that you get the same effect.
(And be sure to read Andrew's comment - the actual query isn't fired until you try to use it, so don't end your transaction before at least converting the query to a List or something.)
Cheers,
Matt
# Marcel
19/08/2008 12:29 AM
How can I check that my code works good on SQL 2000 so that I am sure that the transaction is executed?
# CRM-Consultant
29/10/2009 8:37 PM
So if you are using Linq-to-Sql with extension methods to populate your "where" clauses, does that mean that the Database Transaction Level has to be set in the business logic? I would probably feel a little uncomfortable doing that. Best Practices and all that.......
# mabster
30/10/2009 8:01 AM
Yeah, the best option is to change the isolation on your database. Have a look at this old discussion on Stack Overflow:
stackoverflow.com/.../diagnosing-dead
# Mike
5/11/2009 7:53 AM
I use a much simpler approach:
<System.Runtime.CompilerServices.Extension()> _
Public Sub SetNoLockOn(ByVal thisDataContext As DataContext)
thisDataContext.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED")
End Sub
<System.Runtime.CompilerServices.Extension()> _
Public Sub SetNoLockOff(ByVal thisDataContext As DataContext)
thisDataContext.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL READ COMMITTED")
End Sub
then I just call:
MyDataContext.SetNoLockOn
right after I create my DataContext.
# nick chan
30/12/2009 6:33 PM
at work, some of my stored procedures runs on with combination of lock and nolock under 1 transaction. could this solution work ?
# Dasith
11/05/2011 1:37 PM
Hey, I tried your solution with SQL server profiler. All I can see is read-committed in a set-transaction isolation level line.
I made a post on SOF about this stackoverflow.com/.../linq-transactio