June 19, 2013 at 10:10 am
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
June 19, 2013 at 10:33 am
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'
June 19, 2013 at 10:59 am
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/.
June 19, 2013 at 11:24 am
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
June 21, 2013 at 8:23 am
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.
June 21, 2013 at 9:58 am
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.
June 21, 2013 at 9:59 am
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).
June 21, 2013 at 1:53 pm
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
June 21, 2013 at 2:59 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply