Transactions in SQL
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>
Transactions in Client-Side Languages
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.