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:
Example:
Select *
from aTable with (NOLOCK)
This question is related to
sql
sql-server
locking
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.
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.
It will be faster because it doesnt have to wait for locks
It will be faster because it doesnt have to wait for locks
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.
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.
It will be faster because it doesnt have to wait for locks
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.
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.
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.
Source: Stackoverflow.com