Posted By: Anonymous
In plain English, what are the disadvantages and advantages of using
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
in a query for .NET applications and reporting services applications?
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:
nolockdangerous? Could you end
up reading invalid data with
read uncommittedon? 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
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
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
nolockto 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.