content_image/857645.jpg
You cannot use ROLLBACK  to undo the effect; however, if an error occurs during statement execution, the statement is rolled back. 

To disable autocommit mode implicitly for a single series of statements, 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. 

START TRANSACTION  permits several modifiers that control transaction characteristics. To specify multiple modifiers, separate them by commas. 

•The WITH CONSISTENT SNAPSHOT  modifier starts a consistent read for storage engines that are capable of it. This applies only to InnoDB . The effect is the same as issuing a START TRANSACTION followed by a SELECT  from any InnoDB  table. See Section 15.7.2.3, “Consistent Nonlocking Reads” . The WITH CONSISTENT SNAPSHOT  modifier does not change the current transaction isolation level, so it provides a consistent snapshot only if the current isolation level is one that permits a consistent read. The only isolation level that permits a consistent read is REPEATABLE READ . For all other isolation levels, the WITH CONSISTENT SNAPSHOT  clause is ignored. A warning is generated when the WITH CONSISTENT SNAPSHOT  clause is ignored. 

•The READ WRITE  and READ ONLY  modifiers set the transaction access mode. They permit or prohibit changes to tables used in the transaction. The READ ONLY  restriction prevents the transaction from modifying or locking both transactional and nontransactional tables that are visible to other transactions; the transaction can still modify or lock temporary tables. 

MySQL enables extra optimizations for queries on InnoDB  tables when the transaction is known to be read-only. Specifying READ ONLY  ensures these optimizations are applied in cases where the read- only status cannot be determined automatically. See Section 8.5.3, “Optimizing InnoDB Read-Only Transactions”  for more information. 

If no access mode is specified, the default mode applies. Unless the default has been changed, it is read/write. It is not permitted to specify both READ WRITE  and READ ONLY  in the same statement. 

In read-only mode, it remains possible to change tables created with the TEMPORARY  keyword using DML statements. Changes made with DDL statements are not permitted, just as with permanent tables. 

For additional information about transaction access mode, including ways to change the default mode, see Section 13.3.7, “SET TRANSACTION Statement” . 

If the read_only  system variable is enabled, explicitly starting a transaction with START TRANSACTION READ WRITE  requires the CONNECTION_ADMIN  privilege (or the deprecated SUPER  privilege). 

<img src='content_image/857645.jpg'>

To disable autocommit mode explicitly, use the following statement: 

## Important 

Many APIs used for writing MySQL client applications (such as JDBC) provide their own methods for starting transactions that can (and sometimes should) be used instead of sending a START TRANSACTION  statement from the client. See Chapter 29, Connectors and APIs , or the documentation for your API, for more information. 

