Skip Headers
Oracle® Database SQL Language Reference
11g Release 1 (11.1)

Part Number B28286-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

COMMIT

Purpose

Use the COMMIT statement to end your current transaction and make permanent all changes performed in the transaction. A transaction is a sequence of SQL statements that Oracle Database treats as a single unit. This statement also erases all savepoints in the transaction and releases transaction locks.

Until you commit a transaction:

Oracle Database issues an implicit COMMIT before and after any data definition language (DDL) statement.

You can also use this statement to

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

A normal exit from most Oracle 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 Oracle Database to roll back the current transaction.

See Also:

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.

Syntax

commit::=

Description of commit.gif follows
Description of the illustration commit.gif

Semantics

COMMIT

All clauses after the COMMIT keyword are optional. If you specify only COMMIT, then the default is COMMIT WORK WRITE IMMEDIATE WAIT.

WORK

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

COMMENT Clause

Specify a comment to be associated with the current transaction. The 'text' is a quoted literal of up to 255 bytes that Oracle Database stores in the data dictionary view DBA_2PC_PENDING along with the transaction ID if a distributed transaction becomes in doubt. This comment can help you diagnose the failure of a distributed transaction.

See Also:

COMMENT for more information on adding comments to SQL statements

WRITE Clause

Use this clause to specify the priority with which the redo information generated by the commit operation is written to the redo log. This clause can improve performance by reducing latency, thus eliminating the wait for an I/O to the redo log. Use this clause to improve response time in environments with stringent response time requirements where the following conditions apply:

You can specify the WAIT | NOWAIT and IMMEDIATE | BATCH clauses in any order.

Note:

if you omit this clause, then the behavior of the commit operation is controlled by the COMMIT_WRITE initialization parameter, if it as been set. The default value of the parameter is the same as the default for this clause. Therefore, if the parameter has not been set and you omit this clause, then commit records are written to disk before control is returned to the user.

WAIT | NOWAIT Use these clauses to specify when control returns to the user.

Caution:

With NOWAIT, a crash occurring after the commit message is received, but before the redo log record(s) are written, can falsely indicate to a transaction that its changes are persistent.

If you omit this clause, then the transaction commits with the WAIT behavior.

IMMEDIATE | BATCH Use these clauses to specify when the redo is written to the log.

If you omit this clause, then the transaction commits with the IMMEDIATE behavior.

See Also:

Oracle Database Advanced Application Developer's Guide for more information on asynchronous commit

FORCE Clause

Use this clause to manually commit an in-doubt distributed transaction or a corrupt transaction.

Note:

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

See Also:

Oracle Database Administrator's Guide for more information on these topics

Examples

Committing an Insert: Example This statement inserts a row into the hr.regions table and commits this change:

INSERT INTO regions VALUES (5, 'Antarctica'); 

COMMIT WORK;
 

To commit the same insert operation and instruct the database to buffer the change to the redo log, without initiating disk I/O, use the following COMMIT statement:

COMMIT WRITE BATCH;

Commenting on COMMIT: Example The following statement commits the current transaction and associates a comment with it:

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

If a network or machine failure prevents this distributed transaction from committing properly, then Oracle Database 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.

Forcing an In-Doubt Transaction: Example The following statement manually commits a hypothetical in-doubt distributed transaction. Query the V$CORRUPT_XID_LIST data dictionary view to find the transaction IDs of corrupt transactions. You must have DBA privileges to view the V$CORRUPT_XID_LIST and to issue this statement.

COMMIT FORCE '22.57.53';