Share via

Benefits and disadvantages of WITH (NOLOCK)

Jonathan Brotto 180 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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,202 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,591 questions
0 comments
Accepted answer
  1. Bruce (SqlWork.com) 60,361 Reputation points
    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

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 105.8K Reputation points MVP
    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.

    1 person found this answer helpful.
    0 comments