Re: Isolation levels

Razvan Socol <rsocol_at_gmail.com> wrote in news:1179242156.504604.114710_at_e51g2000hsg.googlegroups.com:

> Please read the following posts by Craig Freedman, member of the SQL
> Server query execution team:
> http://blogs.msdn.com/craigfr/archive/2007/04/25/read-committed-isolati
> on-level.aspx

The anomalies with MS SQL Server Read Committed, especially w.r.t aggregates, have been known for quite a while and are well documented.

> http://blogs.msdn.com/craigfr/archive/2007/05/02/query-plans-and-read-c
> ommitted-isolation-level.aspx
> http://blogs.msdn.com/craigfr/archive/2007/05/09/repeatable-read-isolat
> ion-level.aspx
>
> Is the behaviour described in these posts the *correct* behaviour, as
> defined by ANSI SQL standards, considering the Read Committed and
> Repetable Read isolation levels, respectively ?

Alas, the standard is very vague on isolation levels so much that it lets some databases like Oracle that do not have Serializable qualify as if they do ! See http://www.cs.duke.edu/~junyang/courses/cps216-2003- spring/papers/berenson-etal-1995.pdf on why the ANSI isolation level definition is inadequate.

> Do other DBMS-s behave
> the same way ? I am aware that the behaviour would be different if we
> use snapshot isolation, but I'm interested how things *should* work
> without snapshot isolation.

The Oracle Read Committed is 'better' than Microsofts in the sense that it does not permit some anomalies that SQL Server does. To be fair, SQL Server has a true Serializable that Oracle lacks. But see the paper above.

>
> I'd like some responses from people who know really well the ANSI SQL
> standard and the isolation levels defined in it.
>
> Razvan
>
>
Received on Wed May 16 2007 - 14:41:19 CEST