No Lock

Question

Hi friends....... What is a NOLOCK and how we use it? Thanks.......

Vulpes · Answer

NOLOCK tells SQL server to ignore the locks that are normally applied to a transaction and so allows a query to complete without waiting for the transaction to end and the locks to be released. The advantage is improved performance for queries on large tables. The downside is that you may end up with some incomplete new records or some records that have only been partially updated because they weren't locked at the time. You therefore need to be careful how you use it.

Vikas Mishra · Answer

Thanks. vulpes.....

Datta Kharad · Answer

Hi You can refer these link... http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx E xample:- http://www.techrepublic.com/article/using-nolock-and-readpast-table-hints-in-sql-server/6185492

Satyapriya Nayak · Answer

Hi Vikas Using the NOLOCK query optimiser hint is generally considered good practice in order to improve concurrency on a busy system. When the NOLOCK hint is included in a SELECT statement no locks are taken when data is read. The result is a Dirty Read which means that another process could be updating the data at the exact time you are reading it. There are no guarantees that your query will retrieve the most recent data. The advantage to performance is that your reading of data will not block updates from taking place and updates will not block your reading of data. SELECT statements take Shared (Read) locks. This means that multiple SELECT statements are allowed simultaneous access but other processes are blocked from modifying the data. The updates will queue until all the reads have completed and reads requested after the update will wait for the updates to complete. The result to your system is delay(blocking). Please refer the below link http://www.codeproject.com/KB/database/NoLockHintBehaviour.aspx Thanks