CODEWITHSUNDEEP
sqlsql-serverperformancenolock
slayernoah
slayernoah

Reputation: 4492

SQL server with (NOLOCK) hint

Could someone explain if using with (NOLOCK) in SQL queries actually cause them to run faster? And if so, why?

Upvotes: 1

Views: 630

Answers (2)

Prakash J
Prakash J

Reputation: 15

WITH (NOLOCK) (source):

Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.  

Upvotes: 0

CodeNewbie
CodeNewbie

Reputation: 2091

Using NOLOCK doesn't speed up your individual query per se. But it allows for other queries to access the same table therefore reducing time lost through deadlocks.

This is particularly useful when one client has chosen to update a table and takes a significant time to complete the transaction. Using a NOLOCK command, other clients can execute queries that are trying to access the table. They do not have to wait for the update to complete and so, other transactions can complete even while one transaction is still in progress.

This does not imply that the performance of a single query is enhanced by using the NOLOCK command. It simply improves the overall performance where multiple clients are involved or a batch execution is taking place.

However, you need to be aware of the risk of dirty reads when using NOLOCK. Here's a good article explaining why you should generally avoid using NOLOCK.

Upvotes: 5

Related Questions