Oracle Database - COMMIT (of a transaction)

> Database > Oracle Database

1 - About

Advertising

3 - Syntax

COMMIT;

4 - Committing Transactions

A commit ends the current transaction and makes permanent all changes performed in the transaction.

When a transaction commits, the following actions occur:

The internal transaction table for the associated undo tablespace records that the transaction has committed. The corresponding unique SCN of the transaction is assigned and recorded in the transaction table. See “Serializable Isolation Level”.

  • The log writer (LGWR) process writes remaining redo log entries in the redo log buffers to the online redo log and writes the transaction SCN to the online redo log. This atomic event constitutes the commit of the transaction.
  • Oracle Database releases locks held on rows and tables.

Users who were enqueued waiting on locks held by the uncommitted transaction are allowed to proceed with their work.

  • Oracle Database deletes savepoints.
  • Oracle Database performs a commit cleanout.

If modified blocks containing data from the committed transaction are still in the SGA, and if no other session is modifying them, then the database removes lock-related transaction information from the blocks. Ideally, the COMMIT cleans out the blocks so that a subsequent SELECT does not have to perform this task.

Because a block cleanout generates redo, a query may generate redo and thus cause blocks to be written during the next checkpoint.

  • Oracle Database marks the transaction complete.

After a transaction commits, users can view the changes.

Advertising

5 - Performance

Typically, a commit is a fast operation, regardless of the transaction size. The speed of a commit does not increase with the size of the data modified in the transaction. The lengthiest part of the commit is the physical disk I/O performed by LGWR. However, the amount of time spent by LGWR is reduced because it has been incrementally writing the contents of the redo log buffer in the background.

The default behaviour is for LGWR to write redo to the online redo log synchronously and for transactions to wait for the buffered redo to be on disk before returning a commit to the user. However, for lower transaction commit latency, application developers can specify that redo be written asynchronously so that transactions need not wait for the redo to be on disk and can return from the COMMIT call immediately.

6 - Documentation / Reference