START TRANSACTION

Use this MySQL statement to start a transaction.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

START TRANSACTION [WITH CONSISTENT SNAPSHOT]

Explanation

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.

Examples

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.