Why use a READ UNCOMMITTED isolation level?

Question

In plan English what are the disadvanges and advantages of using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in query for .NET applications and reporting services application?

Answer 1

The advantage is that it can be faster in some situations. The disadvantage is the result can be wrong (data which hasn't been committed yet could be returned) and there is no guarantee that the result is repeatable.

If you care about accuracy, don't use this.

More information is on MSDN:

Implements dirty read, or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels.

Answer 2

In all seriousness: Don't. Unless you really know what you are doing (and even then, there is probably a better solution)

Answer 3

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 for a few examples and further reading:

• Isolation (database systems): READ UNCOMMITTED (dirty reads)

---

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:

• Coding Horror: Deadlocked!

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.

Answer 4

This can be useful to see the progress of long insert queries, make any rough estimates (like COUNT(*) or rough SUM(*)) etc.

In other words, the results the dirty read queries return are fine as long as you treat them as estimates and don't make any critical decisions based upon them.

Answer 5

You asked for this in "plan English" I have no idea what that is but I'll state it in plain English. :)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

When this is passed as part of you SQL query, SQL Server's default setting is overided.

The tables used in the query are not locked while the query is processed and returned to your application.

That means if someone or something can edit a table in your database (in the middle of your query being run) you won't get that update in your query since the table wasn't locked.

Honestly, in most cases this is not a problem. It is always better to let SQL do it's normal thing and lock the table, but I really doubt it will have a majority adverse effect in your case.