Table of Contents for

MySQL in a Nutshell, 2nd Edition

Use this statement to start a transaction. Transaction statements are currently supported by the InnoDB, NDB Cluster, and BDB storage engines and are ignored if used with MyISAM tables. The WORK keyword is optional. Don’t confuse the BEGIN statement with the BEGIN...END compound statement used in stored procedures and triggers (see Chapter 9). To eliminate confusion on this point, it’s recommended you use the alias START TRANSACTION instead of BEGIN.

A transaction is permanently recorded when the session issues a COMMIT statement, starts another transaction, or terminates the connection. You can reverse a transaction by issuing a ROLLBACK statement if the transaction has not yet been committed. See the explanations of COMMIT and ROLLBACK later in this chapter for more information on transactions. The SAVEPOINT and ROLLBACK TO SAVEPOINT statements may also be useful.

Here is an example of the BEGIN statement’s use in context:

BEGIN;

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

COMMIT;

In this example, if there is a problem after the batch of orders is inserted into the orders table, the ROLLBACK statement could be issued instead of the COMMIT statement shown here. ROLLBACK would remove the data imported by the INSERT DATA INFILE statement.