Statements That Cannot Be Rolled Back
1113
13.3.2. Statements That Cannot Be Rolled Back
Some statements cannot be rolled back. In general, these include data definition language (DDL)
statements, such as those that create or drop databases, those that create, drop, or alter tables or
stored routines.
You should design your transactions not to include such statements. If you issue a statement early in
a transaction that cannot be rolled back, and then another statement later fails, the full effect of the
transaction cannot be rolled back in such cases by issuing a
ROLLBACK
statement.
13.3.3. Statements That Cause an Implicit Commit
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.
• Data definition language (DDL) statements that define or modify database objects.
ALTER
TABLE
,
CREATE INDEX
,
DROP INDEX
,
DROP TABLE
,
RENAME TABLE
.
ALTER TABLE
,
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.
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.
Beginning with MySQL 5.0.8,
CREATE TABLE
,
CREATE DATABASE
DROP DATABASE
, and
TRUNCATE TABLE
cause an implicit commit.
Beginning with MySQL 5.0.13,
ALTER PROCEDURE
,
CREATE PROCEDURE
, and
DROP PROCEDURE
cause an implicit commit.
Also beginning with MySQL 5.0.13,
ALTER FUNCTION
,
CREATE FUNCTION
and
DROP FUNCTION
cause an implicit commit when used with stored functions, but not with UDFs. (
ALTER FUNCTION
can only be used with stored functions.)
Beginning with MySQL 5.0.15,
ALTER VIEW
,
CREATE TRIGGER
,
CREATE VIEW
,
DROP TRIGGER
,
and
DROP VIEW
cause an implicit commit.
• Statements that implicitly use or modify tables in the
mysql
database. Beginning with MySQL
5.0.15,
CREATE USER
,
DROP USER
, and
RENAME USER
cause an implicit commit.
• Transaction-control and locking statements.
BEGIN
,
LOCK TABLES
,
SET autocommit = 1
(if
the value is not already 1),
START TRANSACTION
,
UNLOCK TABLES
.
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.
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.
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 13.6.1, “
BEGIN ...
END
Compound-Statement Syntax”
.
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
