Ever since MySQL replication has existed, people have dreamed of
a good solution to automatically split read from write
operations, sending the writes to the MySQL master and load
balancing the reads over a set of MySQL slaves. While if at first
it seems easy to solve, the reality is far more complex.
First, the tool needs to make sure it parses and analyses
correctly all the forms of SQL MySQL supports in order to sort
writes from reads, something that is not as easy as it seems.
Second, it needs to take into account if a session is in a
transaction or not.
While in a transaction, the default transaction isolation level
in InnoDB, Repeatable-read, and the MVCC framework insure that
you’ll get a consistent view for the duration of the transaction.
That means all statements executed inside a transaction must run
on the master but, when the transaction commits or rollbacks, the
following select statements on the session can be again …[Read more]