Start transaction, COMMIT, and ROLLBACK syntax

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} start transaction or begin statement can start a new transaction. Commit can commit the current transaction, which is a permanent change of a change. 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. See section 5.3.3 "server system variables ".

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, the mysql tutorial runs in autocommit mode. This means that after you execute 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. After the autocommit variable is set to zero and 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.

If you want 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; use start transaction, autocommit is still disabled until you end the transaction 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. See section 2.7 "in... end compound statement ".

You can also start a transaction as follows:

Start transaction with consistent snaps tutorial hot; The with consistent snapshot clause is used to start a consistent read for storage engines 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. See section 15.2.10.4, "consistent non-locked read ".

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 table you are using comes from multiple transaction security 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 you use a non-transactional Security table in a transaction, any changes to these tables are immediately stored, regardless of the status of the autocommit mode.

If you publish a rollback statement after updating a transaction table, an er_warning_not_complete_rollback warning will appear. 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 .) See section 5.11.3 "binary log ".

You can use set transaction isolation level to change the transaction isolation level. See section 13.4.6 "set transaction Syntax ".

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.

13.4.2. statements that cannot be rolled back
Some statements cannot be rolled back. Generally, these statements include Data Definition Language (ddl) statements, such as statements used to create or cancel a database tutorial, and statements used to create, cancel, or change a table or a stored subroutine.

You should not include such statements when designing transactions. If you publish a statement in front of the transaction that cannot be rolled back, other statements at the end of the transaction will encounter errors. In these cases, you cannot roll back the full effect of a transaction by publishing a rollback statement.

13.4.3. statements that are implicitly submitted
The following statements (and synonyms) implicitly end a transaction. It seems that you have performed a commit before executing this statement.

· Alter function, alter procedure, alter table, begin, create database, create function, create index, create procedure, create table, drop database, drop function, drop index, drop procedure, drop table, load master data, lock tables, rename table, set autocommit = 1, start transaction, truncate table, unlock tables.

· When all previous tables are locked, unlock tables can commit transactions.

· Create table, create database drop database, truncate table, alter function, alter procedure, create function, create procedure, drop function, drop procedure, and other statements may cause an implicit commit.

· The create table statement in innodb is processed as a single transaction. This means that the rollback from the user will not cancel the create table statement created during the transaction processing.

Transactions cannot be nested. This is the result of implicit commit. When you publish a start transaction statement or its synonym, The commit is executed for any current transaction.

13.4.4. savepoint and rollback to savepoint syntax
Savepoint identifierrollback [work] to savepoint identifierrelease savepoint identifierinnodb supports SQL statements savepoint, rollback to savepoint, release savepoint, and work keywords for rollback.

The savepoint statement is used to set a transaction retention point with an identifier name. If the current transaction has a save point with the same name, the old save point is deleted and the new save point is set.

The rollback to savepoint statement rolls back a transaction to the named save point. If the current transaction changes the row after the save point is set, the changes will be undone in the rollback. However, innodb will not release the row that is stored in the storage after the storage point is locked. (Note: For newly inserted rows, the lock information is carried by the transaction id in the row; the lock is not separately stored in the memory. In this case, the row lock is released in the undo state .) The Save point set after the named save point is deleted.

If the statement returns the following error, it means that there is no save point with the specified name:

Error 1181: got error 153 during rollbackrelease savepoint statement deletes the named save point from a set of save points of the current transaction. Do not submit or roll back. If the save point does not exist, an error occurs.

If you execute a commit statement or execute a rollback statement that cannot be named as the storage point, all the storage points of the current transaction are deleted.

13.4.5. lock tables and unlock tables syntax
Lock tables tbl_name [as alias] {read [local] | [low_priority] write} [, tbl_name [as alias] {read [local] | [low_priority] write}]... unlock tableslock tables can lock the table used for the current thread. If the table is locked by other threads, blocking occurs until all the locks can be obtained. Unlock tables can release any lock maintained by the current thread. When the thread publishes another lock tables, or when the connection to the server is closed, all tables locked by the current thread are implicitly unlocked.

Table locking is only used to prevent other clients from reading and writing data improperly. Clients that keep locked (even read locked) can perform surface-level operations, such as drop table.

Note: The following describes how to use lock tables for the transaction table:

· Before trying to lock the table, lock tables is not transaction-safe and will implicitly commit all active transactions. At the same time, starting a transaction (for example, using start transaction) will implicitly execute unlock tables. (See section 13.4.3, "statements that may be implicitly submitted ".

· The correct way to use lock tables for transaction tables (such as innodb) is to set autocommit = 0 and cannot call unlock tables until you explicitly commit the transaction. When you call lock tables, innodb internally locks its own table, and mysql locks its own table. Innodb releases its table lock when the next commit. However, for mysql, to release the table lock, you must call unlock tables. You should not make autocommit = 1, because in that case, innodb will immediately release the table lock after calling lock tables, and it is easy to form an infinite lock. NOTE: If autocommit = 1, we cannot get the innodb table lock at all, which can help the old application software avoid unnecessary locking.

· Rollback will not release non-transaction table locks for mysql.

To use lock tables, you must have the lock tables permission and select permission for the relevant table.

The main reason for using lock tables is to emulate a transaction or speed up table update. This will be explained in more detail later.

If a thread gets a read lock on a table, the thread (and all other threads) can only read from the table. If a thread gets a write lock on a table, only the locked thread can write data to the table. Other threads are blocked until the lock is released.

The difference between read local and read is that read local allows non-conflicting insert statements (insert at the same time) to be executed when the lock is kept ). However, if you are planning to operate on database files outside mysql while you are still locked, you cannot use read local. For innodb tables, read local is the same as read.

When you use lock tables, you must lock all tables that you intend to use in the query. Although the lock obtained by using the lock tables statement is still valid, you cannot access any tables not locked by this statement. At the same time, you cannot use a locked table multiple times in a query-replace it with an alias. In this case, you must obtain the lock for each alias.

Mysql> lock table t write, t as t1 write; mysql> insert into t select * from t; error 1100: table 't'was not locked with lock tablesmysql> insert into t select * from t as t1; if your query uses an alias to reference a table, you must use the same alias to lock the table. If no alias is specified, the table is not locked.

Mysql> lock table t read; mysql> select * from t as myalias; error 1100: table 'myalias' was not locked with lock tables, if you use an alias to lock a table, you must use this alias to reference the table in your query.

Mysql> lock table t as myalias read; mysql> select * from t; error 1100: table 't'was not locked with lock tablesmysql> select * from t as myalias; write locking usually has a higher priority than read locking to ensure that updates are processed as soon as possible. This means that if a thread gets a read lock, the other thread will apply for a write lock, and the subsequent read lock application will wait until the write thread gets the lock and releases the lock. You can use low_priority write lock to allow other threads to get the read lock when the thread is waiting for the write lock. You should use low_priority write to lock a thread only when you are sure that there is no read lock for the thread.

Lock tables:

1. Sort all the tables to be locked in the order defined internally. From the user's point of view, this order is undefined.

2. If you use one read and one write lock to lock a table, place the write lock before the read lock.

3. Lock a table at a time until the thread gets all the locks.

This rule ensures that the table will not be locked. However, for this rule, you need to pay attention to other things:

If you are using a low_priority write lock on a table, this only means that mysql waits for a specific lock until no thread applied for read lock is applied. When the thread has obtained the write lock and is waiting for the lock for the next table in the locked table list, all other threads will wait for the write lock to be released. If this is a serious problem for applications, you should consider converting some tables into transaction-safe tables.

You can safely use kill to end a thread waiting for table locking. See section 13.5.5.3 "kill Syntax ".

Note that you cannot use insert delayed to lock any tables you are using, because in this case, insert is executed by another thread.

Generally, you do not need to lock the table because all single update statements are atomic. No other thread can interfere with any other SQL statements currently being executed. However, locking a table has the following benefits:

· If you are running many operations on a group of myisam tables, locking the tables you are using can be much faster. Locking a myisam table can speed up insertion, update, or deletion. The disadvantage is that no thread can update a table locked with read (including tables locked ), no thread can access the table locked by the write statement (except for the table locked ).

Some myisam operations are faster under lock tables because mysql does not clear the key cache used for locked tables until the unlock table is called. Generally, the key cache is cleared after each SQL statement.

· If you are using a storage engine in mysql that does not support transactions, you must use lock tables if you want to determine that there are no other threads between select and update. The example shown in this section requires lock tables for safe execution:

· Mysql> lock tables trans read, customer write; · mysql> select sum (value) from trans where customer_id = some_id; · mysql> update customer-> set total_value = sum_from_previus_statement-> where customer_id = some_id; · mysql> unlock tables; if no table lock is available, another thread may Insert a new row in the trans table between the select and update statements.