Using WITH (NOLOCK)

The WITH (nolock) hint is an explicit command directed at a specific table or view used to set the transaction isolation level against the table or tables within a view for a query. Once issued, locks will not be used against the data within the table. The advantage to this is there is no chance a deadlock will occur against any other queries running against the table. The other indirect advantage is that less memory will be used in order to hold locks against that data.

Example:

SELECT
    first_name,
    last_name,
FROM dbo.person p WITH (NOLOCK)
JOIN dbo.employee e WITH (NOLOCK)
    ON e.person_id = p.person_id
WHERE p.person_id = 1;

The nolock setting above is explicit for the table it is being set against. To set the value globally for the scope of the connection, see SET TRANSACTION ISOLATION LEVEL

While the “WITH” keyword may not be necessary in current versions of SQL Server, its use is strongly recommended for future version compatibility

Advantages:

  • Deadlocks will not occur against other queries running against the same data
  • Less memory is utilized due to the lack of row, page, or range level locking
  • Typically allows for much higher concurrency due to lower footprint

Disadvantages:

  • Uncommitted data can be read leading to dirty reads
  • Explicit hints against a table are generally bad practice

Usage

In most places I have worked, with (nolock) has been a generally accepted practice in the specific areas of the system that are not sensitive to data being slightly out of sync. It is important to know where things could go wrong though. The biggest red flag I can think of for not using NOLOCK would be a system that uses explicit transactions (BEGIN TRAN ..END TRAN) or heavy use of triggers. Multiple statements executed within a transaction experience a time delay of their INSERT / UPDATE / DELETE operations however the changes are committed at once upon the COMMIT. Statements that query the changed data using the READ COMMITTED isolation level will be blocked from seeing these changes until commit, whereas READ UNCOMMITTED (NOLOCK) will see the changes immediately irregardless of when the commit occurs. The assumption here is that if your system uses explicit transactions or relies on triggers heavily, it may be plausible to assume nolock is not a good idea.

Do not use WITH (NOLOCK) without fully understanding the ramifications of a dirty read

Example of a Dirty Read

The following example will open a transaction in order to update the first_name column in our global temp table: ##my_name

IF OBJECT_ID('tempdb..##my_name') IS NOT NULL
BEGIN
    DROP TABLE ##my_name;
END;

CREATE TABLE ##my_name
(
    id INT,
    first_name VARCHAR(20)
);

INSERT INTO ##my_name (id, first_name)
VALUES (1, 'dexter');

BEGIN TRAN

UPDATE ##my_name
SET first_name = 'derek'
WHERE id = 1;

Here we have left a transaction open on ##my_name so that row is exclusively locked and cannot be read by any transaction using isolation level read committed or higher.

Open a new connection and execute the following queries:

SELECT * FROM ##my_name WITH (NOLOCK);
SELECT * FROM ##my_name;

Here you will see the first query will show the updated value ‘derek’, whereas the query without the nolock will hang waiting for the transaction to release. The data that has been successfully read is considered dirty data. This is because there may be other tables that need to be updated which relate to the ‘derek’ record (id=1) in order to show a consistent view of all data related to ‘derek’.

Finally let’s commit our transaction within our original window and you’ll see that you are now able to query the data without using (nolock).

COMMIT TRAN
SELECT * FROM ##my_name;

9 comments
Rahul 15 Feb 2017 at 1:01 pm

Nice Explanation.

hari 24 Oct 2016 at 1:59 pm
Gary Alvarez 22 Sep 2016 at 4:16 pm

thank’s so much!

Simon 01 Sep 2016 at 12:24 pm

Good exemple.

We use with (NoLock) in our compagny to read data in several queries because our database has several Go of data. Do you think it is the best way to increase the speed of the SELECT queries?

Rajesh 01 Jul 2016 at 12:13 pm

i did not understand,Please explain more in Details

Gary Alvarez 22 Sep 2016 at 4:19 pm

It is used to avoid locks , after the –> from tablename WITH(NOLOCK).

Regards,

ByeongJoo 29 Aug 2015 at 6:54 am

Hi,thank you for this great example. However when i tseetd the repeatable example where you suggest that if we change the repeatable read to the default of Read committed ,that both selects return different results,but both my selects return exactly the same. Please advise.Thanks again

SQLAddict 07 Nov 2014 at 1:28 pm

Good Article for NOLOCK

ranjan singh 01 Oct 2014 at 12:18 pm

Hi,
i did not understand,Please explain more in Details

Featured Articles

 Site Author

  • Thanks for visiting!