Performing Transactions - MySQL - page 568

Discussion
This recipe describes the SQL statements that control transactional behavior in MySQL.
The immediately following recipes discuss how to perform transactions from within
programs. Some APIs require that you implement transactions by executing the SQL
statements discussed in this recipe; others provide a special mechanism that enables
transaction management without writing SQL directly. However, even in the latter case,
the API mechanism maps program operations onto transactional SQL statements, so
reading this recipe will give you a better understanding of what the API does on your
behalf.
MySQL normally operates in auto-commit mode, which commits the effect of each
statement as soon as it executes. (In effect, each statement is its own transaction.) To
perform a transaction, you must disable auto-commit mode, execute the statements that
make up the transaction, and then either commit or roll back your changes. In MySQL,
you can do this two ways:
• Execute a START TRANSACTION (or BEGIN ) statement to suspend auto-commit mode,
then execute the statements that make up the transaction. If the statements succeed,
record their effect in the database and terminate the transaction by executing a
COMMIT statement:
mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
mysql> START TRANSACTION;
mysql> INSERT INTO t (i) VALUES(1);
mysql> INSERT INTO t (i) VALUES(2);
mysql> COMMIT;
mysql> SELECT * FROM t;
+------+
| i |
+------+
| 1 |
| 2 |
+------+
If an error occurs, don't use COMMIT . Instead, cancel the transaction by executing a
ROLLBACK statement. In the following example, t remains empty after the transac‐
tion because the effects of the INSERT statements are rolled back:
mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
mysql> START TRANSACTION;
mysql> INSERT INTO t (i) VALUES(1);
mysql> INSERT INTO t (x) VALUES(2);
ERROR 1054 (42S22): Unknown column 'x' in 'field list'
mysql> ROLLBACK;
mysql> SELECT * FROM t;
Empty set (0.00 sec)
• Another way to group statements is to turn off auto-commit mode explicitly by
setting the autocommit session variable to 0. After that, each statement you execute