Monday, 19 March 2012

NOLOCK is the SQL hint

NOLOCK is the SQL hint that allows SQL Server to ignore the normal lock that placed and held for transaction and allow the query to complete without having wait.

Take a simple example:

In normal condition of SQL server User –A proceed a transaction for Table-A. Now the User-B tries to access the Table for reading data (Like SELECT statements). In normal conditions of the SQL server it is NOT possible. The User –A must complete the transaction by Committing or Roll backing it before User—B Access the Table for Reading.


Think about the situation, where User—A takes 15 to 20 minutes to complete the transaction and User—B don't have enough time to wait. In such condition we used NOLOCK query hint.

However you need to be very carefully with using NOLOCK. Remember you can get some records that were partially updated or inserted. It is safe to use NOLOCK on rows that you know are not changing right now. For example, records that belong to some user and he is running reports, but not updates, however some users can do updates / inserts at the same time.

 The syntax is mentioned bellow:

SELECT fname, lname

FROM   student_master WITH(NOLOCK)

WHERE  sroll=123


Hop you like this.


Posted by: MR. JOYDEEP DAS