MySQL in a Nutshell, 2nd Edition

Name

COMMIT

Synopsis

COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]

Use this statement to commit transactions, which are SQL statements that have changed data and have been entered into MySQL but are not yet saved. Transaction statements are currently supported by the InnoDB, NDB Cluster, and BDB storage engines and are ignored if used with MyISAM tables.

If AUTOCOMMIT is enabled, it must be disabled for this statement to be meaningful. You can disable it explicitly with the statement:

SET AUTOCOMMIT = 0;

Normally, AUTOCOMMIT is disabled by a START TRANSACTION statement and reinstated with the COMMIT statement.

The WORK keyword is optional and has no effect on the results. It’s available for compatibility with its counterpart, BEGIN WORK. Use the AND CHAIN clause to complete one transaction and start another, thus making it unnecessary to use START TRANSACTION again. Use the AND RELEASE clause to end the current client session after completing the transaction.

Add the keyword NO to indicate explicitly that a new transaction is not to begin (when used with CHAIN) or that the client session is not to end (when used with RELEASE). This is necessary only when the system variable completion_type is set so that the server assumes that a COMMIT statement indicates the start of another transaction or releases a session.

Here is a basic example of this statement:

START TRANSACTION; LOCK TABLES orders WRITE; INSERT DATA INFILE '/tmp/customer_orders.sql' INTO TABLE orders; SELECT ...; COMMIT; UNLOCK ...