How will work with(nolock) option in select queries

Question

How will work with(nolock) option in select queries

BSLChennai

SSCrazy

Points: 2981
More actions
June 19, 2013 at 10:10 am

#274254

Hi,
We have few transaction tables having more than 20 million rows and using those table when we select tables are blocking and we are getting deadlocks, those tables are well indexed and well maintained. can we use WITH(NOLOCK) option in select queries for these tables. how it will work.
please do me needful.
Regards,
BSL

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

david.alcock SSCertifiable Points: 6021 More actions · Answer 1

Quite often you will see things like NOLOCK to improve performance however the massive disadvantage of using it is that queries could well see inconsistent (lets say incorrect) data. Look up isolation levels: dirty reads, non-repeatable reads etc etc

I've often seen deadlocks caused by catch 22 situations in poorly designed code, user X wants to read A and has B locked, user Z wants to read B and has A locked. I'd be more inclined to track the deadlock code down and optimise (look at the indexing too) and take from there rather than use NOLOCK or READ UNCOMMITED.

A bit of avoiding blocking from the text book:

Avoiding Blocking

Be aware that design patterns can lead to blocking. Techniques that can help avoid blocking

include:

¦¦ Keeping the transaction scope as short as possible and in the same batch.

¦¦ Not allowing user interaction during transactions. Don’t display data to a user and wait

for the user to perform an action before completing your transaction; the user might

have just left for lunch!

¦¦ Practicing proper indexing to help limit locks acquired and reduce the chance of

blocking.

¦¦ Elevating the transaction isolation level above the default only for a good reason.

¦¦ Examining the T-SQL code to see whether developers have added locking hints, index

hints, or join hints.

'Only he who wanders finds new paths'

Jason Strate Old Hand Points: 363 More actions · Answer 2

Don't switch to using NOLOCK. Instead follow the items that David listed. Also, you need to investigate the deadlocks to determine why they are deadlocking. In the past, with a well-indexed database and using default, read committed isolation level, the client I was at was getting hammered with deadlocks. Looking at the deadlock graphs showed that the application was switching all inserts to serialized operations.

One option you can consider is leveraging snapshot isolation level. That will help with deadlocks, though your application needs to be able to handle when write version conflicts occur.

As far as what harm NOLOCK can cause - checked out the video in this post - http://www.jasonstrate.com/2013/06/the-side-effect-of-nolock-video/.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

bslchennai (6/19/2013)
can we use WITH(NOLOCK) option in select queries for these tables. how it will work.

Will the users be happy if the data that the queries return is inconsistent and incorrect from time to time? Will they be happy if the queries duplicate data that's in the table (return it more than once)? Will they be happy if the queries miss chunks of data?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DiverKas SSCrazy Points: 2049 More actions · Answer 4

GilaMonster (6/19/2013)
bslchennai (6/19/2013)
can we use WITH(NOLOCK) option in select queries for these tables. how it will work.
Will the users be happy if the data that the queries return is inconsistent and incorrect from time to time? Will they be happy if the queries duplicate data that's in the table (return it more than once)? Will they be happy if the queries miss chunks of data?

I understand the issues involved, but I think your doing a disservice to people if you dont explain WHEN those can happen. In a data warehouse environment where the sheer number of records exist and its non transactional, NOLOCK is probably the best solution, because of the way SQL Server escalates locks. In a high transaction environment, it would yield all the issues you describe.

Knowing when its safe to use a particular solution or even more importantly when NOT to use a solution is really what people need to understand. Blanket statements that something is wrong when there are cases when it isnt, isnt really helping.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

DiverKas (6/21/2013)
GilaMonster (6/19/2013)
bslchennai (6/19/2013)
can we use WITH(NOLOCK) option in select queries for these tables. how it will work.
Will the users be happy if the data that the queries return is inconsistent and incorrect from time to time? Will they be happy if the queries duplicate data that's in the table (return it more than once)? Will they be happy if the queries miss chunks of data?
I understand the issues involved, but I think your doing a disservice to people if you dont explain WHEN those can happen. In a data warehouse environment where the sheer number of records exist and its non transactional, NOLOCK is probably the best solution, because of the way SQL Server escalates locks. In a high transaction environment, it would yield all the issues you describe.
Knowing when its safe to use a particular solution or even more importantly when NOT to use a solution is really what people need to understand. Blanket statements that something is wrong when there are cases when it isnt, isnt really helping.

There's no need to have a high transactional environment to obtain inconsistent (incorrect) results.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

DiverKas (6/21/2013)
GilaMonster (6/19/2013)
bslchennai (6/19/2013)
can we use WITH(NOLOCK) option in select queries for these tables. how it will work.
Will the users be happy if the data that the queries return is inconsistent and incorrect from time to time? Will they be happy if the queries duplicate data that's in the table (return it more than once)? Will they be happy if the queries miss chunks of data?
I understand the issues involved, but I think your doing a disservice to people if you dont explain WHEN those can happen. In a data warehouse environment where the sheer number of records exist and its non transactional, NOLOCK is probably the best solution, because of the way SQL Server escalates locks. In a high transaction environment, it would yield all the issues you describe.
Knowing when its safe to use a particular solution or even more importantly when NOT to use a solution is really what people need to understand. Blanket statements that something is wrong when there are cases when it isnt, isnt really helping.

Nothing in the original post indicates that this is a data warehouse. Also, there is nothing in the original post to tell us what type of data is being returned or its use.

NOLOCK is not a magic make this query faster solution. Blocking is a natural result of reading data from a system which is also being updated and if the transactions are designed correctly, this blocking should not be excessive. If it is, you need to look at the transactions.

Deadlocks are result of processes locking the same needed resources in a different order. This is a problem that needs to be resolved as it usually happens to processes that are both attempting to update the system not usually between readers and writers (at least I have had a deadlock between a processing reading data and one writing data).

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

DiverKas (6/21/2013)
Knowing when its safe to use a particular solution or even more importantly when NOT to use a solution is really what people need to understand. Blanket statements that something is wrong when there are cases when it isnt, isnt really helping.

Any time there's concurrent data modifications you have a chance for bad data. I've seen it in a report-focused environment.

I don't have time to write paragraphs on the subject, since you know more about it than I've written here, please feel free to explain in as much detail as necessary when it's safe to use NOLOCK and why there's no better solution (eg read committed snapshot, read-only filegroups, read only databases) for those situations.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 101101 More actions · Answer 8

You could implement some type of SNAPSHOT isolation level. That will prevent the types of errors you get with (NOLOCK) and prevent readers from being blocked by writers.

See Books Online, under "ALTER DATABASE", for additional info.

Naturally also follow up here with any additional qs you have.

Edit: Changed "You can" to "You could".

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".