locked
pros and cons for WITH (NOLOCK). RRS feed

  • Question

  • I have sql 2012 db and a single database for 100 branches size 350GB. for big query some times sql server lock pages. that time i could not able to read some particular rows those are not use in transaction session. i use with (nolock) for reading data for those rows. what would be the best practice for that.
    Saturday, October 15, 2016 8:28 AM

Answers

  • NOLOCK (or the READ_UNCOMMITTED isolation level) is fine if you don't care if data is inconsistent or rows are missing/duplicated in the result.  Avoid NOLOCK if you need correct results when concurrent activity may update the tables involved and you want correct results. NOLOCK is generally a worst practice instead of best practice.

    Blocking problems can often be avoided with query and index tuning so that only the data needed by the query is touched.  If blocking continues to be a problem, consider turning on the READ_COMMITTED_SNAPSHOT database option so that row versioning instead of locking is used for read consistency. This will increase tempdb usage and row size but the concurrency benefits may outweigh the costs. 


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, October 15, 2016 10:17 AM

All replies

  • NOLOCK (or the READ_UNCOMMITTED isolation level) is fine if you don't care if data is inconsistent or rows are missing/duplicated in the result.  Avoid NOLOCK if you need correct results when concurrent activity may update the tables involved and you want correct results. NOLOCK is generally a worst practice instead of best practice.

    Blocking problems can often be avoided with query and index tuning so that only the data needed by the query is touched.  If blocking continues to be a problem, consider turning on the READ_COMMITTED_SNAPSHOT database option so that row versioning instead of locking is used for read consistency. This will increase tempdb usage and row size but the concurrency benefits may outweigh the costs. 


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, October 15, 2016 10:17 AM
  • Thanks Dan. you got the point. thank you.
    Saturday, October 15, 2016 10:23 AM
  • You can mark as answer of Dan reply if it does solve this issue. It will benefit others if they are in the same situation. Thanks.
    Tuesday, October 18, 2016 1:52 AM