START TRANSACTION [WITH CONSISTENT SNAPSHOT] | BEGIN [WORK] COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] SET autocommit = {0 | 1}
The START
TRANSACTION
or
BEGIN
statement
begins a new transaction. COMMIT
commits the current transaction, making its changes permanent.
ROLLBACK
rolls
back the current transaction, canceling its changes. The
SET autocommit
statement disables or enables the default autocommit mode for the
current session.
The optional WORK
keyword is supported for
COMMIT
and
ROLLBACK
, as are
the CHAIN
and RELEASE
clauses. CHAIN
and RELEASE
can be used for additional control over transaction completion.
The value of the completion_type
system variable determines the default completion behavior. See
Section 5.1.4, “Server System Variables”.
Within all stored programs (stored procedures and functions,
triggers, and events), the parser treats
BEGIN [WORK]
as the beginning of a
BEGIN ...
END
block. Begin a transaction in this context with
START
TRANSACTION
instead.
The AND CHAIN
clause causes a new transaction
to begin as soon as the current one ends, and the new transaction
has the same isolation level as the just-terminated transaction.
The RELEASE
clause causes the server to
disconnect the current client session after terminating the
current transaction. Including the NO
keyword
suppresses CHAIN
or RELEASE
completion, which can be useful if the
completion_type
system variable
is set to cause chaining or release completion by default.
By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk to make it permanent. To disable autocommit mode, use the following statement:
SET autocommit=0;
After disabling autocommit mode by setting the
autocommit
variable to zero,
changes to transaction-safe tables (such as those for
InnoDB
or
NDBCLUSTER
) are not made permanent
immediately. You must use COMMIT
to
store your changes to disk or
ROLLBACK
to
ignore the changes.
To disable autocommit mode for a single series of statements, use
the START
TRANSACTION
statement:
START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; COMMIT;
With START
TRANSACTION
, autocommit remains disabled until you end
the transaction with COMMIT
or
ROLLBACK
. The
autocommit mode then reverts to its previous state.
BEGIN
and
BEGIN WORK
are
supported as aliases of
START
TRANSACTION
for initiating a transaction.
START
TRANSACTION
is standard SQL syntax and is the
recommended way to start an ad-hoc transaction.
Many APIs used for writing MySQL client applications (such as
JDBC) provide their own methods for starting transactions that
can (and sometimes should) be used instead of sending a
START
TRANSACTION
statement from the client. See
Chapter 20, Connectors and APIs, or the documentation for your
API, for more information.
The BEGIN
statement differs from the use of the BEGIN
keyword that starts a BEGIN ... END
compound
statement. The latter does not begin a transaction. See
Section 12.8.1, “BEGIN ... END
Compound Statement Syntax”.
You can also begin a transaction like this:
START TRANSACTION WITH CONSISTENT SNAPSHOT;
The WITH CONSISTENT SNAPSHOT
clause starts a
consistent read for storage engines that are capable of it. This
applies only to InnoDB
. The effect is the same
as issuing a START
TRANSACTION
followed by a
SELECT
from any
InnoDB
table. See
Section 13.6.8.2, “Consistent Nonlocking Reads”. The WITH
CONSISTENT SNAPSHOT
clause does not change the current
transaction isolation level, so it provides a consistent snapshot
only if the current isolation level is one that allows consistent
read (REPEATABLE READ
or
SERIALIZABLE
).
Beginning a transaction causes any pending transaction to be committed. See Section 12.4.3, “Statements That Cause an Implicit Commit”, for more information.
Beginning a transaction also causes table locks acquired with
LOCK TABLES
to be released, as
though you had executed
UNLOCK
TABLES
. Beginning a transaction does not release a
global read lock acquired with
FLUSH TABLES WITH READ
LOCK
.
For best results, transactions should be performed using only tables managed by a single transaction-safe storage engine. Otherwise, the following problems can occur:
If you use tables from more than one transaction-safe storage
engine (such as InnoDB
and
Falcon
), and the transaction isolation
level is not SERIALIZABLE
,
it is possible that when one transaction commits, another
ongoing transaction that uses the same tables will see only
some of the changes made by the first transaction. That is,
the atomicity of transactions is not guaranteed with mixed
engines and inconsistencies can result. (If mixed-engine
transactions are infrequent, you can use
SET
TRANSACTION ISOLATION LEVEL
to set the isolation
level to SERIALIZABLE
on a
per-transaction basis as necessary.)
If you use tables that are not transaction-safe within a transaction, changes to those tables are stored at once, regardless of the status of autocommit mode.
If you issue a
ROLLBACK
statement after updating a nontransactional table within a
transaction, an
ER_WARNING_NOT_COMPLETE_ROLLBACK
warning occurs. Changes to transaction-safe tables are rolled
back, but not changes to nontransaction-safe tables.
Each transaction is stored in the binary log in one chunk, upon
COMMIT
. Transactions that are
rolled back are not logged.
(Exception: Modifications to
nontransactional tables cannot be rolled back. If a transaction
that is rolled back includes modifications to nontransactional
tables, the entire transaction is logged with a
ROLLBACK
statement at the end to ensure that modifications to the
nontransactional tables are replicated.) See
Section 5.2.4, “The Binary Log”.
You can change the isolation level for transactions with
SET TRANSACTION
ISOLATION LEVEL
. See Section 12.4.6, “SET TRANSACTION
Syntax”.
Rolling back can be a slow operation that may occur implicitly
without the user having explicitly asked for it (for example, when
an error occurs). Because of this, SHOW
PROCESSLIST
displays Rolling back
in
the State
column for the session, not only for
explicit rollbacks performed with the
ROLLBACK
statement but also for implicit rollbacks.
Beginning with MySQL 5.1.36, BEGIN
,
COMMIT
, and ROLLBACK
are
no longer affected by
--replicate-do-db
or
--replicate-ignore-db
rules. (Bug#43263)
User Comments
Add your own comment.