O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required