Effect of NOLOCK hint in SELECT statements
SqlSql ServerLockingSql Problem Overview
I guess the real question is:
If I don't care about dirty reads, will adding the with (NOLOCK) hint to a SELECT statement affect the performance of:
- the current SELECT statement
- other transactions against the given table
Example:
Select *
from aTable with (NOLOCK)
Sql Solutions
Solution 1 - Sql
-
Yes, a select with
NOLOCKwill complete faster than a normal select. -
Yes, a select with
NOLOCKwill allow other queries against the effected table to complete faster than a normal select.
Why would this be?
NOLOCK typically (depending on your DB engine) means give me your data, and I don't care what state it is in, and don't bother holding it still while you read from it. It is all at once faster, less resource-intensive, and very very dangerous.
You should be warned to never do an update from or perform anything system critical, or where absolute correctness is required using data that originated from a NOLOCK read. It is absolutely possible that this data contains rows that were deleted during the query's run or that have been deleted in other sessions that have yet to be finalized. It is possible that this data includes rows that have been partially updated. It is possible that this data contains records that violate foreign key constraints. It is possible that this data excludes rows that have been added to the table but have yet to be committed.
You really have no way to know what the state of the data is.
If you're trying to get things like a Row Count or other summary data where some margin of error is acceptable, then NOLOCK is a good way to boost performance for these queries and avoid having them negatively impact database performance.
Always use the NOLOCK hint with great caution and treat any data it returns suspiciously.
Solution 2 - Sql
NOLOCK makes most SELECT statements faster, because of the lack of shared locks. Also, the lack of issuance of the locks means that writers will not be impeded by your SELECT.
NOLOCK is functionally equivalent to an isolation level of READ UNCOMMITTED. The main difference is that you can use NOLOCK on some tables but not others, if you choose. If you plan to use NOLOCK on all tables in a complex query, then using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is easier, because you don't have to apply the hint to every table.
Here is information about all of the isolation levels at your disposal, as well as table hints.
Solution 3 - Sql
In addition to what is said above, you should be very aware that nolock actually imposes the risk of you not getting rows that has been committed before your select.
Solution 4 - Sql
It will be faster because it doesnt have to wait for locks
Solution 5 - Sql
-
The answer is Yes if the query is run multiple times at once, because each transaction won't need to wait for the others to complete. However, If the query is run once on its own then the answer is No.
-
Yes. There's a significant probability that careful use of WITH(NOLOCK) will speed up your database overall. It means that other transactions won't have to wait for this SELECT statement to finish, but on the other hand, other transactions will slow down as they're now sharing their processing time with a new transaction.
Be careful to only use WITH (NOLOCK) in SELECT statements on tables that have a clustered index.
WITH(NOLOCK) is often exploited as a magic way to speed up database read transactions.
The result set can contain rows that have not yet been committed, that are often later rolled back.
If WITH(NOLOCK) is applied to a table that has a non-clustered index then row-indexes can be changed by other transactions as the row data is being streamed into the result-table. This means that the result-set can be missing rows or display the same row multiple times.
READ COMMITTED adds an additional issue where data is corrupted within a single column where multiple users change the same cell simultaneously.