COMMIT

Syntax:
START TRANSACTION
[transaction_characteristic [, transaction_characteristic] ...]

transaction_characteristic: {
WITH CONSISTENT SNAPSHOT
| READ WRITE
| READ ONLY
}

BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}

These statements provide control over use of transactions:

o START TRANSACTION or BEGIN start a new transaction.

o COMMIT commits the current transaction, making its changes permanent.

o ROLLBACK rolls back the current transaction, canceling its changes.

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

By default, MySQL runs with autocommit mode enabled. This means that,
when not otherwise inside a transaction, each statement is atomic, as
if it were surrounded by START TRANSACTION and COMMIT. You cannot use
ROLLBACK to undo the effect; however, if an error occurs during
statement execution, the statement is rolled back.

To disable autocommit mode implicitly 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.

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

o 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
https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html.
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. A warning is generated when the WITH
CONSISTENT SNAPSHOT clause is ignored.

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

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
https://dev.mysql.com/doc/refman/8.0/en/innodb-performance-ro-txn.htm
l 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.

For additional information about transaction access mode, including
ways to change the default mode, see [HELP SET TRANSACTION].

If the read_only system variable is enabled, explicitly starting a
transaction with START TRANSACTION READ WRITE requires the
CONNECTION_ADMIN privilege (or the deprecated 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
https://dev.mysql.com/doc/refman/8.0/en/connectors-apis.html, 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.

autocommit is a session variable and must be set for each session. To
disable autocommit mode for each new connection, see the description of
the autocommit system variable at
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html.

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.

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 [HELP BEGIN END].

*Note*:

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
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html.

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 new transaction also uses the
same access mode (READ WRITE or READ ONLY) 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.

URL: https://dev.mysql.com/doc/refman/8.0/en/commit.html