DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(mysql.info.gz) COMMIT

Info Catalog (mysql.info.gz) Transactional Commands (mysql.info.gz) Transactional Commands (mysql.info.gz) Cannot roll back
 
 13.4.1 `START TRANSACTION', `COMMIT', and `ROLLBACK' Syntax
 -----------------------------------------------------------
 
 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.
 
 If you are using transaction-safe tables (like `InnoDB' or `BDB'), you
 can disable autocommit mode with the following statement:
 
      SET AUTOCOMMIT=0;
 
 After disabling autocommit mode by setting the `AUTOCOMMIT' variable to
 zero, you must use `COMMIT' to store your changes to disk or `ROLLBACK'
 if you want to ignore the changes you have made since the beginning of
 your 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;
 
 With `START TRANSACTION', autocommit remains disabled until you end the
 transaction with `COMMIT' or `ROLLBACK'. The autocommit mode then
 reverts to its previous state.
 
 `BEGIN' and `BEGIN WORK' can be used instead of `START TRANSACTION' to
 initiate a transaction.  `START TRANSACTION' was added in MySQL 4.0.11.
 This is standard SQL syntax and is the recommended way to start an
 ad-hoc transaction.  `BEGIN' and `BEGIN WORK' are available from MySQL
 3.23.17 and 3.23.19, respectively.
 
 As of MySQL 4.1.8, you can begin a transaction like this:
 
      START TRANSACTION WITH CONSISTENT SNAPSHOT;
 
 The `WITH CONSISTENT SNAPSHOT' clause starts a consistent read for
 storage engines that are capable of it.  Currently, this applies only
 to `InnoDB'.  The effect is the same as issuing a `START TRANSACTION'
 followed by a `SELECT' from any `InnoDB' table.   InnoDB
 consistent read.
 
 Beginning a transaction causes an implicit `UNLOCK TABLES' to be
 performed.
 
 Note that if you are not using transaction-safe tables, any changes are
 stored at once, regardless of the status of autocommit mode.
 
 If you issue a `ROLLBACK' statement after updating a non-transactional
 table within a transaction, an `ER_WARNING_NOT_COMPLETE_ROLLBACK'
 warning occurs.  Changes to transaction-safe tables will be rolled
 back, but not changes to non-transaction-safe tables.
 
 If you are using `START TRANSACTION' or `SET AUTOCOMMIT=0', you should
 use the MySQL binary log for backups instead of the older update log.
 Transactions are stored in the binary log in one chunk, upon `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 includes modifications to
 non-transactional tables, the entire transaction is logged with a
 `ROLLBACK' statement at the end to ensure that the modifications to
 those tables are replicated. This is true as of MySQL 4.0.15.)  
 Binary log.
 
 You can change the isolation level for transactions with `SET
 TRANSACTION ISOLATION LEVEL'.   `SET TRANSACTION' SET TRANSACTION.
 
 Rolling back can be a slow operation that can occur without the user
 having explicitly asked for it (for example, when an error occurs).
 Because of this, `SHOW PROCESSLIST' will display `Rolling back' in the
 `State' column for the connection during implicit rollback and explicit
 (`ROLLBACK' SQL command) rollbacks, starting from MySQL 4.1.8.
 
Info Catalog (mysql.info.gz) Transactional Commands (mysql.info.gz) Transactional Commands (mysql.info.gz) Cannot roll back
automatically generated byinfo2html