I hope this mailing list could help me answer these questions. Thanks.
---------- Forwarded message ----------
From: Xuekun Hu <xuekun.hu@stripped>
Date: Tue, Jul 7, 2009 at 9:32 PM
Subject: ANSI Isolation Level vs. InnoDB consistent read implementation
To: mysql <mysql@stripped>
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.