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
• NO_NOCK
• How you would do it in Transact-SQL
• How you do it in LINQ to SQL
• Conclusion
• Related Articles

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

• Improve Performance of your LINQ to SQL Queries - Query Compilation
• SQL 2008 TVP (Table Valued Parameters) and LINQ - It is Possible! (Sorta)

See ya