What is the benefit of using WITH (NOLOCK) in T-SQL?
NOLOCK
is a T-SQL hint (directive) that allows MS SQL Server to ignore the normal locks that are placed and held for a transaction and allows the query to complete without having to wait for the first transaction to finish and therefore release the locks. Using NOLOCK
gives a significant improvement on large tables, where insert / update commands can take 3-15 seconds. However, you need to be very careful with using NOLOCK
. Remember you can get some records that were partially updated or inserted. It is safe to use NOLOCK on rows that you know are not changing right now. For example, records that belong to some user and he is running reports, but not updates, however, some users can do updates or inserts at the same time.
Example:
INSERT INTO Orders (UserName,Total) VALUES ('John', 12.50)
SELECT * FROM Orders WITH(NOLOCK) where UserName = 'Viktar'
Conclusion:
If you have blocking issues try to eliminate them, but this is not always so simple. If you can not get rid of the severe blocking issues, see if using NOLOCK can help.