NOLOCK vs. Transaction Isolation Level

19
votes

What's the difference between using "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" and NOLOCK? Is one better than the other?

4
sqlsql-servertransaction-isolation
Note that the NOLOCK hint is deprecated in MSSQL 2012, to be replaced with the equivalent READUNCOMMITTED hint. Source. – Jeroen
The suggested source says - Support for use of the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement will be removed in a future version of SQL Server. . It doesn't say that NOLOCK is deprecated. Currently both NOLOCK and READUNCOMMITTED hints are available I believe. – RBT

4 Answers

22
votes

They're the same thing, just scoped differently. NOLOCK is placed on a per table basis and SET Transaction... can be placed as a block.

6
votes

NOLOCK is a query hint and as such only applies to the specifc table within the query in which it is specified.

Setting the transaction isolation level applies to all code executed hence forth within the current connection or until it is explicitly modified.

To clarify, functionally the isoloation level at work is the same however the scope which is covered may not be.

4
votes

See that answer from a few hours ago, to the question SQL Server SELECT statements causing blocking.

Quoting Remus Rusanu:

SELECT can block updates. A properly designed data model and query will only cause minimal blocking and not be an issue. The 'usual' WITH NOLOCK hint is almost always the wrong answer. The proper answer is to tune your query so it does not scan huge tables.

If the query is untunable then you should first consider SNAPSHOT ISOLATION level, second you should consider using DATABASE SNAPSHOTS and last option should be DIRTY READS (and is better to change the isolation level rather than using the NOLOCK HINT). Note that dirty reads, as the name clearly states, will return inconsistent data (eg. your total sheet may be unbalanced).

The other answers may help you as well.

1
votes

They have the same effect, only one is used as a lock hint (nolock) and the other is used for a connection scope.

Be careful with either of those - dirty reads can be a very bad thing depending on your app. Reading the same record twice or missing a record because of page movement can be a very confusing thing to users...