MySQL 5.0 Reference Manual  /  ...  /  How to Use Transactions in InnoDB with Different APIs

14.2.3.1 How to Use Transactions in InnoDB with Different APIs

By default, each client that connects to the MySQL server begins with autocommit mode enabled, which automatically commits every SQL statement as you execute it. To use multiple-statement transactions, you can switch autocommit off with the SQL statement SET autocommit = 0 and end each transaction with either COMMIT or ROLLBACK. If you want to leave autocommit on, you can begin your transactions within START TRANSACTION and end them with COMMIT or ROLLBACK. The following example shows two transactions. The first is committed; the second is rolled back.

shell> mysql test

mysql> CREATE TABLE customer (a INT, b CHAR (20), INDEX (a))
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO customer VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SET autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO customer VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM customer;
+------+--------+
| a    | b      |
+------+--------+
|   10 | Heikki |
+------+--------+
1 row in set (0.00 sec)
mysql>

In APIs such as PHP, Perl DBI, JDBC, ODBC, or the standard C call interface of MySQL, you can send transaction control statements such as COMMIT to the MySQL server as strings just like any other SQL statements such as SELECT or INSERT. Some APIs also offer separate special transaction commit and rollback functions or methods.


User Comments
  Posted by kumar mcmillan on May 20, 2005
unlike any other transactional database, MySQL does not rollback CREATE TABLE statements!! This is a major pain when trying to, say, re-run a script that loads a schema, but errors on foreign key constraints towards the end.
  Posted by Jon Coulter on July 22, 2005
The previous user mentioned that other database engines rollback DDL operations (like create table), and while this is true for MSSQL and PostgreSQL, it is not true for Oracle -- Oracle commits any existing transaction as soon as a DDL command is executed. Therefor, InnoDB tables are handled as Oracle would handle them, which as we can all agree, is a good example to follow :)
Sign Up Login You must be logged in to post a comment.