SQL Server Performance Forum – Threads Archive

NOLOCK and Store Procedures

Hello All, 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. Thanks in advanced!

---

Well, I think I found something useful. For those of you who had the same question, check this link out. http://www.sql-server-performance.com/reducing_locks_hints.asp Have a great day!

---

Hi, 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. Regards
Sachin Samuel

---

Thanks Sachin!

---

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.

---

Good Lord…. Do you have a blocking problem? What’s your symptoms? DON’T USE (NOLOCK) It’s probably the fastest way to hose your database
Brett Hint: Want your questions Answered Fast? Read Below
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

---

Be carefull, if a query using the no lock hint is scanning a data page that another query deletes the no lock query will fail.

---

hi,<br /><br />read below links also :<br />1). <a href=’http://www.sql-server-performance.com/rd_table_hints.asp’ target=’_blank’ title=’http://www.sql-server-performance.com/rd_table_hints.asp'<a target="_blank" href=http://www.sql-server-performance.com/rd_table_hints.asp>http://www.sql-server-performance.com/rd_table_hints.asp</a> </a> <br />2). <a href=’http://www.sql-server-performance.com/hints.asp’ target=’_blank’ title=’http://www.sql-server-performance.com/hints.asp'<a target="_blank" href=http://www.sql-server-performance.com/hints.asp>http://www.sql-server-performance.com/hints.asp</a></a><br />3). <a href=’http://www.sql-server-performance.com/reducing_locks_hints.asp’ target=’_blank’ title=’http://www.sql-server-performance.com/reducing_locks_hints.asp'<a target="_blank" href=http://www.sql-server-performance.com/reducing_locks_hints.asp>http://www.sql-server-performance.com/reducing_locks_hints.asp</a></a><br />4). <a href=’http://www.sql-server-performance.com/absolutenm/templates/?a=217&z=0′ target=’_blank’ title=’http://www.sql-server-performance.com/absolutenm/templates/?a=217&z=0′<a target="_blank" href=http://www.sql-server-performance.com/absolutenm/templates/?a=217&z=0>http://www.sql-server-performance.com/absolutenm/templates/?a=217&z=0</a></a> <br />5). <a href=’http://www.sql-server-performance.com/reducing_locks.asp’ target=’_blank’ title=’http://www.sql-server-performance.com/reducing_locks.asp'<a target="_blank" href=http://www.sql-server-performance.com/reducing_locks.asp>http://www.sql-server-performance.com/reducing_locks.asp</a> </a> <br /><br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br />Regards<br /><br />Hemantgiri S. Goswami<br />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami<br />

---

Brett, do you mind elaborating on this?

---

]]>