Oracle Database - COMMIT (of a transaction)

> Database > Oracle Database

1 - About

In a commit, a user explicitly or implicitly requested that the changes in the transaction be made permanent. Changes made by the transaction are permanent and visible to other users only after a transaction commits.

changes mad prior to any commit event are in a sort of staging area.

Advertising

3 - Syntax

COMMIT;

or Ansi Standard SQL:

COMMIT WORK;

4 - Type

4.1 - Explicit

An explicit commit occurs when the COMMIT statement is executed.

4.2 - Implicit

An implicit commit occurs automatically when certain database events occur.

The database issues an implicit COMMIT statement before and after every DDL statement. If the current transaction contains DML statements, then Oracle Database first commits the transaction and then runs and commits the DDL statement as a new, single-statement transaction.

A user exits normally from most Oracle Database utilities and tools, causing the current transaction to be implicitly committed. The commit behavior when a user disconnects is application-dependent and configurable.

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

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

7 - Documentation / Reference

db/oracle/commit.txt · Last modified: 2017/09/13 16:15 by gerardnico