START TRANSACTION, COMMIT, and ROLLBACK Statements 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). 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. To disable autocommit mode explicitly, use the following statement: Important 2721