14.8.2.2 autocommit, Commit, and Rollback

In InnoDB, all user activity occurs inside a
transaction. If autocommit mode
is enabled, each SQL statement forms a single transaction on its
own. By default, MySQL starts the session for each new
connection with autocommit
enabled, so MySQL does a commit after each SQL statement if that
statement did not return an error. If a statement returns an
error, the commit or rollback behavior depends on the error. See
Section 14.23.4, “InnoDB Error Handling”.

A COMMIT means that the changes
made in the current transaction are made permanent and become
visible to other sessions. A
ROLLBACK
statement, on the other hand, cancels all modifications made by
the current transaction. Both
COMMIT and
ROLLBACK
release all InnoDB locks that were set during
the current transaction.

Grouping DML Operations with Transactions

By default, connection to the MySQL server begins with
autocommit mode
enabled, which automatically commits every SQL statement as
you execute it. This mode of operation might be unfamiliar if
you have experience with other database systems, where it is
standard practice to issue a sequence of
DML statements and commit them
or roll them back all together.

To use multiple-statement
transactions, switch
autocommit off with the SQL statement SET autocommit
= 0 and end each transaction with
COMMIT or
ROLLBACK as
appropriate. To leave autocommit on, begin each transaction
with START
TRANSACTION and end it with
COMMIT or
ROLLBACK.
The following example shows two transactions. The first is
committed; the second is rolled back.

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.