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
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 NAME,
DROP FUNCTIONalso cause an implicit commit when used with stored functions, but not with user-defined functions. (
ALTER FUNCTIONcan only be used with stored functions.)
DROP TABLEstatements do not commit a transaction if the
TEMPORARYkeyword 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, which means that the use of such statements causes transactional atomicity to be violated. For example, if you use
CREATE TEMPORARY TABLEand then roll back the transaction, the table remains in existence.
CREATE TABLE ... SELECTcauses 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 #22865.
UNLOCK TABLEScommits a transaction only if any tables currently have been locked with
LOCK TABLESto acquire nontransactional table locks. A commit does not occur for
FLUSH TABLES WITH READ LOCKbecause 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 TRANSACTIONstatement or one of its synonyms.
Statements that cause an implicit commit cannot be used in an XA transaction while the transaction is in an
BEGINstatement differs from the use of the
BEGINkeyword that starts a
BEGIN ... ENDcompound 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:
CREATE TABLEvariants (
CREATE TABLE ... SELECT) that previously were special cases no longer are so because
CREATE TABLEuniformly causes an implicit commit before and after executing.
Transaction-control and locking statements behave as before. If an implicit commit occurs before execution, another does not occur after.