Posted by Erland Sommarskog on 04/23/07 21:42

Igor (jerosimic@gmail.com) writes:
> someone wrote: "I've implemented SET TRANSACTION ISOLATION LEVEL READ
> UNCOMMITTED at the beginning
> of a number of stored procedures and, then SET TRANSACTION ISOLATION
> LEVEL READ
> COMMITTED at the end to minimize the disruption to the application.".
> My question is, do you really need to set READ COMMITTED at the end of
> stored procedure? What scope does that command affect?

No, you don't need it. A SET command only affects the current scope, and
any scope it invokes. When the scope terminates, the SET option reverts
to its previous setting. (With one exception, SET CONTEXT_INFO.)

> 2. Could someone write some real world example where i should never
> read uncommitted data... i'm having trouble understanding when i
> should and when i should not use it.

The basic rule is: if you don't understand the consequences of it, don't
use it.

Then there are cases, where it is even more wrong than others. Say that
you write a query which returns the balance on a number of accounts, and
the result of this query is to be used to reconciliate the books. If
you read with NOLOCK, you may come in the middle of a transaction that
moves money from one account to other. You could then display the same
amount in two accounts - or it is missing from both accounts.

NOLOCK is more OK if you need to read large amounts of historic data,
and there is no requirement for complete accuracy. The same table also
has active insertion of current data. In this case, locks could be
table locks which would prevent insertions from taking place. NOLOCK
can prevent this.

Never use NOLOCK or READ UNCOMMITTED as a matter of routine!

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]