Effect of NOLOCK hint in SELECT statements
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)
1) Yes, a select with NOLOCK will complete faster than a normal select.
2) Yes, a select with NOLOCK will 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.
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.
SET TRANSACTION ISOLATION LEVEL
Table Hint (Transact-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.
See http://blogs.msdn.com/sqlcat/archive/2007/02/01/previously-committed-rows-might-be-missed-if-nolock-hint-is-used.aspx
It will be faster because it doesnt have to wait for locks