Name
START TRANSACTION
Synopsis
START TRANSACTION [WITH CONSISTENT SNAPSHOT]
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, ...
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.