Menu
What's the difference between using "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" and NOLOCK? Is one better than the other?
528
In a nutshell, nolock (read uncommitted) takes no shared locks to prevent other transactions from modifying data read by this transaction. It also effectively ignores exclusive locks taken by other transactions when they have added or changed data but not committed it yet.
The NOLOCK hint allows SQL to read data from tables by ignoring any locks and therefore not get blocked by other processes. This can improve query performance by removing the blocks, but introduces the possibility of dirty reads.
Serializable. This is the highest isolation level and prevents all possible types of concurrency phenomena in SQL Server, but on the other hand, the serializable level decreases performance and increases the likelihood of deadlocks.
One of the more heavily used table hints in the SELECT T-SQL statements is the WITH (NOLOCK) hint. The default transaction isolation level in SQL Server is the READ COMMITTED isolation level, in which retrieving the changing data will be blocked until these changes are committed.
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.
182
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.
26
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.
31
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...
25
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
