Skip to Main Content
SQL in a Nutshell
book

SQL in a Nutshell

by Kevin Kline
December 2000
Intermediate to advanced content levelIntermediate to advanced
224 pages
9h 52m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

SQL in a Nutshell, 3rd Edition

SQL in a Nutshell, 3rd Edition

Kevin Kline

Publisher Resources

ISBN: 1565927443