sql-info.de

4. Transactions and InnoDB tables

4.1. Transactions on mixed table types

If, during a transaction, changes are made to a mixture of transaction-capable and transaction-incapable tables, and a ROLLBACK is subsequently issued, changes made to the transaction-incapable tables cannot be rolled back. A corresponding warning is issued only after the ROLLBACK.

This behaviour is a consequence of MySQL's mixed table engine model. Although understandable, it causes problems when a table was mistakenly not defined as transaction-capable (which can easily happen - see section 2.3 "Supported syntax, unsupported feature" and 2.4 "Whoops, no InnoDB table support").

4.2. Altering InnoDB tables

Apparently CREATE INDEX, DROP INDEX and ALTER TABLE operations on InnoDB tables cause the entire table to be rebuilt. Depending on the size of the table this can be a long operation. See: http://lists.mysql.com/list.php?1:mss:139208:jkkcnkbjkhdagmffkcnh

4.3. Epic Feature Length Rollbacks

The time required to roll back a transaction increases in proportion with the number of operations performed during that transaction.