SET autocommit disables or enables the
default autocommit mode for the current session.

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. The change cannot be rolled back.

To disable autocommit mode implicitly for a single series of
statements, use the START TRANSACTION
statement:

With START TRANSACTION, autocommit remains
disabled until you end the transaction with
COMMIT or ROLLBACK. The
autocommit mode then reverts to its previous state.

START TRANSACTION permits several modifiers
that control transaction characteristics. To specify multiple
modifiers, separate them by commas.

The WITH CONSISTENT SNAPSHOT modifier
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 14.2.4, “Consistent Nonlocking Reads”. The
WITH CONSISTENT SNAPSHOT modifier does not
change the current transaction
isolation level,
so it provides a consistent snapshot only if the current
isolation level is one that permits a consistent read. The
only isolation level that permits a consistent read is
REPEATABLE READ. For all
other isolation levels, the WITH CONSISTENT
SNAPSHOT clause is ignored.

The READ WRITE and READ
ONLY modifiers set the transaction access mode. They
permit or prohibit changes to tables used in the transaction.
The READ ONLY restriction prevents the
transaction from modifying or locking both transactional and
nontransactional tables that are visible to other
transactions; the transaction can still modify or lock
temporary tables. These modifiers are available as of MySQL
5.6.5.

MySQL enables extra optimizations for queries on
InnoDB tables when the transaction is known
to be read-only. Specifying READ ONLY
ensures these optimizations are applied in cases where the
read-only status cannot be determined automatically. See
Section 14.13.14, “Optimizing InnoDB Read-Only Transactions” for more
information.

If no access mode is specified, the default mode applies.
Unless the default has been changed, it is read/write. It is
not permitted to specify both READ WRITE
and READ ONLY in the same statement.

In read-only mode, it remains possible to change tables
created with the TEMPORARY keyword using
DML statements. Changes made with DDL statements are not
permitted, just as with permanent tables.

If the read_only system
variable is enabled, explicitly starting a transaction with
START TRANSACTION READ WRITE requires the
SUPER privilege.

Important

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 23, Connectors and APIs, or the documentation for
your API, for more information.

To disable autocommit mode explicitly, 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
NDB) are not made permanent
immediately. You must use COMMIT to
store your changes to disk or ROLLBACK to
ignore the changes.

BEGIN and BEGIN WORK are
supported as aliases of START TRANSACTION for
initiating a transaction. START TRANSACTION is
standard SQL syntax, is the recommended way to start an ad-hoc
transaction, and permits modifiers that BEGIN
does not.

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 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”.

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.

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 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”.

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.