START TRANSACTION, COMMIT, and ROLLBACK Syntax

妖狐艹你老母 2022-08-10 15:58 116阅读 0赞

本文转载自：http://dev.mysql.com/doc/refman/5.5/en/commit.html

### `START TRANSACTION`, `COMMIT`, and `ROLLBACK` Syntax ###

START TRANSACTION [WITH CONSISTENT SNAPSHOT]
    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][]:

*  `START TRANSACTION` or `BEGIN` start a new transaction.
 *  `COMMIT` commits the current transaction, making its changes permanent.
 *  `ROLLBACK` rolls back the current transaction, canceling its changes.
 *  `SET autocommit` disables or enables the default autocommit mode for the current session.

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 to make it permanent. The change cannot be 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.

You can also begin a transaction like this:

START TRANSACTION WITH CONSISTENT SNAPSHOT;

The `WITH CONSISTENT SNAPSHOT` option 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`][SELECT] from any`InnoDB` table. See [Section 14.5.4, “Consistent Nonlocking Reads”][Section_14.5.4_ _Consistent Nonlocking Reads]. The `WITH CONSISTENT SNAPSHOT` option 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`][REPEATABLE READ]. For all other isolation levels, the `WITH CONSISTENT SNAPSHOT` clause is ignored. As of MySQL 5.5.34, a warning is generated when the `WITH CONSISTENT SNAPSHOT` is ignored.

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 [Chapter 23, Connectors and APIs][Chapter_23_Connectors and APIs], 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`][autocommit 1] variable to zero, changes to transaction-safe tables (such as those for [`InnoDB`][InnoDB] or [`NDBCLUSTER`][NDBCLUSTER]) are not made permanent immediately. You must use `COMMIT` to store your changes to disk or `ROLLBACK` to ignore the changes.

[`autocommit`][autocommit 1] 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`][autocommit 1] system variable at [Section 5.1.4, “Server System Variables”][Section_5.1.4_ _Server System Variables].

`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`][BEGIN ... END] compound statement. The latter does not begin a transaction. See [Section 13.6.1, “`BEGIN ... END` Compound-Statement Syntax”][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`][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`][completion_type] system variable determines the default completion behavior. See [Section 5.1.4, “Server System Variables”][Section_5.1.4_ _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 session after terminating the current transaction. Including the `NO` keyword suppresses `CHAIN` or `RELEASE` completion, which can be useful if the [`completion_type`][completion_type] system variable is set to cause chaining or release completion by default.

Beginning a transaction causes any pending transaction to be committed. See [Section 13.3.3, “Statements That Cause an Implicit Commit”][Section_13.3.3_ _Statements That Cause an Implicit Commit], for more information.

Beginning a transaction also causes table locks acquired with [`LOCK TABLES`][LOCK TABLES] to be released, as though you had executed [`UNLOCK TABLES`][LOCK TABLES]. Beginning a transaction does not release a global read lock acquired with [`FLUSH TABLES WITH READ LOCK`][FLUSH TABLES WITH READ LOCK].

For best results, transactions should be performed using only tables managed by a single transaction-safe storage engine. Otherwise, the following problems can occur:

*  If you use tables from more than one transaction-safe storage engine (such as `InnoDB`), and the transaction isolation level is not [`SERIALIZABLE`][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`][SET TRANSACTION ISOLATION LEVEL] to set the isolation level to [`SERIALIZABLE`][SERIALIZABLE] on a per-transaction basis as necessary.)
 *  If you use tables that are not transaction-safe within a transaction, changes to those tables are stored at once, regardless of the status of autocommit mode.
 *  If you issue a [`ROLLBACK`][ROLLBACK] statement after updating a nontransactional table within a transaction, an[`ER_WARNING_NOT_COMPLETE_ROLLBACK`][ER_WARNING_NOT_COMPLETE_ROLLBACK] warning occurs. Changes to transaction-safe tables are rolled back, but not changes to nontransaction-safe tables.

Each transaction is stored in the binary log in one chunk, upon [`COMMIT`][ROLLBACK]. Transactions that are rolled back are not logged. (*Exception*: Modifications to nontransactional tables cannot be rolled back. If a transaction that is rolled back includes modifications to nontransactional tables, the entire transaction is logged with a [`ROLLBACK`][ROLLBACK] statement at the end to ensure that modifications to the nontransactional tables are replicated.) See [Section 5.2.4, “The Binary Log”][Section_5.2.4_ _The Binary Log].

You can change the isolation level for transactions with the [`SET TRANSACTION`][SET TRANSACTION ISOLATION LEVEL] statement. See [Section 13.3.6, “`SET TRANSACTION` Syntax”][SET TRANSACTION ISOLATION LEVEL].

Rolling back can be a slow operation that may occur implicitly without the user having explicitly asked for it (for example, when an error occurs). Because of this, [`SHOW PROCESSLIST`][SHOW PROCESSLIST] displays `Rolling back` in the `State`column for the session, not only for explicit rollbacks performed with the [`ROLLBACK`][ROLLBACK] statement but also for implicit rollbacks.

Note

In MySQL 5.5, `BEGIN`, `COMMIT`, and `ROLLBACK` are not affected by [`--replicate-do-db`][--replicate-do-db] or [`--replicate-ignore-db`][--replicate-ignore-db] rules.

[Previous][] /  [Next][] /  [Up][Previous] /  [Table of Contents][]

# User Comments #

注：近期参加MySQL运维学习，老师推荐该文章作为学习和技术提高的扩展阅读，先记录到自己的博客中，随后慢慢消化、学习、提高。本文章与“备份恢复”主题相关

[transactions]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_transaction
[autocommit]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_autocommit
[consistent read]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_consistent_read
[SELECT]: http://dev.mysql.com/doc/refman/5.5/en/select.html
[Section_14.5.4_ _Consistent Nonlocking Reads]: http://dev.mysql.com/doc/refman/5.5/en/innodb-consistent-read.html
[isolation level]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_isolation_level
[REPEATABLE READ]: http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html#isolevel_repeatable-read
[Chapter_23_Connectors and APIs]: http://dev.mysql.com/doc/refman/5.5/en/connectors-apis.html
[autocommit 1]: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_autocommit
[InnoDB]: http://dev.mysql.com/doc/refman/5.5/en/innodb-storage-engine.html
[NDBCLUSTER]: http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster.html
[Section_5.1.4_ _Server System Variables]: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
[BEGIN ... END]: http://dev.mysql.com/doc/refman/5.5/en/begin-end.html
[completion_type]: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_completion_type
[Section_13.3.3_ _Statements That Cause an Implicit Commit]: http://dev.mysql.com/doc/refman/5.5/en/implicit-commit.html
[LOCK TABLES]: http://dev.mysql.com/doc/refman/5.5/en/lock-tables.html
[FLUSH TABLES WITH READ LOCK]: http://dev.mysql.com/doc/refman/5.5/en/flush.html
[SERIALIZABLE]: http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html#isolevel_serializable
[SET TRANSACTION ISOLATION LEVEL]: http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html
[ROLLBACK]: http://dev.mysql.com/doc/refman/5.5/en/commit.html
[ER_WARNING_NOT_COMPLETE_ROLLBACK]: http://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html#error_er_warning_not_complete_rollback
[Section_5.2.4_ _The Binary Log]: http://dev.mysql.com/doc/refman/5.5/en/binary-log.html
[SHOW PROCESSLIST]: http://dev.mysql.com/doc/refman/5.5/en/show-processlist.html
[--replicate-do-db]: http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_replicate-do-db
[--replicate-ignore-db]: http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_replicate-ignore-db
[Previous]: http://dev.mysql.com/doc/refman/5.5/en/sql-syntax-transactions.html
[Next]: http://dev.mysql.com/doc/refman/5.5/en/cannot-roll-back.html
[Table of Contents]: http://dev.mysql.com/doc/refman/5.5/en/index.html