Re: Statement-based replication is disabled for Falcon

[Ann Harrison]

Statement based replication does not produce correct
results with repeatable read isolation level.

InnoDB does a number of tricks to make statement based
replication work. Both InnoDB and Falcon provide more
or less repeatable read isolation level. Both provide
more than the standard specifies, generally, because
both use MVCC to create a virtual database snapshot.
This technique avoids the phantoms that the standard
allows in repeatable read isolation.

InnoDB adds an unusual wrinkle. Their normal select
produces repeatable results, but if you add the "FOR
UPDATE" qualifier, the transaction sees changes that
were committed after it started. Essentially, a
SELECT ... FOR UPDATE runs in read committed isolation
mode.

It turns out that making statement based replication work
requires that anomaly, because transactions are run in
commit order. In fact, InnoDB implicitly does a select
for update when a subselect drives an insert, update, or
delete. InnoDB also locks "end of table" and index
ranges, and offers several ways to avoid interleaving
autonumber values when two transactions do simultaneous
inserts.

Row based replication produces correct copies of the
master without any of these tricks.