The statements listed in this section (and any synonyms for them) implicitly end any transaction active in the
current session, as if you had done a
COMMIT before executing the statement. As of MySQL 5.5.3, most of these
statements also cause an implicit commit after executing; for additional details, see the end of this section.
Data definition language (DDL) statements that define or
modify database objects.
ALTER DATABASE ... UPGRADE DATA DIRECTORY
CREATE TABLE, and
DROP TABLE do not commit a transaction if the
keyword is used. (This does not apply to other operations on temporary tables such as
CREATE INDEX, which do cause a commit.) However, although no
implicit commit occurs, neither can the statement be rolled back. Therefore, use of such statements
will violate transaction atomicity: For example, if you use
CREATE TEMPORARY TABLE and then roll back the transaction, the
table remains in existence.
CREATE TABLE ... SELECT causes an implicit commit before and after
the statement is executed when you are creating nontemporary tables. (No commit occurs for
CREATE TEMPORARY TABLE ... SELECT.) This is to prevent an issue
during replication where the table could be created on the master after a rollback, but fail to be
recorded in the binary log, and therefore not replicated to the slave. For more information, see Bug
TABLES commits a transaction only if any tables currently have been locked with
TABLES to acquire nontransactional table locks. A commit does not occur for
FLUSH TABLES WITH READ LOCK because the latter statement does not
acquire table-level locks.
Transactions cannot be nested. This is a consequence of the implicit commit performed for any
current transaction when you issue a
START TRANSACTION statement or one of its synonyms.
Statements that cause an implicit commit cannot be used in an XA transaction while the transaction
is in an
BEGIN statement differs from the use of the
keyword that starts a
BEGIN ... END compound statement. The latter does not cause an
implicit commit. See Section 13.6.1,
BEGIN ... END Compound-Statement Syntax".
As of MySQL 5.5.3, most statements that previously caused an implicit commit before executing also do so after executing. The intent is to handle each such statement in its own special transaction because it cannot be rolled back anyway. The following list provides additional details pertaining to this change:
TABLE variants (
InnoDB tables and
CREATE TABLE ... SELECT) that previously were special cases no longer are
uniformly causes an implicit commit before and after executing.
Transaction-control and locking statements behave as before.