Syntax
------

START TRANSACTION [transaction_property [, transaction_property] ...] | BEGIN
[WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}

transaction_property:
  WITH CONSISTENT SNAPSHOT
 | READ WRITE
 | READ ONLY

Description
-----------

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.

START TRANSACTION and SET autocommit = 1 implicitly commit the current
transaction, if any.

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.

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.

Access Mode
-----------

The access mode specifies whether the transaction is allowed to write data or
not. By default, transactions are in READ WRITE mode (see the tx_read_only
system variable). READ ONLY mode allows the storage engine to apply
optimizations that cannot be used for transactions which write data. Note that
unlike the global read_only mode, READ_ONLY ADMIN (and SUPER before MariaDB
10.11.0) privilege doesn't allow writes and DDL statements on temporary tables
are not allowed either.

It is not permitted to specify both READ WRITE and READ ONLY in the same
statement.

READ WRITE and READ ONLY can also be specified in the SET TRANSACTION
statement, in which case the specified mode is valid for all sessions, or for
all subsequent transaction used by the current session.

autocommit
----------

By default, MariaDB runs with autocommit mode enabled. This means that as soon
as you execute a statement that updates (modifies) a table, MariaDB 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.

DDL Statements
--------------

DDL statements (CREATE, ALTER, DROP) and administrative statements (FLUSH,
RESET, OPTIMIZE, ANALYZE, CHECK, REPAIR, CACHE INDEX), transaction management
statements (BEGIN, START TRANSACTION) and LOAD DATA INFILE, cause an implicit
COMMIT and start a new transaction. An exception to this rule are the DDL that
operate on temporary tables: you can CREATE, ALTER and DROP them without
causing any COMMIT, but those actions cannot be rolled back. This means that
if you call ROLLBACK, the temporary tables you created in the transaction will
remain, while the rest of the transaction will be rolled back.

Transactions cannot be used in Stored Functions or Triggers. In Stored
Procedures and Events BEGIN is not allowed, so you should use START
TRANSACTION instead.

A transaction acquires a metadata lock on every table it accesses to prevent
other connections from altering their structure. The lock is released at the
end of the transaction. This happens even with non-transactional storage
engines (like MEMORY or CONNECT), so it makes sense to use transactions with
non-transactional tables.

in_transaction
--------------

The in_transaction system variable is a session-only, read-only variable that
returns 1 inside a transaction, and 0 if not in a transaction.

WITH CONSISTENT SNAPSHOT
------------------------

The WITH CONSISTENT SNAPSHOT option starts a consistent read for storage
engines such as InnoDB that can do so, the same as if a START TRANSACTION
followed by a SELECT from any InnoDB table was issued.

See Enhancements for START TRANSACTION WITH CONSISTENT SNAPSHOT.

Examples
--------

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

URL: https://mariadb.com/kb/en/start-transaction/