How will work with(nolock) option in select queries

  • BSLChennai

    SSCrazy

    Points: 2981

    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

  • david.alcock

    SSCertifiable

    Points: 6021

    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: 353

    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: 1004484

    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

    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: 183637

    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: 442359

    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: 1004484

    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: 98552

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

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

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