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 purpose of a transaction is to be able to undo SQL statements if need be. You can reverse a transaction if you have not yet committed it 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 explanations of the COMMIT and ROLLBACK statements earlier in this chapter for more information on transactions. The SAVEPOINT statement and the ROLLBACK TO SAVEPOINT statement may also be useful.

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

START TRANSACTION;

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

COMMIT;

In this example, after the batch of orders is inserted into the orders table, the user decides everything went properly and issues the COMMIT statement to actually enter the data in the database and to end the transaction started with the START TRANSACTION statement. If there had been a problem, the ROLLBACK statement could be issued instead of COMMIT. ROLLBACK would remove the data imported by the INSERT DATA INFILE statement.

The WITH CONSISTENT SNAPSHOT clause initiates a consistent read. It does not change the current transaction isolation level. Therefore, it provides consistent data only if the current isolation level allows consistent reading (i.e., REPEATABLE READ or SERIALIZABLE). At this time, it only works with InnoDB tables. See the SET TRANSACTION statement earlier in this chapter for more information on isolation levels.