GNU Info

(mysql.info)COMMIT

---

Next: LOCK TABLES Prev: Transactional Commands Up: Transactional Commands

---

Enter node , (file) or (file)node

`BEGIN/COMMIT/ROLLBACK' Syntax
------------------------------

By default, MySQL runs in `autocommit' mode. This means that as soon as
you execute an update, MySQL will store the update on disk.

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

     SET AUTOCOMMIT=0

After this 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 switch from `AUTOCOMMIT' mode for one series of
statements, you can use the `BEGIN' or `BEGIN WORK' statement:

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

Note that if you are using non-transaction-safe tables, the changes
will be stored at once, independent of the status of the `autocommit'
mode.

If you do a `ROLLBACK' when you have updated a non-transactional table
you will get an error (`ER_WARNING_NOT_COMPLETE_ROLLBACK') as a
warning.  All transactional safe tables will be restored but any
non-transactional table will not change.

If you are using `BEGIN' 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 which are rolled back are not stored.
Note: Binary log.

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

`ALTER TABLE'            `BEGIN'                  `CREATE INDEX'
`DROP DATABASE'          `DROP TABLE'             `RENAME TABLE'
`TRUNCATE'                                        

You can change the isolation level for transactions with `SET
TRANSACTION ISOLATION LEVEL ...'. Note: SET TRANSACTION.