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 | The 1}start transaction or BEGIN statement can begin a new transaction. A commit can commit the current transaction, and the change becomes a permanent change. Rollback can roll back the current transaction and cancel its change. The SET AUTOCOMMIT statement can disable or enable the default autocommit mode for the current connection.

Optional work keywords are supported for commit and release, with chain and release clauses. Chain and release can be used to attach control to the completion of a transaction. 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 starts a new transaction immediately at the end of the current transaction, and the new transaction has the same isolation level as the transaction just ended. The release clause will cause the server to disconnect from the current client after the current transaction has been terminated. Containing no keywords can inhibit chain or release completion. If the Completion_type system variable is set to a certain value, the chain or release completion can be done by default, at which point no keyword is useful.

By default, the MySQL tutorial runs in autocommit mode. This means that when you execute a statement that updates (modifies) a table, MySQL stores the update to disk immediately.

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

Set autocommit=0; After you disable the autocommit mode by setting the autocommit variable to zero, you must use commit to save the changes to disk, or you can use rollback if you want to ignore the changes made since the start of the transaction.

If you want to disable 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; using the star The T Transaction,autocommit is still disabled until you use commit or rollback to end the transaction. The autocommit mode is then restored to its original state.

The begin and begin work are supported for initializing transactions as the alias for start transaction. The start transaction is a standard SQL syntax and is the recommended way to start a ad-hoc transaction. The BEGIN statement differs from the use of the BEGIN keyword. Begin keyword to start a begin...end compound statement. The latter does not begin a transaction. See section 20.2.7, "Begin with ... end Compound statement".

You can also start a transaction in the following ways:

The start transaction with consistent snaps tutorial hot;with consistent clause is used to initiate a consistent read for storage engines with such functionality. Currently, this clause applies only to InnoDB. The effect of the clause is the same as that of the publication of a start transaction, followed by a select from any InnoDB table. See section 15.2.10.4, "consistent, non-locked reads."

Starting a transaction causes an implied unlock tables to be executed.

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

· If you are using tables 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 commits, other in-progress transactions that use the same table will only occur changes that are produced by the first transaction. That is, using a hybrid engine does not guarantee the atomicity of the transaction and can cause inconsistencies. (If mixed engine transactions are not often available, you can set the isolation level to serializable as needed using the SET transaction isolation levels.) ）

· If you use a non-transactional security table in a transaction, any changes to those tables are immediately stored, regardless of the state of the autocommit mode.

If you publish a ROLLBACK statement after updating one of the transaction tables in the transaction, a er_warning_not_complete_rollback warning appears. Changes to the Transaction security table are rolled back, but there are no changes to the non-transactional security table.

Each transaction is stored in a binary log in a group block, above the commit. Transactions that are rolled back are not counted into the log. (Exception: Changes to a non-transactional table are not rolled back.) If a transaction that is rolled back includes changes to a non-transactional table, the entire transaction is counted into the log with a rollback statement at the end to ensure that changes to those tables are replicated. See section 5.11.3, "binary log".

You can change the isolation level of a transaction by using the SET TRANSACTION isolation levels. See section 13.4.6, "SET transaction Syntax".

Rollback can run slowly. A rollback can also be made when the user does not explicitly require it (for example, when an error occurs). Therefore, when you rollback explicitly and implicitly (Rollback SQL commands), show processlist displays rolling back in the stage column for the connection.

13.4.2. Statements that cannot be rolled back
Some statements cannot be rolled back. Typically, these statements include data definition language (DDL) statements, such as statements that create or cancel a database tutorial, and statements that create, cancel, or change a table or stored subroutine.

You should not include such statements when you design a transaction. If you publish a statement in the front of the transaction that cannot be rolled back, an error occurs in the other statements at the back, in which case the full effect of the transaction cannot be rolled back by publishing the rollback statement.

13.4.3. Statements that would result in an implicit commit
The following statements (and synonyms) implicitly end a transaction, and it appears that you have made a commit before executing this statement.

· alter function, ALTER PROCEDURE, ALTER TABLE, begin, CREATE DATABASE, create function, create INDEX, create PROCEDURE, CRE Ate table, drop database, drop function, DROP index, drop procedure, drop table, load master data, lock tables, rename tab Le, set autocommit=1, start transaction, TRUNCATE TABLE, unlock tables.

· Unlock tables can commit transactions when all of the current tables are locked.

· CREATE TABLE, create database drop database, TRUNCATE TABLE, ALTER function, ALTER PROCEDURE, create function, create proc Edure, a statement such as drop function and drop procedure causes an implied commit.

· The CREATE TABLE statement in InnoDB is handled as a single transaction. This means that rollback from the user does not undo the CREATE TABLE statement that the user created during the transaction process.

Transactions cannot be nested. This is the result of an implied commit. When you publish a start transaction statement or its synonyms, 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 Support SQL statements savepoint, Rollback to SavePoint, release savepoint and optional work keywords for rollback.

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

The rollback to savepoint statement rolls back a transaction to a named SavePoint. If the current transaction makes changes to the row after the savepoint is set, the changes are undone in the rollback. However, InnoDB does not release row locks that are stored in the storage after the savepoint. (Note that for newly inserted rows, the lock information is stored in the transaction ID of the row, and the locks are not stored separately in the memory.) In this case, the row lock is freed in the undo. The save point set after the named SavePoint is deleted.

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

The error 1181:got error 153 during Rollbackrelease savepoint statement deletes the named SavePoint from a set of save points in the current transaction. No commit or rollback occurs. If the save point does not exist, an error occurs.

If you perform a commit or execute a rollback that cannot name the savepoint, all the savepoint for 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 be locked for the current thread. If the table is locked by another thread, it is blocked until all locks can be obtained. Unlock tables can release any locks that are held by the current thread. When the thread publishes another lock table, or when the connection to the server is closed, all the tables locked by the current thread are implicitly unlocked.

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

Note that the following is a description of the use of lock tables for the transaction table:

· Lock tables are not transaction-safe until you attempt to lock the table, and implicitly commit all active transactions. At the same time, starting a transaction (for example, using the start transaction) implicitly executes unlock tables. (see section 13.4.3, "statements that would result in an implicit submission".)

· The correct way to use lock tables for a transaction table (such as InnoDB) is to set up autocommit=0 and not call unlock tables until you explicitly commit the transaction. When you call lock tables, InnoDB internally takes its own table lock, and MySQL takes its own table lock. InnoDB releases its table lock at the next commit, but for MySQL, to release the table lock, you must call unlock tables. You should not let autocommit=1, because in that case, InnoDB will release the table lock immediately after calling lock tables, and it is easy to form a deadlock. Note that if autocommit=1, we simply cannot get a InnoDB table lock, which will help the old application to avoid unnecessary deadlock.

· Rollback does not release MySQL's non-transactional table locks.

To use lock tables, you must have lock tables permissions and select permissions for the related tables.

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

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

The difference between read local and read is that read local allows you to execute a non conflicting INSERT statement (insert at the same time) when the lock is persisted. However, if you are trying to manipulate the database files outside of MySQL and you remain locked, you cannot use read Local. For the InnoDB table, read local is the same as read.

When you use lock tables, you must lock all the tables that you intend to use in the query. Although locks obtained using the lock tables statement are still valid, you cannot access any tables that are not locked by this statement. Also, you cannot use a locked table more than once in a query-using aliases instead, in which case you have to get a lock on each alias separately.

mysql> Lock Table T write, t as T1 write;mysql> insert INTO t select * from T;error 1100:table ' t ' is not locked W ITH lock tablesmysql> INSERT INTO t select * from t as T1; If your query uses an alias to refer to a table, you must lock the table with the same alias. 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 ' is not locked with lock tabl Es Conversely, if you use an alias to lock a table, you must use that alias to refer to the table in your query.

mysql> lock table T as Myalias read;mysql> select * from T;error 1100:table ' t ' is not locked with lock tablesmysq L> SELECT * from t as Myalias;write locks usually have higher precedence than read locks to ensure that updates are processed as quickly as possible. This means that if one thread gets a read lock, another thread requests a write lock, and subsequent read lock requests wait until the write thread gets locked and releases the lock. You can use the Low_priority write lock to allow other threads to obtain a read lock when the thread is waiting for a write lock. You should use the Low_priority write lock only if you are sure that there will be a time when no threads have a read lock.

Lock tables are executed as follows:

1. Classify all tables to be locked in an internally defined order. From the user's perspective, this order is undefined.

2. If a table is locked with a read and a write lock, the write lock is placed before the read lock.

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

This rule ensures that table locking does not appear dead locked. However, for this rule, you need to be aware of 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 the read-locked thread is not requested. When a thread has obtained a write lock and is waiting for a lock on the next table in the locked table manifest, all other threads will wait for the write lock to be freed. If this becomes a serious problem for your application, you should consider converting some of the tables into transaction-safe tables.

You can safely use Kill to end a thread that is waiting for a table to lock. See section 13.5.5.3, "kill syntax."

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

Typically, you do not need to lock the table because all individual update statements are atomic, and no other thread can interfere with any other currently executing SQL statement. However, in several cases, locking a table can be beneficial:

· If you're running a lot of operations on a set of MyISAM tables, it's much faster to lock down the tables you're using. Locking the MyISAM table speeds the insertion, update, or deletion. The downside is that no thread can update a table locked with read (including a locked table), and no thread can access a table locked with write (except the table that remains locked).

Some MyISAM operations are faster under lock tables because MySQL does not empty critical caches for locked tables until unlock table is invoked. Typically, the critical cache is emptied 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 the select and update. The examples shown here require 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_previous_statement                     ->      where customer_id=some_id;                 mysql> Unlock If there is no lock tables, it is possible that another thread inserts a new row in the trans table between the Execute SELECT and UPDATE statements