Skip navigation links

Additional languages
12.4.4 SAVEPOINT and ROLLBACK TO SAVEPOINT Syntax »
Section Navigation      [Toggle]

12.4.3. Statements That Cause an Implicit Commit

Each of the following statements (and any synonyms for them) implicitly end a transaction, as if you had done a COMMIT before executing the statement:

  • ALTER EVENT, ALTER FUNCTION, ALTER PROCEDURE, ALTER TABLE, ALTER VIEW, BEGIN, CREATE DATABASE, CREATE EVENT, CREATE FUNCTION, CREATE INDEX, CREATE PROCEDURE, CREATE TABLE, CREATE TRIGGER, CREATE USER, CREATE VIEW, DROP DATABASE, DROP EVENT, DROP FUNCTION, DROP INDEX, DROP PROCEDURE, DROP TABLE, DROP TRIGGER, DROP USER, DROP VIEW, LOAD DATA INFILE LOCK TABLES, RENAME TABLE, RENAME USER, SET AUTOCOMMIT=1 (if the value is not already 1), START TRANSACTION, TRUNCATE TABLE, UNLOCK TABLES.

    The BEGIN statement differs from the use of the BEGIN keyword that starts a BEGIN ... END compound statement. The latter does not cause an implicit commit. See Section 21.2.5, “BEGIN ... END Compound Statement Syntax”.

  • UNLOCK TABLES commits a transaction only if any tables currently have been locked with LOCK TABLES to acquire non-transactional table locks. A commit does not occur for UNLOCK TABLES following FLUSH TABLES WITH READ LOCK because the latter statement does not acquire table-level locks.

  • The CREATE TABLE statement in InnoDB is processed as a single transaction. This means that a ROLLBACK from the user does not undo CREATE TABLE statements the user made during that transaction.

  • CREATE TABLE and DROP TABLE do not commit a transaction if the TEMPORARY 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.

  • LOAD DATA INFILE causes an implicit commit only for tables using the NDB storage engine. For more information, see Bug#11151.

  • CREATE TABLE ... SELECT causes an implicit commit before and after the statement is executed when you are creating non-temporary 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.

  • Beginning with MySQL 6.0.3, GRANT and REVOKE statements cause an implicit commit.

  • Beginning with MySQL 6.0.4, SET PASSWORD statements cause an implicit commit.

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 ACTIVE state.