From: Jay Pipes
Date: July 8 2009 12:39pm
Subject: Re: Fwd: ANSI Isolation Level vs. InnoDB consistent read implementation
List-Archive: http://lists.mysql.com/internals/37127
Message-Id: <4A549393.1030705@sun.com>
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset=ISO-8859-1
Content-Transfer-Encoding: 8BIT

Guilhem Bichot wrote:
> Xuekun Hu a écrit, Le 08.07.2009 02:39:
>> 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?
> 
> REPEATABLE READ never uses next-key locking for SELECT.
> So, to protect your SELECTs from phantoms, you need to switch to 
> SERIALIZABLE.

This is incorrect.  Please see manual:

"By default, InnoDB operates in REPEATABLE READ transaction isolation 
level and with the innodb_locks_unsafe_for_binlog  system variable 
disabled. In this case, InnoDB uses next-key locks for searches and 
index scans, which prevents phantom rows"

http://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html

Cheers,

Jay