13.4.1. START TRANSACTION, COMMIT, and ROLLBACK Syntax
START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT = {0 | 1}
The START TRANSACTION and
BEGIN statement begin 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 connection.
The optional WORK keyword is supported for
COMMIT and RELEASE, 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.2.2, “Server System Variables”.
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 connection 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.
If you are using a transaction-safe storage engine (like
InnoDB, BDB or
NDB Cluster), you can disable autocommit mode
with the following statement:
SET AUTOCOMMIT=0;
After disabling autocommit mode by setting the
AUTOCOMMIT variable to zero, you must use
COMMIT to store your changes to disk or
ROLLBACK if you want to ignore the changes
you have made since the beginning of your transaction.
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.
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 19.2.5, “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.
Currently, 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 14.2.10.4, “Consistent Non-Locking Read”.
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 an implicit UNLOCK
TABLES to be performed.
For best results, transactions should be performed using only
tables managed by a single transactional storage engine.
Otherwise, the following problems can occur:
If you use tables from more than one transaction-safe
storage engine (such as InnoDB and
BDB), 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 non-transaction-safe tables within a transaction,
any changes to those tables are stored at once, regardless
of the status of autocommit mode.
If you issue a ROLLBACK statement after
updating a non-transactional table within a transaction, an
ER_WARNING_NOT_COMPLETE_ROLLBACK warning
occurs. Changes to transaction-safe tables are rolled back,
but not changes to non-transaction-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 non-transactional tables cannot be rolled back.
If a transaction that is rolled back includes modifications to
non-transactional tables, the entire transaction is logged with
a ROLLBACK statement at the end to ensure
that the modifications to those tables are replicated.) See
Section 5.11.4, “The Binary Log”.
You can change the isolation level for transactions with
SET TRANSACTION ISOLATION LEVEL. See
Section 13.4.6, “SET TRANSACTION Syntax”.
Rolling back can be a slow operation that may occur 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 connection during
implicit and explicit (ROLLBACK SQL
statement) rollbacks.
