MySQL in a Nutshell

Name

START TRANSACTION

Synopsis

START TRANSACTION

Use this statement to start a transaction, or a set of SQL statements for an InnoDB or a BDB table. Transaction statements are ignored when you use them with MyISAM tables. The purpose of a transaction is to be able to undo SQL statements if need be. You can reverse a transaction if the transaction has not yet been committed either with a COMMIT statement, implicitly by starting another transaction, or by terminating the connection. In earlier versions of MySQL, BEGIN or BEGIN WORK were used instead of START TRANSACTION. See the explanation of the COMMIT statement and the ROLLBACK statement for more information on transactions. The SAVEPOINT statement and the ROLLBACK TO SAVEPOINT statements may also be useful.

Here is an example of this statement’s use:

START TRANSACTION;
INSERT DATA INFILE '/tmp/customer_orders.sql'
  INTO TABLE orders;
COMMIT;

In this example, after the batch of orders was inserted into the orders table, if there was a problem the ROLLBACK statement could be issued instead of the COMMIT statement shown here. It 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 entries and to end the transaction started with the START TRANSACTION statement.