MySQueaL Resources

START TRANSACTION Statement

Use this MySQL statement to start a transaction.

Use this MySQL statement to start a transaction in MySQL. 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 needed. You can reverse a transaction if the transaction has not yet been committed with a the MySQL statement, COMMIT, 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 in our MySQL documentation for more information on transactions. The SAVEPOINT statement and the ROLLBACK TO SAVEPOINT statements may also be useful.

Here is an example of this MySQL 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 was inserted into the orders table, the user decided everything went properly and issued 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 of our MySQL documentation for more information on isolation levels.