Can someone explain why use “with (nolock)” in SQL Server queries, when you should/shouldn't use it?
The NOLOCK hint allows SQL to read data from tables by ignoring any locks and therefore not being blocked by other processes.
This can improve query performance, but also introduces the possibility of dirty reads.
Read more to better understand the use of NOLOCK.
BEGIN TRANSACTION UPDATE Customer set Contact='0004894149' where ID=1
If you run the above query ,you will notice that it never completes, because the UPDATE has not yet been committed.
select * from customer where ID=1
If we run above SELECT without NOLOCK in another query window then your table will getting locked.
select * from customer with(nolock)where ID=1
If we run above SELECT with NOLOCK in another query window then your table will return all record.
1 Answers
The NOLOCK hint allows SQL to read data from tables by ignoring any locks and therefore not being blocked by other processes.
This can improve query performance, but also introduces the possibility of dirty reads.
Read more to better understand the use of NOLOCK.
If you run the above query ,you will notice that it never completes, because the UPDATE has not yet been committed.
If we run above SELECT without NOLOCK in another query window then your table will getting locked.
If we run above SELECT with NOLOCK in another query window then your table will return all record.