MySQL in a Nutshell

Name

COMMIT

Synopsis

COMMIT

Use this statement to commit transactions in an InnoDB or a BDB table. If AUTOCOMMIT is enabled, it must be disabled for this statement to be meaningful. To do this, set the value of AUTOCOMMIT to 0 with the SET statement. AUTOCOMMIT will also be disabled with the use of the START TRANSACTION statement and reinstated with the COMMIT statement. Here is an example of this statement:

START TRANSACTION;
LOCK TABLES orders WRITE;
INSERT DATA INFILE '/tmp/customer_orders.sql'
  INTO TABLE orders;
SELECT ...;
COMMIT;
UNLOCK TABLES;

In this example, after inserting a batch of orders into the orders table, an administrator enters a series of SELECT statements to check the integrity of the data. They are omitted here to save space. If there is a problem, the ROLLBACK statement could be issued rather than the COMMIT statement shown here. ROLLBACK would remove the data imported by the INSERT DATA INFILE statement. The ROLLBACK statement works only with InnoDB and BDB tables. If everything seems alright, the COMMIT statement would be issued to commit the transactions.