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
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.
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 :)
Add your own comment.