SQL in a Nutshell, 2nd Edition

Name

COMMIT Statement

Synopsis

The COMMIT statement explicitly ends an open transaction and makes the changes permanent in the database. Transactions can be opened implicitly as part of an INSERT, UPDATE, or DELETE statement, or opened explicitly with a START statement. In either case, an explicitly issued COMMIT statement will end the open transaction.

Platform	Command
DB2	Supported
MySQL	Supported, with variations
Oracle	Supported, with variations
PostgreSQL	Supported, with variations
SQL Server	Supported, with variations

SQL2003 Syntax

COMMIT [WORK] [AND [NO] CHAIN]

Keywords

COMMIT [WORK]

Ends the current, open transaction and writes any data manipulated by the transaction to the database. The optional keyword WORK is noise and has no effect.

AND [NO] CHAIN

AND CHAIN tells the DBMS to treat the next transaction as if it were a part of the preceding transaction. In effect, the two transactions are separate units of work, but they share a common transaction environment (such as transaction isolation level). Including the optional NO keyword tells the DMBS to explicitly use the ANSI default behavior. The COMMIT keyword by itself is functionally equivalent to the statement COMMIT WORK AND NO CHAIN.

Rules at a Glance

For simple operations, you will execute transactions (that is, SQL code that manipulates or changes data and objects in a database) without explicitly declaring a transaction. However, all transactions are best managed by explicitly closing them with a COMMIT