RE: ANSI Isolation Levels

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Thu, 05 Sep 2002 16:08:19 -0800
Message-ID: <F001.004C952C.20020905160819@fatcity.com>

The purpose of the command "set transaction read only" is to implement the repeatable read isolation level. I just checked the 9i documentation ...

Oracle provides these transaction isolation levels:

Read committed
This is the default transaction isolation level. Each query executed by a transaction sees only data that was committed before the query (not the transaction) began. An Oracle query never reads dirty (uncommitted) data.

Because Oracle does not prevent other transactions from modifying the data read by a query, that data can be changed by other transactions between two executions of the query. Thus, a transaction that executes a given query twice can experience both nonrepeatable read and phantoms.

Serializable
Serializable transactions see only those changes that were committed at the time the transaction began, plus those changes made by the transaction itself through INSERT, UPDATE, and DELETE statements. Serializable transactions do not experience nonrepeatable reads or phantoms.

Read-only
Read-only transactions see only those changes that were committed at the time the transaction began and do not allow INSERT, UPDATE, and DELETE statements.

Set the Isolation Level
Application designers, application developers, and database administrators can choose appropriate isolation levels for different transactions, depending on the application and workload. You can set the isolation level of a transaction by using one of these statements at the beginning of a transaction:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET TRANSACTION ISOLATION LEVEL READ ONLY;

I thought MySQL, at least the earlier versions, had no concept of a transaction and queries read uncommitted data from other sessions. Does one have a MySQL administrator?

Is it true for Sybase, SQLServer, DB2 that writers never block readers and vice versa? For the purists this does not include latching of buffers. Do all these products have their own versions of undo segments? If I block a query from even accessing an object which has gained, changed, or lost data until that data is committed, have I implemented the read committed isolation level.

Ian MacGregor
Stnford Linear Accelerator Center
ian_at_slac.stanford.edu

-----Original Message-----
Sent: Thursday, September 05, 2002 12:12 PM To: Multiple recipients of list ORACLE-L

Intro:
There are 4 defined ANSI isolation levels: 1) read uncommitted; 2) read committed; 3) repeatable read; 4) serializable. By default Oracle implements the read committed (2) isolation level. Oracle can implement the serializable isolation level but not the repeatable read isolation level.

Questions:
I'm looking for a summary document of how the various database engines implement ANSI SQL transaction management. For performance reasons, is the read committed isolation level the most commonly implemented default by the various database vendors? (From what I gather it is also the default for Sybase, SQLServer and PostgreSQL.) Is the read committed isolation level the most practical? Has anyone ever administered a database or application with a different isolation level and why? Is there any summary document of transaction features for all the database vendors?

Theoretically and Academically yours,
Steve Orr
Bozeman, Montana
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Orr, Steve
INET: sorr_at_rightnow.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: MacGregor, Ian A.
INET: ian_at_SLAC.Stanford.EDU

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------