COMMIT

Use this MySQL statement to commit transactions, which are SQL statements that have changed data and that have been entered into MySQL but not yet saved.

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).

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

Explanation

Use this MySQL statement to commit transactions, which are SQL statements that have changed data and that have been entered into MySQL but 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 MySQL statement, COMMIT.

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.

Examples

Here is a basic example of this MySQL statement:

START TRANSACTION;

LOCK TABLES orders WRITE;

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

SELECT ...;

COMMIT;

UNLOCK TABLES;

In this example, after inserting a batch of orders into the orders table, an administrator enters a series of SELECT statements to check the integrity of the data. They are omitted here to save space. If there is a problem, the MySQL statement, ROLLBACK could be issued rather than the MySQL statement, COMMIT shown here. ROLLBACK would remove the data imported by the INSERT DATA INFILE statement.

The following statements also cause a transaction to be committed: ALTER EVENT, ALTER FUNCTION, ALTER PROCEDURE, ALTER TABLE, BEGIN, CREATE DATABASE, CREATE EVENT, CREATE FUNCTION, CREATE INDEX, CREATE PROCEDURE, CREATE TABLE, DROP DATABASE, DROP EVENT, DROP FUNCTION, DROP INDEX, DROP PROCEDURE, DROP TABLE, LOAD DATA INFILE, LOCK TABLES, RENAME TABLE, SET AUTOCOMMIT=1, START TRANSACTION, TRUNCATE, and UNLOCK TABLES.