| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: ANSI Isolation Levels
I have not read the ANSI specifications, however the gist of "repeatable reads" is that the query will return the same data each time it is issued by a transaction. "Set transaction read only"
does provide this. Your attention is invited to ...
http://otn.oracle.com/docs/products/oracle9i/doc_library/901_doc/server.901/a88856/c21cnsis.htm#3374
There certainly could be nuances which prevent "set transaction read only" from meeting the "repeatable read" specification. There is also the question of setting it globally vs. per transaction. There is no way to set "repeatable read" globally in Oracle. I thought Oracle planned to or had removed the ability to set "serializable" globally as well.
Does the read committed specification written to say that committed blocks are always readable or that dirty blocks are never read? What about a more sophisticated model. An update only locks the changed rows. A read blocks any further changes and would succeed if it didn't visit the changed rows. I don't really want to go on building a hypothetical system. I thought at one time databases such as Sybase did have readers blocking writers and vice versa, but I don't know the circumstances.
Ian MacGregor
Stanford Linear Accelerator Center
ian_at_SLAC.Stanford.edu
-----Original Message-----
Sent: Friday, September 06, 2002 9:49 AM
To: Multiple recipients of list ORACLE-L
Hi Ian,
> The purpose of the command "set transaction read only" is to
> implement the repeatable read isolation level.
Did you misspeak? As I understand it, the purpose of the set transaction
command is to set the isolation level of which there are 4 specified in the
ANSI spec and Oracle does not support the "repeatable read" isolation level
as of 8i. (As I understand it there are ambiguities in the ANSI spec and
efforts are underway to provide clarification with the anticipated result
that there will be even more granularity in isolations levels.)
> I thought MySQL, at least the earlier versions, had no concept of a
> transaction
You're right about the earlier versions but with current versions MySQL now
has a "table type" of "InnoDB" (as well as table types of ISAM and Berkeley
DB) and this supports transactions, referential integrity and row level
locking. Without this option MySQL transaction support is limited to full
table locks with no concept of rollbacks. With the InnoDB option MySQL is
now "ACID" compliant. Supposedly the Berkeley DB option supports
transactions but it's not a workable solution.
> 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.
Hmmm... by blocking access to an object altogether there is no reading at
all including read committed. Right?
Steve Orr
-----Original Message-----
Sent: Thursday, September 05, 2002 6:08 PM
To: Multiple recipients of list ORACLE-L
Importance: High
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;
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 -- 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 -------------------------------------------------------------------- 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).Received on Fri Sep 06 2002 - 18:03:22 CDT
 |
 |