Our DBA came to us with information that our LINQ queries are creating many thousands of locks on the database. A developer on our team dug up this Hanselman post as a possible solution to our problem:
http://www.hanselman.com/blog/GettingLINQToSQLAndLINQToEntitiesToUseNOLOCK.aspx
Scott provides 3 ways in LINQ for setting NOLOCK. 1) TransactionScope (preferred), 2) SPROCS, 3) context.ExecuteCommand
We are a news site that is 99% reads, 1% writes so our major focus is on the speed of retrieval. Is NOLOCK a good strategy for all our LINQ-TO-SQL queries?
What I'm trying to understand is why using NOLOCK is or isn't a good idea. There must be a lot of people with our same goals: many fast reads, little to no updates. If NOLOCK is the obvious answer, then why isn't it the default? Why can't I make it a default on the context, instead of having to set it in every single data call?
Is NOLOCK really the best option for the many fast reads, few updates site?
UPDATE: In SQL Server 2005 and above, Is Snapshot isolation better than NOLOCK? I just found this http://msdn.microsoft.com/en-us/library/ms179599.aspx
which covers READ COMMITTED SNAPSHOT. This prevents write-block, but doesn't return dirty data? Should this be used 90% of the time over NOLOCK?
UPDATE 2: What's bothering me is DRY
The part that's bothering me most is that in order to implement either a no-lock or snapshot pattern, I have to change it on EVERY LINQ-to-SQL query method (except those being used in updates). This smells like a major violation of DRY.
Worth noting:
READ COMMITTED SNAPSHOTis (or at least was 2+ years ago) good enough for the site you're using.