Oracle7 Server SQL Reference

COMMIT

Purpose

To end your current transaction and make permanent all changes performed in the transaction. This command also erases all savepoints in the transaction and releases the transaction's locks.

You can also use this command to manually commit an in-doubt distributed transaction.

Prerequisites

You need no privileges to commit your current transaction.

To manually commit a distributed in-doubt transaction that you originally committed, you must have FORCE TRANSACTION system privilege. To manually commit a distributed in-doubt transaction that was originally committed by another user, you must have FORCE ANY TRANSACTION system privilege.

If you are using Trusted Oracle7 in DBMS MAC mode, you can only commit an in-doubt transaction if your DBMS label matches the label the transaction's label and the creation label of the user who originally committed the transaction or if you satisfy one of the following criteria:

If the transaction's label or the user's creation label is higher than your DBMS label, you must have READUP and WRITEUP system privileges.

If the transaction's label or the user's creation label is lower than your DBMS label, you must have WRITEDOWN system privilege.

If the transaction's label or the user's creation label is not comparable with your DBMS label, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

Syntax

Keywords and Parameters

WORK

is supported only for compliance with standard SQL. The statements COMMIT and COMMIT WORK are equivalent.

COMMENT

specifies a comment to be associated with the current transaction. The 'text' is a quoted literal of up to 50 characters that Oracle7 stores in the data dictionary view DBA_2PC_PENDING along with the transaction ID if the transaction becomes in-doubt.

FORCE

manually commits an in-doubt distributed transaction. The transaction is identified by the 'text' containing its local or global transaction ID. To find the IDs of such transactions, query the data dictionary view DBA_2PC_PENDING. You can also use the integer to specifically assign the transaction a system change number (SCN). If you omit the integer, the transaction is committed using the current SCN.

COMMIT statements using the FORCE clause are not supported in PL/SQL.

Usage Notes

It is recommended that you explicitly end every transaction in your application programs with a COMMIT or ROLLBACK statement, including the last transaction, before disconnecting from Oracle7. If you do not explicitly commit the transaction and the program terminates abnormally, the last uncommitted transaction is automatically rolled back.

A normal exit from most Oracle7 utilities and tools causes the current transaction to be committed. A normal exit from an Oracle Precompiler program does not commit the transaction and relies on Oracle7 to rollback the current transaction. See the COMMIT command (Embedded SQL) .

Transactions

A transaction (or a logical unit of work) is a sequence of SQL statements that Oracle7 treats as a single unit. A transaction begins with the first executable SQL statement after a COMMIT, ROLLBACK or connection to the database. A transaction ends with a COMMIT, ROLLBACK or disconnection (intentional or unintentional) from the database. Note that Oracle7 issues an implicit COMMIT before and after any Data Definition Language statement.

You can also use a COMMIT or ROLLBACK statement to terminate a read only transaction begun by a SET TRANSACTION statement.

Example I

This example inserts a row into the DEPT table and commits this change:

INSERT INTO dept 	VALUES (50, 'MARKETING', 'TAMPA')

COMMIT WORK

Example II

The following statement commits the current transaction and associates a comment with it:

COMMIT WORK

	COMMENT 'In-doubt transaction Code 36, Call (415) 555-2637'

If a network or machine failure prevents this distributed transaction from committing properly, Oracle7 stores the comment in the data dictionary along with the transaction ID. The comment indicates the part of the application in which the failure occurred and provides information for contacting the administrator of the database where the transaction was committed.

Distributed Transactions

Oracle7 with the distributed option allows you to perform distributed transactions, or transactions that modify data on multiple databases. To commit a distributed transaction, you need only issue a COMMIT statement as you would to commit any other transaction. Each component of the distributed transaction is then committed on each database.

If a network or machine failure during the commit process for a distributed transaction, the state of the transaction may be unknown, or in-doubt. After consultation with the administrators of the other databases involved in the transaction, you may decide to manually commit or roll back the transaction on your local database. You can manually commit the transaction on your local database by using the FORCE clause of the COMMIT command. For more information on these topics, see the "Database Administration" chapter of Oracle7 Server Distributed Systems, Volume I.

Note that a COMMIT statement with a FORCE clause only commits the specified transaction. Such a statement does not affect your current transaction.

Example III

The following statement manually commits an in-doubt distributed transaction:

COMMIT FORCE '22.57.53'