Hello David,
On 7/30/2012 11:46 AM, David Lerer wrote:
Thanks Shawn. This is very useful.
Could you shed some light on how rolled-back transactions (or not-yet-committed
transactions for that matter) are treated as far as the binary logs? Are these
updates actually go to the binary logs, thus trigger replication to the salve?
Thanks, David.
There are two scenarios to worry about
1) This transaction involves no other storage engine except InnoDB.
2) This transaction involves any other storage engine as well as InnoDB.
In both cases, they start out the same. A temporary binary log cache is
generated to keep track of the transaction as it occurs. All events are
recorded to this cache which may become a temporary file if you exceed
certain limits.
In the event of a COMMIT, this separate cache (or file) is appended to
the normal binary log file at the position that corresponds to the
moment where the COMMIT occurs.
In the event of a ROLLBACK, there is a difference. In scenario 1) where
all changes are transacted, the cache is just dropped. As there were no
changes to the actual data, nothing needs to enter the binary log. In
scenario 2), however, permanent changes to the data exist on the master.
In this case, the entire cache is appended (as normal) but the last
command is the ROLLBACK. This applies the non-transacted changes to the
slave while removing the transacted changes from the slave thus keeping
the two systems in sync.
Beginning with 5.5.9, we created a separate cache specifically for the
non-transacted events to reduce our replication overhead. Only those
statements will be transmitted in the event of a ROLLBACK.
http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Binlog_cache_disk_use
Regards,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
