Re: Isolation levels


"Razvan Socol" <rsocol@xxxxxxxxx> wrote in message
news:1179242156.504604.114710@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • RE: SQL
    ... Subject: SQL ... procedures for SQL Server) send you mails with the results of the ... queries or with execution results! ... This list is provided by the SecurityFocus Security Intelligence Alert ...
    (Pen-Test)
  • 2000 vs 2005 performance issue
    ... I have a performance issue occurred during migration from 2000 to 2005 ... SQL Server. ... The execution ...
    (microsoft.public.sqlserver)
  • Re: DBParameter vs. direkter Angabe im SQL-Statement
    ... SQL Server 2005 hat "auto-parameterized queries". ... Dann ersetzt der SQL Server alle Konstanten durch Variable ... ... erstellt seinen Execution Plan und kann dann bei den Queries ... ...
    (microsoft.public.de.german.entwickler.dotnet.datenbank)
  • RE: Fulltext failure on a 2 node cluster
    ... Server full-text search resource online: "SQL Cluster Resource 'Full Text' ...
    (microsoft.public.sqlserver.clustering)
  • Re: HELP PLEASE ~ ???
    ... You mentioned that it went ahead and added a SQL ... SQL Server 2000 database for all my data. ... find the connectionString in the newly recreated SQLExpress database. ... The connection string specifies a local Sql Server Express instance ...
    (microsoft.public.dotnet.framework.aspnet)