Jeremy, look at http://www.innodb.com/ibman.html and http://www.innodb.com/todo.html about InnoDB isolation levels.
Currently, SET TRANSACTION ISOLATION LEVEL does not have any effect on MyISAM, BDB, or InnoDB. The standard isolation level of InnoDB is REPEATABLE READ (you can also call that READ-COMMITTED with no phantom rows), the same as in Oracle. By adding ...LOCK IN SHARE MODE (set S-locks) or ...FOR UPDATE (set X-locks) to all your SELECT statements you get SERIALIZABLE execution in InnoDB. Both wait if there are conflicting locks set on the rows they look at (to be precise, index records), for example an the index records of an inserted row have an X-lock on them until the inserting transaction commits. Both read the latest data. I will add the SET TRANSACTION ISOLATION LEVEL feature which lets the user to change the InnoDB isolation level to SERIALIZABLE. It will map the SQL-92 isolation levels in the following way to InnoDB isolation levels: READ-UNCOMMITTED -> REPEATABLE READ READ-COMMITTED -> REPEATABLE READ REPEATABLE READ -> REPEATABLE READ SERIALIZABLE -> SERIALIZABLE I have no plans of adding READ-UNCOMMITTED to InnoDB, because there is no such level in Oracle either. READ-UNCOMMITTED is usually used in non-multiversioning databases to avoid excessive locking when you run big SELECT queries on tables. The penalty you pay in them for READ-UNCOMMITTED is that your reports may be a bit inconsistent. The standard InnoDB isolation level REPEATABLE READ is not equivalent to SERIALIZABLE. Consider the following example: there are two transactions which both do SELECT COUNT(*) FROM T and INSERT INTO T VALUES (...). Both may see the row count as 10, if they are running concurrently, and then do the inserts. But in any serial (= strictly consecutive) execution of these two transactions the latter should see the row count as 11. But, if we add LOCK IN SHARE MODE to the SELECT query, then inevitably one of the transactions will see the count as 11. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com -----Original Message----- From: Jeremy Zawodny <[EMAIL PROTECTED]> To: Heikki Tuuri <[EMAIL PROTECTED]> Cc: [EMAIL PROTECTED] <[EMAIL PROTECTED]> Date: Sunday, January 27, 2002 3:52 AM Subject: InnoDB and SET TRANSACTION ISOLATION LEVEL, etc... >Okay, I'm trying to make sense of several things here (so that I can >clearly document them), so bear with me. > >First of all, there was some syntax added to MySQL for setting >transaction isolation levels: > > http://www.mysql.com/doc/S/E/SET_TRANSACTION.html > >around the time that Gemini/MySQL work first started happening, I >think. > >Since InnoDB uses MVCC and row-level locking for transactions, I'm >wondering how that syntax interacts with InnoDB. Does it have any >effect? And what isolation levels does it support? > >Here's what I think I know. > >I'm reading about InnoDB's transaction model: > > http://www.mysql.com/doc/I/n/InnoDB_transaction_model.html > >and using some PostgreSQL docs for reference: > > http://www.postgresql.org/idocs/index.php?transaction-iso.html > >The first case is READ-UNCOMMITTED. > >InnoDB's doesn't have the capability as far as I can tell. Is that >true? No dirty reads? > >(PostgreSQL doesn't support READ-UNCOMMITTED.) > >Next comes READ-COMMITTED. > > http://www.mysql.com/doc/I/n/InnoDB_consistent_read.html > >This is InnoDB's default, with non-locking reads and all that good >stuff. Easy. :-) > >(PostgreSQL, of course, supports READ-COMMITTED.) > >Then comes REPEATABLE-READ. > > http://www.mysql.com/doc/I/n/InnoDB_locking_reads.html > >InnoDB handles this via SELECT ... LOCK [IN SHARE MODE|FOR UPDATE]. >Or that's what it would appear. However, I'm a bit confused by the >information on how IN SHARE MODE differs from FOR UPDATE. > >On IN SHARE MODE, the docs say: > > Performing a read in share mode means that we read the latest > available data, and set a shared mode lock on the rows we read. If > the latest data belongs to a yet uncommitted transaction of > another user, we will wait until that transaction commits. A > shared mode lock prevents others from updating or deleting the row > we have read. > >Which makes sense, I think. We get the *latest available* data, >meaning the most recently committed transaction's data. But if the >data is part of an open transaction, then we wait for it. Once we >have the data, we get a shared lock so that nobody can change the data >until we're done. Right? Just want to be positive I have this right >(and document it correctly). > >On FOR UPDATE, the docs say: > > A SELECT ... FOR UPDATE will read the latest available data > setting exclusive locks on each row it reads. Thus it sets the > same locks a searched SQL UPDATE would set on the rows. > >This one also reads the *latest available data* but it DOES NOT say if >it will wait for an open transaction that involves the data like IN >SHARE MODE does. Are the docs correct, or is this an omission? > >Asked another way, is FOR UPDATE a superset of IN SHARE MODE? Or is >it different in a subtle way? > >(PostgreSQL doesn't support REPEATABLE-READ.) > >Finally, we have the SERIALIZABLE isolation level. > >The description of how InnoDB solves the phantom read problem (which >is the whole point of the SERIALIZABLE isolation level): > > http://www.mysql.com/doc/I/n/InnoDB_Next-key_locking.html > >leads me to believe that is is ALWAYS in effect. If true, that means >that what I thought was REPEATABLE-READ in InnoDB is actually >SERIALIZABLE. Is that correct? > >(PostgreSQL supports SERIALIZABLE.) > >In Summary > > - PostgreSQL does READ-COMMITTED and SERIALIZABLE. > > - InnoDB *appears* to do the same (and no more). Is that correct, > or have I missed something important? > > - Gemini supports all four isolation levels. > >And the meta-question stated again: > > Does the SET TRANSACTION ISOLATION LEVEL have any effect on any > table hander other than Gemini? The manual really doesn't say > (that I can find). > >I could construct tests for each of these, but what are the odds that >I'd get them all right? :-) > >Thanks for any clarification and/or confirmation on this stuff. > >Jeremy >-- >Jeremy D. Zawodny, <[EMAIL PROTECTED]> >Technical Yahoo - Yahoo Finance >Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 > >MySQL 3.23.41-max: up 24 days, processed 539,920,742 queries (259/sec. avg) --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
