About MySQL Transaction Processing

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 or begin statement can start a new transaction. Commit can commit the current transaction to make the change permanent. Rollback can roll back the current transaction and cancel its change. The Set autocommit statement can be used to disable or enable the default autocommit mode for the current connection.

The optional work keywords are supported for commit and release, and the chain and release clauses. Chain and release can be used to perform additional control on Transaction completion. The value of the completion_type system variable determines the nature of the default completion.

The and chain clause immediately starts a new transaction at the end of the current transaction, and the new transaction has the same isolation level as the just-concluded transaction. After the release clause terminates the current transaction, it will disconnect the server from the current client. Keywords containing no can suppress chain or release. If the completion_type system variable is set to a certain value, the chain or release can be completed by default. In this case, the No keyword is useful.

By default, MySQL runs in autocommit mode. This means that, after executing a statement for updating (modifying) The table, MySQL immediately stores the update to the disk.

If you are using a transaction-safe storage engine (such as InnoDB, bdb, or NDB cluster), you can disable the autocommit mode using the following statement:

Set autocommit = 0;

By setting the autocommit variable to zero, after the autocommit mode is disabled, you must use commit to store the changes to the disk, or if you want to ignore the changes made since the beginning of the transaction, use rollback.

To disable the autocommit mode for a single series of statements, you can 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 is still disabled until the transaction ends with commit or rollback. Then, the autocommit mode is restored to the original state.

Begin and begin work are supported as aliases of start transaction and used to initialize transactions. Start transaction is a standard SQL syntax and a recommended method for starting an ad-hoc transaction. The use of the begin statement is different from that of the begin keyword. The begin keyword can start a begin... end compound statement. The latter will not start a transaction.

You can also start a transaction as follows:

Start transaction with consistent snapshot;

The with consistent snapshot clause is used to start a consistent read for a storage engine with such features. Currently, this clause only applies to InnoDB. The effect of this clause is the same as that of publishing a start transaction statement, followed by a select statement from any InnoDB table.

Starting a transaction will cause an implicit unlock tables to be executed.

To obtain the best results, transactions should only be executed using tables managed by a single transaction storage engine. Otherwise, the following problems may occur:

If the tables used come from multiple transaction-safe storage engines (such as InnoDB and bdb) and the transaction isolation level is not serializable, it is possible that when a transaction is committed, other ongoing transactions that use the same table will only be changed by the first transaction. That is, the use of hybrid engines cannot guarantee the atomicity of transactions and may cause inconsistency. (If hybrid engine transactions are uncommon, you can use SET transaction isolation level to set the isolation level to serializable as needed .)

If a non-transactional Security table is used in a transaction, any changes to these tables are immediately stored, regardless of the status of the autocommit mode.

If a rollback statement is published after a transaction table is updated, an er_warning_not_complete_rollback warning is displayed. Changes to the transaction security table are rolled back, but no changes to the non-Transaction Security table.

Each transaction is stored in a binary log in a group block, on the commit. Rollback transactions are not included in logs. (Exception: Changes to non-transaction tables are not rolled back. If a rolled back transaction includes changes to non-transaction tables, the entire transaction uses a rollback statement at the end of the log to ensure that the changes to these tables are replicated .)

You can use SET transaction isolation level to change the transaction isolation level.

Rollback can run slowly. You can also perform rollback (for example, when an error occurs) If you do not have specific requirements ). Therefore, when explicitly rolling back to an implicit (rollback SQL command), show processlist will display rolling back in the stage column for connection.