Share via

Benefits and disadvantages of WITH (NOLOCK)

Jonathan Brotto 420 Reputation points
2024-05-30T15:12:46.18+00:00

Benefits and disadvantages of WITH (NOLOCK). Also is there other commands that can serve different use cases.

Developer technologies | Transact-SQL
Developer technologies | Transact-SQL

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.

SQL Server | Other

Locked Question. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Bruce (SqlWork.com) 83,581 Reputation points Volunteer Moderator
    2024-05-30T15:29:52.0366667+00:00

    The benefit is that no locks are taken and the query will not block another query. The main downside is the results may contain uncommited values (updated, added or deleted) that end up not being committed or see partial updates.

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 133.1K Reputation points MVP Volunteer Moderator
    2024-05-30T21:20:01.3066667+00:00

    It's more than just see uncommitted values that can be inconsistent. You may also fail to read committed data, because it was being moved or in a page split when you scanned the table. Or you may read the same data twice due to the same reason.

    When you use NOLOCK and SQL Server scans a table, it may use an IAM scan, which is more efficient, but which is not safe for things like I mentioned above.

    NOLOCK can be very useful for ad-hoc queries in a production system to avoid blocking, and particularly to investigate the progress of long-running transactions.

    But it very rarely has a place in application code, since the risk for transient incorrect results is too high. If blocking is a concern, using some form of snapshot is better.

    4 people found this answer helpful.
    0 comments
  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more