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
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.
These clauses appeared in MariaDB 10.0.
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. The only exception to this rule is that read only transactions can perform DDL statements on temporary tables.
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.
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 (CREATE, ALTER, DROP) and administrative statements (FLUSH
, RESET
, OPTIMIZE
, ANALYZE
, CHECK
, REPAIR
, CACHE INDEX
), 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.
The in_transaction system variable appeared in MariaDB 5.3.
It is a session-only, read-only variable that returns 1
inside a transaction, and 0
if not in a transaction.
The WITH CONSISTENT SNAPSHOT
option starts a consistent read for storage engines such as XtraDB and InnoDB that can do so, the same as if a START TRANSACTION followed by a SELECT from any InnoDB table was issued.
MariaDB 5.3 introduced enhancements to this feature. See Enhancements for START TRANSACTION WITH CONSISTENT SNAPSHOT.
START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; COMMIT;
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/start-transaction/