Effect of NOLOCK hint in SELECT statements

Question

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

User · Answer

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)

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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)

User · Answer

It will be faster because it doesnt have to wait for locks

User · Answer

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

User · Answer

It will be faster because it doesnt have to wait for locks

User · Answer

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)

User · Answer

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

User · Answer

It will be faster because it doesnt have to wait for locks

User · Answer

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)

User · Answer

It will be faster because it doesnt have to wait for locks

[sql] Effect of NOLOCK hint in SELECT statements

Examples related to sql

Examples related to sql-server

Examples related to locking