Begin a new transaction. This statement initiates a transaction, optionally setting characteristics like consistent snapshots or read-only mode.
The START TRANSACTION or BEGIN statement begins a new transaction. commits the current transaction, making its changes permanent. rolls back the current transaction, canceling its changes. The statement disables or enables the default autocommit mode for the current session.
START TRANSACTION and SET autocommit = 1 implicitly commit the current transaction, if there is one.
The optional WORK keyword is supported forCOMMIT and ROLLBACK, as are theCHAIN and RELEASE clauses.CHAIN and RELEASE can be used for additional control over transaction completion. The value of the 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 suppressesCHAIN or RELEASE completion, which can be useful if the system variable is set to cause chaining or release completion by default.
The access mode specifies whether the transaction is allowed to write data or not. By default, transactions are in READ WRITE mode (see the 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 mode, the privilege doesn't allow writes and DDL statements on temporary tables are not allowed either.
The access mode specifies whether the transaction is allowed to write data or not. By default, transactions are in READ WRITE mode (see the 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 mode, the 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 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 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:
After disabling autocommit mode by setting the autocommit variable to zero, changes to transaction-safe tables (such as those for InnoDB orNDBCLUSTER) 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), transaction management statements (BEGIN, START TRANSACTION) and LOAD DATA INFILE, cause an implicit COMMIT
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 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 or ), so it makes sense to use transactions with non-transactional tables.
The system variable 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 that can do so, the same as if a START TRANSACTION followed by a SELECT from any InnoDB table was issued.
See .
This page is licensed: GPLv2, originally from
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 ONLYCREATEALTERDROPCOMMITROLLBACKSET autocommit=0;START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;