Start transaction, commit, and rollback syntax

Start transaction,COMMIT, AndROLLBACKSyntax

From: http://dev.mysql.com/doc/refman/5.0/en/commit.html

Of course, the article noted from MySQL, but MySQL, SQL Server, Oracle start tracmsaction, commit, and rollback syntax are no problem in DML.

I hope this article cocould help you understand how to using "Start transaction,COMMIT, AndROLLBACKSyntax ".

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}

The commit, and
Rollback Syntax "href =" http://dev.mysql.com/doc/refman/5.0/en/commit.html ">START TRANSACTIONOr commit, and
Rollback Syntax "href =" http://dev.mysql.com/doc/refman/5.0/en/commit.html ">BEGINStatement begins a new transaction. Commit, and
Rollback Syntax "href =" http://dev.mysql.com/doc/refman/5.0/en/commit.html ">COMMITCommits the current transaction, making its changes permanent. Commit, and
Rollback Syntax "href =" http://dev.mysql.com/doc/refman/5.0/en/commit.html ">ROLLBACKRolls back the current transaction, canceling its changes. The commit, and
Rollback Syntax "href =" http://dev.mysql.com/doc/refman/5.0/en/commit.html ">SET autocommitStatement disables or enables the default autocommit mode for the current session.

Beginning with MySQL 5.0.3, the optionalWORKKeyword is supported for commit, and
Rollback Syntax "href =" http://dev.mysql.com/doc/refman/5.0/en/commit.html ">COMMITAnd commit, and
Rollback Syntax "href =" http://dev.mysql.com/doc/refman/5.0/en/commit.html ">ROLLBACK, As areCHAINAndRELEASEClses.CHAINAndRELEASECan be used for additional control over Transaction completion. The value ofcompletion_typeSystem variable determines the default completion behavior. SeeSection 5.1.3, "server system variables".

TheAND CHAINClause 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.RELEASEClause causes the server to disconnect the current client session after terminating the current transaction. IncludingNOKeyword suppressesCHAINOrRELEASECompletion, which can be useful ifcompletion_typeSystem 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 to make it permanent. to disable autocommit mode, use the following statement:

SET autocommit=0;

After disabling autocommit mode by settingautocommitVariable to zero, changes to transaction-safe tables (such as thoseInnoDB,BDB, OrNDBCLUSTER) Are not made permanent immediately. You must use commit, and
Rollback Syntax "href =" http://dev.mysql.com/doc/refman/5.0/en/commit.html ">COMMITTo store your changes to disk or commit, and
Rollback Syntax "href =" http://dev.mysql.com/doc/refman/5.0/en/commit.html ">ROLLBACKTo ignore the changes.

To disable autocommit mode for a single series of statements, use the commit, and
Rollback Syntax "href =" http://dev.mysql.com/doc/refman/5.0/en/commit.html ">START TRANSACTIONStatement:

START TRANSACTION;SELECT @A:=SUM(salary) FROM table1 WHERE type=1;UPDATE table2 SET summary=@A WHERE type=1;COMMIT;

With commit, and
Rollback Syntax "href =" http://dev.mysql.com/doc/refman/5.0/en/commit.html ">START TRANSACTION, Autocommit remains disabled until you end the transaction with commit, and
Rollback Syntax "href =" http://dev.mysql.com/doc/refman/5.0/en/commit.html ">COMMITOr commit, and
Rollback Syntax "href =" http://dev.mysql.com/doc/refman/5.0/en/commit.html ">ROLLBACK. The autocommit mode then reverts to its previous state.

Commit, and
Rollback Syntax "href =" http://dev.mysql.com/doc/refman/5.0/en/commit.html ">BEGINAnd commit, and
Rollback Syntax "href =" http://dev.mysql.com/doc/refman/5.0/en/commit.html ">BEGIN WORKAre supported as aliases of commit, and
Rollback Syntax "href =" http://dev.mysql.com/doc/refman/5.0/en/commit.html ">START TRANSACTIONFor initiating a transaction. Commit, and
Rollback Syntax "href =" http://dev.mysql.com/doc/refman/5.0/en/commit.html ">START TRANSACTIONIs standard SQL syntax and is the recommended way to start an ad-hoc transaction.

Important

Extends APIs used for writing MYSQL client applications (such as JDBC) provide their own methods for starting transactions that can (and sometimes shocould) be used instead of sending a commit, and
Rollback Syntax "href =" http://dev.mysql.com/doc/refman/5.0/en/commit.html ">START TRANSACTIONStatement from the client. SeeChapter 20,Connectors and APIs, Or the documentation for your API, for more information.

The commit, and
Rollback Syntax "href =" http://dev.mysql.com/doc/refman/5.0/en/commit.html ">BEGINStatement differs from the use ofBEGINKeyword that startsBEGIN ... ENDCompound statement. The latter does not begin a transaction. SeeSection 12.8.1,"BEGIN ... ENDCompound statement syntax".

You can also begin a transaction like this:

START TRANSACTION WITH CONSISTENT SNAPSHOT;

TheWITH CONSISTENT SNAPSHOTClause starts a consistent read for storage engines that are capable of it. This applies onlyInnoDB. The effect is the same as issuing a commit, and
Rollback Syntax "href =" http://dev.mysql.com/doc/refman/5.0/en/commit.html ">START TRANSACTIONFollowed bySELECTFrom anyInnoDBTable. SeeSection 13.2.9.2, "consistent non-locking reads".WITH CONSISTENT SNAPSHOTClause 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 READOrSERIALIZABLE).

Beginning a transaction causes any pending transaction to be committed. SeeSection 12.4.3, "statements that cause an implicit commit", For more information.

Beginning a transaction also causes table locks acquired with unlock
Tables Syntax "href =" http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html ">LOCK TABLESTo be released, as though you had executed unlock
Tables Syntax "href =" http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html ">UNLOCK TABLES. Beginning a transaction does not release a global read lock acquiredFLUSH TABLES WITH READ LOCK.

For best results, transactions shocould be stored med 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 (suchInnoDBAndBDB), And the transaction isolation level is notSERIALIZABLE, 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 useSET TRANSACTION ISOLATION LEVELTo set the isolation levelSERIALIZABLEOn 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 commit, and
  Rollback Syntax "href =" http://dev.mysql.com/doc/refman/5.0/en/commit.html ">ROLLBACKStatement after updating a non-transactional table within a transaction,ER_WARNING_NOT_COMPLETE_ROLLBACKWarning 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, and
Rollback Syntax "href =" http://dev.mysql.com/doc/refman/5.0/en/commit.html ">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 between des modifications to non-transactional tables, the entire transaction is logged with a commit, and
Rollback Syntax "href =" http://dev.mysql.com/doc/refman/5.0/en/commit.html ">ROLLBACKStatement at the end to ensure that modifications to the non-transactional tables are replicated.) SeeSection 5.2.3, "the binary log".

You can change the isolation level for transactionsSET TRANSACTION ISOLATION LEVEL. SeeSection 12.4.6,"SET TRANSACTIONSyntax".

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 PROCESSLISTDisplaysRolling backInStateColumn for the session, not only for explicit rollbacks completed MED with the commit, and
Rollback Syntax "href =" http://dev.mysql.com/doc/refman/5.0/en/commit.html ">ROLLBACKStatement but also for implicit rollbacks.