SQL : NOLOCK

This time i am here with one of the fascinated member of SQL family which we love to call is "NOLOCK"

What is NOLOCK
As the name suggest, it will help in accessing the locked data.

Why NOLOCK
Sometime it is really required to access the data which is locked by some other transaction, hence we are in requirement of NOLOCK.

Where NOLOCK
It is used with SELECT command of SQL.

USE of NOLOCK
Let's start the use of NOLOCK with the help of example.

Step 1 :
We have a table named "NOLOCKS" and fired the query to retrieve data

Step 2 :
Fire update query under TRANSACTION command without giving ROllBACK or COMMIT command at the end

Step 3 :
Try to execute the "SELECT" in new widow, it will execute forever

Step 4:
You have seen that SELECT query which executed in second's of time initially, now executing for ever, this happens because UPDATE command under TRANSACTION is not yet committed to the database so the records are locked, hence SELECT query is not able to retrieve the data as it did initially, under such scenario what will we do if we really required data to be fetched ? no worries we have the solution in the name of "NOLOCK" and get updated record as well, let's have a look on below query

Step 5 :
Now you can see, records has been fetched though the data has been locked by Update command.

Point to remember :

• If you rollback or commit the Update command under the transaction, there is no need of NOLOCK as data is already released by update command.

• If you ROLLBACK the command, changes that has been made by Update command will be rollbacked.

Author : Feel free to ask if you have any query.