Using WITH (NOLOCK)
-
Posted on November 30, 2012 by Derek Dieter
-
21
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:
[cc lang=”sql”]
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;
[/cc]
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
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.
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
[cc lang=”sql”]
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;
[/cc]
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:
[cc lang=”sql”]
SELECT * FROM ##my_name WITH (NOLOCK);
SELECT * FROM ##my_name;
[/cc]
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).
[cc lang=”sql”]
COMMIT TRAN
SELECT * FROM ##my_name;
[/cc]
- Comments (RSS)
- Trackback
- Permalink
Great explanation.
Nice Explanation.
sadsad
thank’s so much!
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?
i did not understand,Please explain more in Details
It is used to avoid locks , after the –> from tablename WITH(NOLOCK).
Regards,
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
Good Article for NOLOCK
Hi,
i did not understand,Please explain more in Details
