Additional languages
« 13.4.2 Statements That Cannot Be Rolled Back
13.4.4 SAVEPOINT and ROLLBACK TO SAVEPOINT Syntax »
Section Navigation      [Toggle]

13.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 TABLE, BEGIN, CREATE INDEX, DROP INDEX, DROP TABLE, LOAD MASTER DATA, LOCK TABLES, RENAME TABLE, SET AUTOCOMMIT=1, START TRANSACTION, UNLOCK TABLES.

  • Prior to MySQL 4.0.13, CREATE TABLE commits a transaction if the binary update log is enabled. The CREATE TABLE, CREATE DATABASE DROP DATABASE, and TRUNCATE TABLE statements cause an implicit commit beginning with MySQL 4.1.13.

  • UNLOCK TABLES commits a transaction only if any tables currently have been locked with LOCK TABLES. This 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.)

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.


User Comments

Posted by Ferry Boender on June 29 2006 12:38pm[Delete] [Edit]

Note that a CREATE TEMPORARY TABLE does not seem to cause an implicit COMMIT.

Add your own comment.