Saturday, March 19, 2011

Uncommitted Reads in LINQ: Avoiding Deadlocks and Timeouts!

This short post today is about a performance enhancement in LINQ to SQL: Reading uncommitted data. You have to know that it's not a LINQ to SQL concept but a straight SQL Server concept.

But read on to know how they relate to each other.

Table of Contents



Introduction


Hi

By default in MS SQL, you're always reading committed data and that's fine but in some scenarios, you have to tweak your queries a little. Fortunately, you have a way of telling MS SQL you want to read uncommitted data from LINQ and that's the purpose of this article today!

NO_NOCK


In Transact-SQL, "NO_LOCK indicates that no shared locks are issued against the table that would prohibit other transactions from modifying the data in the table". It basically says: If I'm beginning to read a table, others can modify that table because they won't see any shared lock on it (the one I would have put on it). What's also very very important is that it works the other way around too: If something is currently modifying the table I can jump in and still read the table at the same time!

Let's not get ahead of ourselves and put NO_LOCK everywhere. One of the main goals of a database is data consistency. You must understand that you can retrieve "dirty" records if you specify NO_LOCK in your queries. Okay, now you do or you don't? You must be really sure when to use NO_LOCK and this is not the purpose of this article. But an example would be that: two clients are reading/updating the same table but are acting on different columns. They don't step on each other's feet, so why not dance at the same time?! Another example: records are being purged from a table but at the same time you know you won't read those records with your query. You may have other examples...

So let's see some code now:

How you would do it in Transact-SQL


SELECT * FROM Table WITH(NOLOCK)

How you do it in LINQ to SQL


TransactionOptions transOptions = new TransactionOptions() { IsolationLevel = IsolationLevel.ReadUncommitted };
using (new TransactionScope(TransactionScopeOption.Required, transOptions))
{
    // Your LINQ here will carry the WITH(NO_LOCK) when translated in SQL
}

Conclusion


Well, told you it would be short. So you see with good planning and with a good strategy you can go from a "SQL Timeout" error to a pretty quick query. You must be careful though and know what you're doing.

I'm putting time and effort on this blog and having you here is my reward. Make me feel better and better everyday by spreading the love with the buttons below! Also, don't hesitate to leave a comment. Thanks for reading!

Related Articles



See ya

2 comments:

Anonymous Avatar Anonymous said...

I have used this technique and it all seems to work fine but I cannot verify it. If you look at the sql generated by the linq query using the read uncommitted transaction scope there is nothing like with(noLock) in the sql. It looks exactly the same as the sql generated with default transaction scope settings. Where or how does this get communicated to the database?

Anonymous Avatar Anonymous said...

I would guess it translates to:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

©2009-2011 Mike Gleason jr Couturier All Rights Reserved