CODEWITHSUNDEEP
sqlsql-server-2008
Jayanti Lal
Jayanti Lal

Reputation: 1185

why nolock is important while querying on database

Woking on a huge database I was extracting huge amount of data. for example my query is 

select * from temp

it was causing errors for my application users then I realised that I was because my query was taking too long time to extract data.

then I modified my query with 

select * from temp with(nolock)

and then everything is smooth. somebody please explain why nolock is such important.

Upvotes: 2

Views: 1005

Answers (4)

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

NOLOCK overrides the rules that ensure that the data you read from your database is consistent and respects ACID rules (Atomicity, Consistency, Isolation, Durability).

If you run a query without NOLOCK then you will always get the same result back (assuming nobody else deletes or updates any of the data in the meantime).

If you run a query WITH NOLOCK twice or more, your results may vary.

NOLOCK actually means that you tell the DB to return the data it can read, no matter if it has been commited to the database or not.

So, in conclusion and in short: NOLOCK is important if you want accurate data or not. If you want just data, then you could use NOLOCK, but it's generally not a very good idea.

Upvotes: 5

Sham Sunder
Sham Sunder

Reputation: 102

You can use NOLOCK for queries used in MIS Reports, where some changes in data doesn't affect the management decisions.

But you should not use NOLOCK for operational queries and operationl reports. which could impact the operations and are risky

Upvotes: 0

gehlot
gehlot

Reputation: 35

The WITH (nolock) hint is an explicit command directed at a specific table or view used to set the transaction isolation level against the table or tables within a view for a query. Once issued, locks will not be used against the data within the table. The advantage to this is there is no chance a deadlock will occur against any other queries running against the table. The other indirect advantage is that less memory will be used in order to hold locks against that data

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

When you say WITH NOLOCK and execute your query then it is like using READ UNCOMMITED data. So you are ready to read the uncommitted data as well which can be rolled back. So you are in risk of reading the dirty data.

You can refer:

Upvotes: 1

Related Questions