SQL in a Nutshell

Name

COMMIT TRANSACTION

Synopsis

The COMMIT TRANSATION statement explicitly ends an open transaction, whether explicitly opened with BEGIN, or implicitly opened as part of an INSERT, UPDATE, or DELETE statement. This command allows the manual and permanent end to a data-manipulation operation.

Vendor	Command
SQL Server	Supported, with variations
MySQL	Not supported
Oracle	Supported
PostgreSQL	Supported

SQL99 Syntax and Description

COMMIT [WORK]

In addition to finalizing a single or group of data-manipulation operation(s), COMMIT has some interesting effects on other aspects of a transaction. First, it closes any associated open cursors. Second, any temporary table(s) specified with ON COMMIT DELETE ROWS are cleared of data. Third, all locks opened by the transaction are released. Finally, all deferred constraints are checked. If the deferred constraints are violated, the transaction is rolled back.

Please note that SQL99 dictates that transactions are implicitly opened when one of these statements is executed:

ALTER

CLOSE

COMMIT AND CHAIN (new for SQL99)

CREATE

DELETE

DROP

FETCH

FREE LOCATOR

GRANT

HOLD LOCATOR

INSERT

OPEN

RETURN

REVOKE

ROLLBACK AND CHAIN (new for SQL99)

SELECT

START TRANSACTION (new for SQL99)

UPDATE

SQL99 offers the new, optional keywords AND CHAIN . None of our vendors yet support this command. This new syntax is:

COMMIT [WORK] [AND [NO] CHAIN]

The AND CHAIN option tells the DBMS to treat the following transaction as if it were a ...