(mysql.info) commit
Info Catalog
(mysql.info) transactional-commands
(mysql.info) transactional-commands
(mysql.info) cannot-roll-back
13.4.1 `START TRANSACTION', `COMMIT', and `ROLLBACK' Syntax
-----------------------------------------------------------
START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT = {0 | 1}
The `START TRANSACTION' and `BEGIN' statement begin 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 connection.
Beginning with MySQL 5.0.3, the optional `WORK' keyword is supported
for `COMMIT' and `RELEASE', 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
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 connection 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.
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.
If you are using a transaction-safe storage engine (like `InnoDB',
`BDB' or `NDB Cluster'), you can disable autocommit mode with the
following statement:
SET AUTOCOMMIT=0;
After disabling autocommit mode by setting the `AUTOCOMMIT' variable to
zero, you must use `COMMIT' to store your changes to disk or `ROLLBACK'
if you want to ignore the changes you have made since the beginning of
your transaction.
To disable autocommit mode 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.
`BEGIN' and `BEGIN WORK' are supported as aliases of `START
TRANSACTION' for initiating a transaction. `START TRANSACTION' is
standard SQL syntax and is the recommended way to start an ad-hoc
transaction.
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 begin-end.
You can also begin a transaction like this:
START TRANSACTION WITH CONSISTENT SNAPSHOT;
The `WITH CONSISTENT SNAPSHOT' clause starts a consistent read for
storage engines that are capable of it. Currently, this applies only
to `InnoDB'. The effect is the same as issuing a `START TRANSACTION'
followed by a `SELECT' from any `InnoDB' table. See
innodb-consistent-read.
The `WITH CONSISTENT SNAPSHOT' clause does not change the current
transaction isolation level, so it provides a consistent snapshot only
if the current isolation level is one that allows consistent read
(`REPEATABLE READ' or `SERIALIZABLE').
Beginning a transaction causes an implicit `UNLOCK TABLES' to be
performed.
For best results, transactions should be performed using only tables
managed by a single transactional storage engine. Otherwise, the
following problems can occur:
* If you use tables from more than one transaction-safe storage
engine (such as `InnoDB' and `BDB'), 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 non-transaction-safe tables within a transaction, any
changes to those tables are stored at once, regardless of the
status of autocommit mode.
If you issue a `ROLLBACK' statement after updating a
non-transactional table within a transaction, an
`ER_WARNING_NOT_COMPLETE_ROLLBACK' warning occurs. Changes to
transaction-safe tables are rolled back, but not changes to
non-transaction-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 non-transactional tables cannot be
rolled back. If a transaction that is rolled back includes
modifications to non-transactional tables, the entire transaction is
logged with a `ROLLBACK' statement at the end to ensure that the
modifications to those tables are replicated.) See binary-log.
You can change the isolation level for transactions with `SET
TRANSACTION ISOLATION LEVEL'. See set-transaction.
Rolling back can be a slow operation that may occur 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 connection during implicit and explicit
(`ROLLBACK' SQL statement) rollbacks.
Info Catalog
(mysql.info) transactional-commands
(mysql.info) transactional-commands
(mysql.info) cannot-roll-back
automatically generated byinfo2html