Re: Isolation levels

"Razvan Socol" <rsocol_at_gmail.com> wrote in message 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-isolation-level.aspx
> http://blogs.msdn.com/craigfr/archive/2007/05/02/query-plans-and-read-committed-isolation-level.aspx
> http://blogs.msdn.com/craigfr/archive/2007/05/09/repeatable-read-isolation-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 ? 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.
>
Yes. These behaviors are correct.

The 1999 standard, ISO/IEC 9075-2:1999 (E), defines the phenomena "Dirty Read," "Non-repeatable read," and "Phantom" in terms of reading rows, not in terms of statement execution. This means that for READ COMMITTED, a row that is not currently being read by a statement that is executing in transaction T1 can be read and updated by transaction T2 even after that statement starts. The implications for long-running statements is that it's possible for a row that has already been read to be updated and committed by another transaction so that it ends up being read again later on during the long-running statement.

Sybase SQL Anywhere operates similar to Microsoft Sql Server.

IBM DB2 uses RR for SERIALIZABLE, RS for REPEATABLE READ, CS for READ COMMITTED and UR for READ UNCOMMITTED, but the behavior is similar to Microsoft Sql Server.

Oracle uses MVCC (similar to snapshot isolation). REPEATABLE READ is the same as SERIALIZABLE.

PostgresSQL also uses MVCC. REPEATABLE READ is the same as SERIALIZABLE.

Gupta SQLBase also uses MVCC but in a strange way. RR is like SERIALIZABLE, CS is like READ COMMITTED but an entire page is locked until all rows from that page are fetched, RL operates similar to the way Sql Server implements READ COMMITTED (locks are placed but immediately released), and RO is implemented with MVCC.

> 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:57:49 CEST