Use of (NOLOCK) in SQL

Question

2.67/5 (3 votes)

See more:

SQL-Server-2005

, +

Dear Friends,

What is the use of (Nolock) in SQL.
Is there any other feature like this to use in sql.
Kindly give answer on this and if my question is wrong then educate me on this.

Regards,
AP

Posted 23-May-12 18:07pm

Arunprasath Natarajan

Add a Solution

4 solutions

Solution 1

NOLOCK means that when you query the database, no locks will be honored. While this may speed your query since it's not waiting for locks to be released, you may see 'false' (uncommitted) data.

Have a look at this What should be considered when NOLOCK hint is used[^]

Posted 23-May-12 18:13pm

Wendelius

Solution 2

WITH(NOLOCK) hint takes Schema Lock on the object which is accessed,
Is also known as READUNCOMMITTED, and applicable to SELECT statements only.

Syntax:

SQL

SELECT * FROM tableName a WITH (NOLOCK)

Posted 23-May-12 18:16pm

sachin10d

Solution 4

WITH (NOLOCK) example[^]
May this help you.

Posted 15-May-13 8:19am

Amey K Bhatkar

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Prasad_Kulkarni · Accepted Answer · 2012-05-23T18:21:00

Solution 3

NOLOCK
NOLOCK does not lock any object.
This is the default for SELECT operations.
It does not apply to INSERT, UPDATE, and DELETE statements.

Examples:

SQL

SELECT OrderID
FROM Orders WITH (ROWLOCK)
WHERE OrderID BETWEEN 100
AND 2000

UPDATE Products WITH (NOLOCK)
SET ProductCat = 'Machine'
WHERE ProductSubCat = 'Mac'

Ref: SQLAuthority.com

WITH (NOLOCK) is the equivalent of using READ UNCOMMITED as a transaction isolation level. So, you stand the risk of reading an uncommitted row that is subsequently rolled back, i.e. data never made it into the database. So, while it can prevent reads being deadlocked by other operations, it comes with a risk.

Please refer: Using NOLOCK[^]

Using MS-SQL's NOLOCK for faster queries[^]

Note: Don't use NOLOCK to do any writing of data, or even for quick reads.
You can get more details on links provided above.

Hope it helps!

Posted 23-May-12 18:21pm

Prasad_Kulkarni

Comments

Arunprasath Natarajan 24-May-12 0:47am

Dear Prasad, You said it won't work in Insert, Update n Delete statement.
But in your eg. you have mentioned in update query. Will it work

Gopi M.G 31-Dec-13 4:19am

Dear prasad,
can you explain me what is xlock because i am some what confused about it.

Prasad_Kulkarni 24-May-12 1:08am

You got me wrong Arunprasath.
Let me explain; I din't said it doesn't work I said it does not apply when you insert, update or delete query in sql. It comes default when you're using SELECT.

Arunprasath Natarajan 24-May-12 1:11am

Yeah now I understand. But is there any difference between (ROWLOCK) and (NOLOCK).
Because when i use it, both gives me the data quick but i couldn't find and difference.

Prasad_Kulkarni 24-May-12 1:20am

ROWLOCK simply adds lock on row level, here are all types of LOCKS in SQL with some links, as far I know both works for improving performance.

ROWLOCK
Use row-level locks when reading or modifying data.

PAGLOCK
Use page-level locks when reading or modifying data.

TABLOCK
Use a table lock when reading or modifying data.

DBLOCK
Use a database lock when reading or modifying data.

UPDLOCK
UPDLOCK reads data without blocking other readers, and update it later with the assurance that the data has not changed since last read.

XLOCK
Use exclusive locks instead of shared locks while reading a table, and use hold locks until the end of the statement or transaction.

HOLDLOCK
Use a hold lock to hold a lock until completion of the transaction, instead of releasing the lock as soon as the required table, row, or data page is no longer required.

Please refer: SQL Server NOLOCK / ROWLOCK Directives To Improve Performance[^]
How does ROWLOCK work?[^]
SQL Server Locks[^]

Arunprasath Natarajan 24-May-12 1:48am

Thank You very much...

Prasad_Kulkarni 24-May-12 2:35am

You're welcome!