Re: ISOLATION levels and when to use SERIALIZEABLE

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: *** (deacdb2_at_hotmail.com)
Date: 06/25/04 

Date: Fri, 25 Jun 2004 09:44:27 -0700

Thanks for the response.

Please see my response to Bojidar where I explain that I did test the code
in two windows extensively.
The documentation and my testing shows that only SELECT statements are
affected by isolation level.
So I my transaction below which contains only UPDATE statements runs exactly
the same at any isolation level.

Please let me know if you can get that transaction to behave incorrectly.
And how.

***

"Pablo" <Pablo@discussions.microsoft.com> wrote in message
news:8D0969C3-02F5-4012-B3C0-F0991BD9BE65@microsoft.com...
> In this scenario, higher isolation than READ COMMITTED is required
>
> BEGIN TRANSACTION
>
> SELECT @Balance FROM savings WHERE SSAN = 555-55-5555;
> -- here @Balance is proccessed,adjusted by other statements
> ..
> -- more complex computing of @Balance
> SET @Balance = .....
> ..
>
> -- finally
> UPDATE savings SET Balance = @Balance WHERE SSAN = 555-55-5555;
> COMMIT -- o
>
> I suggest you testing by debuging the same stored procedure in two windows
>
> Regards,
> Pawel
>
>
>
>
> "***" wrote:
>
> > What do you think about this?
> >
> > It seems to me that isolation levels only affect reads (SELECT
statements)
> > in the current transaction because isolation levels only affect how S
locks
> > are used.
> >
> > If the transaction contains only INSERT, UPDATE or DELETE, isolation
level
> > doesn't matter since they all use X locks.
> >
> > So in the transaction below, even READ UNCOMMITTED would work the same
as
> > SERIALIZEABLE since there are no SELECT statements. I've done quite a
bit of
> > testing to try to break it and I can't.
> >
> > BEGIN TRANSACTION
> > UPDATE savings SET Balance = Balance - 1000 WHERE SSAN =
555-55-5555;
> > UPDATE checking SET Balance = Balance + 1000 WHERE SSAN =
555-55-5555;
> > COMMIT -- or ROLLBACK
> >
> > What's wrong with my thinking?
> >
> > I must confess that when it comes to critical data like money I tend to
use
> > SERIALIZEABLE anyway, perhaps to avoid lawsuits. ;-) Also in case we
later
> > add some SELECT statements that might need the protection. We should
keep
> > the transaction as short as possible and COMMIT asap anyway, so it
doesn't
> > hurt.
> >
> > thanks,
> > ***
> >
> >
> >
> >

Quantcast