Oracle Commit Transactions

[popgo]

Committing a transaction means making permanent the changes performed
by the SQL statements within the transaction.

Before a transaction that modifies data is committed, the following
has occurred:
1. Oracle has generated undo information. The undo information
contains the old data values changed by the SQL statements of the
transaction.
2. Oracle has generated redo log entries in the redo log buffer of the
SGA. The redo log record contains the change to the data block and the
change to the rollback block. These changes may go to disk before a
transaction is committed.
3. The changes have been made to the database buffers of the SGA.
These changes may go to disk before a transaction is committed.

Note: The data changes for a committed transaction, stored in the
database buffers of the SGA, are not necessarily written immediately
to the datafiles by the database writer (DBWn) background process.
This writing takes place when it is most efficient for the database to
do so. It can happen before the transaction commits or, alternatively,
it can happen some time after the transaction commits.

When a transaction is committed, the following occurs:
1. The internal transaction table for the associated undo tablespace
records that the transaction has committed, and the corresponding
unique system change number (SCN) of the transaction is assigned and
recorded in the table.
2. The log writer process (LGWR) writes redo log entries in the SGA's
redo log buffers to the redo log file. It also writes the
transaction's SCN to the redo log file.
This atomic event constitutes the commit of the transaction.
3. Oracle releases locks held on rows and tables.
4. Oracle marks the transaction complete.

Note: The default behavior is for LGWR to write redo to the online
redo log files synchronously and for transactions to wait for the redo
to go to disk before returning a commit to the user. However, for
lower transaction commit latency application developers can specify
that redo be written asynchronously and that transactions do not need
to wait for the redo to be on disk.