With (Nolock) VS Set Transaction Isolation Level Read Uncommitted

WITH (NOLOCK) vs SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

They are the same thing. If you use the set transaction isolation level statement, it will apply to all the tables in the connection, so if you only want a nolock on one or two tables use that; otherwise use the other.

Both will give you dirty reads. If you are okay with that, then use them. If you can't have dirty reads, then consider snapshot or serializable hints instead.

What is (are) difference between NOLOCK and UNCOMMITTED

NOLOCK : Is equivalent to READ UNCOMMITTED (source : MSDN)

NOLOCK or READ UNCOMMITTED Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data. Allowing dirty reads can cause higher concurrency, but at the cost of reading data modifications that then are rolled back by other transactions

READ UNCOMMITTED and NOLOCK hints apply only to data locks. All queries, including those with READ UNCOMMITTED and NOLOCK hints, acquire Sch-S (schema stability) locks during compilation and execution. Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the table

NOLOCK vs. Transaction Isolation Level

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.

Is it possible to set no lock or TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at database level?

The short answer is "no". The default isolation level in SQL Server is READ COMMITTED, and there is no way to change this to UNCOMMITTED, either globally or per-database. And that's a very good thing too.

WITH (NOLOCK) is a recipe for trouble when it comes to getting accurate results from your database, and in bad cases it can even cause timeouts from queries that run forever due to data getting moved (which NOLOCK cannot protect against). See Is the NOLOCK (Sql Server hint) bad practice? for some more discussion, and some good tips on alternatives.

In particular, many applications that are reader-heavy and want to proceed without blocking can benefit from snapshot isolation. Unlike UNCOMMITTED, you can make snapshot isolation the default with the READ_COMMITTED_SNAPSHOT option. Be sure to read up on the pros and cons of snapshot isolation before you do this -- or better yet, ask your DBA to do this, as any DBA who recommends a global use of WITH (NOLOCK) has some reading up to do. Query hints should be used only as a last resort.

SELECT Statement - NOLOCK with SET TRANSACTION ISOLATION LEVEL READ COMMITTED

yes a table hint overrides the isolation level setting, so you will still get dirty reads

easy to test

first run this

create table bla(id int)
insert bla values(1)

begin tran
select * from bla with (updlock, holdlock)

make sure not commit the tran!!
open another window and run this

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

select * from bla -- with (nolock)

you don't get anything back.

open another window and run this now

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

select * from bla with (nolock)

as you can see you will get back the row

BTW, READ COMMITTED is the default isolation level, no need to set it

Take a look at Snapshot Isolation which won't give you back dirty data but still won't lock

Why use a READ UNCOMMITTED isolation level?

This isolation level allows dirty reads. One transaction may see uncommitted changes made by some other transaction.

To maintain the highest level of isolation, a DBMS usually acquires locks on data, which may result in a loss of concurrency and a high locking overhead. This isolation level relaxes this property.

You may want to check out the Wikipedia article on READ UNCOMMITTED for a few examples and further reading.

---

You may also be interested in checking out Jeff Atwood's blog article on how he and his team tackled a deadlock issue in the early days of Stack Overflow. According to Jeff:

But is nolock dangerous? Could you end
up reading invalid data with read uncommitted on? Yes, in theory. You'll
find no shortage of database
architecture astronauts who start
dropping ACID science on you and all
but pull the building fire alarm when
you tell them you want to try nolock.
It's true: the theory is scary. But
here's what I think: "In theory there
is no difference between theory and
practice. In practice there is."

I would never recommend using nolock
as a general "good for what ails you"
snake oil fix for any database
deadlocking problems you may have. You
should try to diagnose the source of
the problem first.

But in practice adding nolock to queries that you absolutely know are simple, straightforward read-only affairs never seems to lead to problems... As long as you know what you're doing.

One alternative to the READ UNCOMMITTED level that you may want to consider is the READ COMMITTED SNAPSHOT. Quoting Jeff again:

Snapshots rely on an entirely new data change tracking method ... more than just a slight logical change, it requires the server to handle the data physically differently. Once this new data change tracking method is enabled, it creates a copy, or snapshot of every data change. By reading these snapshots rather than live data at times of contention, Shared Locks are no longer needed on reads, and overall database performance may increase.

---

Related Topics