6.7.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax

By default, MySQL runs in autocommit mode. This means that as soon as you execute a statement that updates (modifies) a table, MySQL will store the update on disk.

If you are using transaction-safe tables (like InnoDB or BDB), you can put MySQL into non-autocommit mode with the following command:

SET AUTOCOMMIT=0

After disabling autocommit mode by setting the AUTOCOMMIT variable to zero, you must use COMMIT to store your changes to disk or ROLLBACK if you want to ignore the changes you have made since the beginning of your transaction.

If you want to disable autocommit mode for a single series of statements, you can use the START TRANSACTION statement: :

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summmary=@A WHERE type=1;
COMMIT;

BEGIN and BEGIN WORK can be used instead of START TRANSACTION to initiate a transaction. START TRANSACTION was added to MySQL 4.0.11; it is SQL-99 syntax and is the recommended way to start an ad-hoc transaction. BEGIN and BEGIN WORK are available from MySQL 3.23.17 and 3.23.19, respectively.

Note that if you are not using transaction-safe tables, any changes will be stored at once, regardless of the status of autocommit mode.

If you issue a ROLLBACK statement after updating a non-transactional table, you will get an error (ER_WARNING_NOT_COMPLETE_ROLLBACK) as a warning. All transaction-safe tables will be restored but any non-transaction-safe table will not change.

If you are using START TRANSACTION or SET AUTOCOMMIT=0, you should use the MySQL binary log for backups instead of the older update log. Transactions are stored in the binary log in one chunk, upon COMMIT, to ensure that transactions that are rolled back are not stored. See section 4.9.4 The Binary Log.

The following commands implicitly end a transaction (as if you had done a COMMIT before executing the command):

UNLOCK TABLES also ends a transaction if any tables currently are locked.

Transactions cannot be nested. This is a consequence of the implicit COMMIT performed for any current transaction when you issue a START TRANSACTION statement or one of its synonyms.

You can change the isolation level for transactions with SET TRANSACTION ISOLATION LEVEL .... See section 6.7.4 SET TRANSACTION Syntax.

User Comments

If you start a transaction with BEGIN, and it gets
interrupted unexpectedly (so a COMMIT is never
issued), shouldn't the default behavior be to
ROLLBACK when another major command is issued?
I would think that the whole point of transactions is
to make a process fail-safe. The current method
requires an explicit ROLLBACK, which seems to
defeat the purpose of transactions. If you start a
transaction with BEGIN, but it still auto-commits if
something goes wrong, how is that a transaction at
all? Or am I missing something huge and obvious?

In my experience, it seens that creating tables inside a BEGIN-COMMIT block won't automagically be rolled back if you have an error somewhere (either in your Syntax or on the database side).

Add your own comment.