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 [EMAIL PROTECTED] -----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; ---------------------------------------------------------------------------- ------------------------------------------- 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 [EMAIL PROTECTED] -----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: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
