NOLOCK and Store Procedures

I am trying to find some documentation that will help me understand the benefits of using NOLOCK in stored procedures, if there is a benefit that is. Does anyone have some info that could help me out? Sites would be great, but personal experience is much appreciated.

Using NOLOCK politely asks SQL Server to ignore locks and read directly from the tables. This means you completely circumvent the lock system, which is a major performance and scalability improvement.

But there are also some risk factors like you may be getting some phantom reads or dirty reads as it ignores all locks and reads the table even when they undergoing changes. So you need to strike out a balance between the volume of modification which are carried on the tables.

quote:Originally posted by sachinsamuel
So you need to strike out a balance between the volume of modification which are carried on the tables.

Regards
Sachin Samuel

There are times when a high level of traffic to a table involving altering transactions (inserts/updates/deletes) shouldn't deter you from using noLock. To elaborate, you really have to know your data @ a system level, how does the application read/write to the db/table? We've seen some huge performance wins when adding noLock to a table that was by far the most traffic intensive table. On any given day there are some 100+ users pegging the table w/inserts and updates at a time, does this mean we should strike using noLock on it based on it's constant state of alteration? n this specific case though, the data is very user specific. Ie. User_A only ever accesses User_A's records, and User_B only ever accesses User_B's records, and so on. So even though the table is in a constant state of alteration as a whole, knowing that the system only read user specific records in all instances when the table is accessed showed tiny risk in using noLock on it.

ps. If noLock seems to liberal, consider lock hints of other granularities. For the most part, I've used noLock in probably 90% of the Selects I've written, and have thrown some rowLocks in when I know the records in question can be read and altered by multiple users at multiple times. Watch that you're not burning too many resources on such small lock granularites though.