Micheal,
----- Original Message -----
From: "micheal" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Friday, July 12, 2002 1:35 AM
Subject: Serious doubt about InnoDB isolation level
> Hi MySQL experts there,
> Here is the copy of the mail I sent to the author.
> ...
> 1. According to the document, InnoDB only support
> repeatable read and serializable.Why it does not
> support read committed? Major databases like
> Oracle,DB2,MS-SQL all support read-committed and use
> it as THE default isolation level.
> While in MySQL, I have no way to read committed data
> from another transaction without a new transaction or
> explict locking...which is either infeasible or has
> performance implications.
in all databases, both multiversioning and non-multiversioning, you need use
a locking read (in Oracle SELECT ... FOR UPDATE, InnoDB also has SELECT ...
LOCK IN SHARE MODE) to read the latest committed data. For example, Oracle
without the FOR UPDATE clause reads a snapshot which is taken at the start
of that SELECT, and nothing guarantees that the snapshot contains the latest
committed data when the query returns.
The default behavior of InnoDB with non-locking SELECTs is that they read
the snapshot taken at the start of the FIRST non-locking SELECT issued by
the current transaction. That behavior was chosen because if you have
several non-locking SELECTs inside one transaction, then they all read the
same snapshot and thus are consistent with respect to each other. Thus a
read-only non-locking transaction is serialized with respect to all other
transactions.
I could add yet another clause to InnoDB: SELECT ... READ LATEST SNAPSHOT.
That would read the snapshot taken at the start of this SELECT, not the
snapshot taken at the start of the first SELECT of the transaction. Thus in
a transaction containing several non-locking SELECTs, this would return a
fresher snapshot than the current InnoDB behavior.
> 2. InnoDB's default isolation level is "repeatable
> read", I seriously doubt that's true. IMHO, it is
> "fake" repeatable read, because although you get
> the same data every time, the data could be
> changed and committed by another transaction without
> your notice. I have thought lost update should never
> happen in "repeatable read" mode, right!?
You are right. The isolation levels of Oracle, PostgreSQL, or InnoDB should
rather be called MULTIVERSIONING. But that term is not available in the
SQL-92 standard, which is a pity, because the scale tailored for
non-multiversioning databases poorly describes this. I have pasted below the
relevant section from a SQL-92 standard draft.
..................
The isolation level specifies the kind of phenomena that can occur
during the execution of concurrent SQL-transactions. The following
phenomena are possible:
1) P1 ("Dirty read"): SQL-transaction T1 modifies a row. SQL-
transaction T2 then reads that row before T1 performs a COMMIT.
If T1 then performs a ROLLBACK, T2 will have read a row that was
never committed and that may thus be considered to have never
existed.
2) P2 ("Non-repeatable read"): SQL-transaction T1 reads a row. SQL-
transaction T2 then modifies or deletes that row and performs
a COMMIT. If T1 then attempts to reread the row, it may receive
the modified value or discover that the row has been deleted.
3) P3 ("Phantom"): SQL-transaction T1 reads the set of rows N
that satisfy some <search condition>. SQL-transaction T2 then
executes SQL-statements that generate one or more rows that
satisfy the <search condition> used by SQL-transaction T1. If
SQL-transaction T1 then repeats the initial read with the same
<search condition>, it obtains a different collection of rows.
The four isolation levels guarantee that each SQL-transaction will
be executed completely or not at all, and that no updates will be
lost. The isolation levels are different with respect to phenomena
P1, P2, and P3. Table 9, "SQL-transaction isolation levels and the
three phenomena" specifies the phenomena that are possible and not
possible for a given isolation level.
68 Database Language SQL
X3H2-92-154/DBL CBR-002
4.28 SQL-transactions
__Table_9-SQL-transaction_isolation_levels_and_the_three_phenomena_
_Level__________________P1______P2_______P3________________________
| READ UNCOMMITTED | Possib|e Possib|e Possible |
| | | | |
| READ COMMITTED | Not | Possibl| Possible |
Possible
| REPEATABLE READ | Not | Not | Possible |
| | Possib|e Possib|e |
| | | | |
| SERIALIZABLE | Not | Not | Not Possible |
|______________________|_Possib|e_Possib|e_________________________|
| | | | |
|Note: The exclusion of|these p|enomena |or SQL-transactions ex- |
ecuting at isolation level SERIALIZABLE is a consequence of the
requirement that such transactions be serializable.
.................
> Thank you for your time.
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
..
---------------------------------------------------------------------
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
