List:General Discussion« Previous MessageNext Message »
From:Xuekun Hu Date:July 7 2009 1:32pm
Subject:ANSI Isolation Level vs. InnoDB consistent read implementation
View as plain text  
Hi, Guys

I think I got puzzled about the transaction isolation level and the
InnoDB consistent read implementation.

My understanding about the ANSI isolation level are:
1. READ-COMMITTED is to protect against Lost Updates, Dirty Reads, and
NOT protect against Nonrepeatable Reads and Phantoms.
2. REPEATABLE-READ is to protect against Lost Updates, Dirty Reads,
Nonrepeatable Reads, and NOT protect against Phantoms.
3. SERIALIZABLE is to protect against Lost Updates, Dirty Reads,
Nonrepeatable Reads, and Phantoms.
4. Here "Nonrepeatable Reads" is also called "Consistent Reads".

While the isolation level in InnoDB implemenation are little different
from the ANSI standard.
1. READ-COMMITTED and REPEATABLE-READ in InnoDB can protect against
Phantom, since innodb has next-key locking support. Right?
2. If so, REPEATABLE-READ can protect against the all four secenaria.
Why need SERIALIZABLE again, or what is the purpose of SERIALIZABLE to
convert all plain SELECT statements to SELECT ... LOCK IN SHARE MODE?
3. Innodb has consistent non-locking read mode. Here the concept of
"consistent non-locking read" is not the concept "Nonrepeatable Read"
that "consistent read". They are different terms. Right?

Am I understanding the InnoDB right? Thanks in advance.

Thx, Xuekun
Thread
ANSI Isolation Level vs. InnoDB consistent read implementationXuekun Hu7 Jul