Commit vs Fast Commit vs Commit Cleanout in Oracle Database

oracleoracle-10goracle-11goracle-11g-r2rdbms

I was wondering whether someone could verify my understanding regarding the differences between those 3 terms as pertaining to Oracle Databases.

Many sources confuse these terms and do not explain them in detail so it was a bit of a challenge to find info.

From what I gather:

  1. Commit and fast commit are the exact same thing, all commits are fast commits.
  2. A fast commit essentially only updates the flag in the transaction table of the undo/rollback segment header to indicate that the transaction has committed. However, the actual block is not revisited meaning that the undo byte address (UBA) in the interested transaction list (ITL) which is located in the header of the data block still points to the transaction table of the corresponding undo segment. Furthermore, the lock bytes of the corresponding rows are not released and the lock count in the ITL is unchanged (rows still locked).
  3. In a commit cleanout, the block is revisited and the ITL is updated with the commit SCN. However, the lock count in the ITL and the lock byte stored with each row is still not updated (row still locked just like in fast commit), this does not generate redo even though the block is changed.
  4. Blocks which were committed normally (== fast committed) will undergo Delayed Block Cleanout when they are next touched (and generate redo).
  5. Blocks which were underwent commit cleanout will undergo Delayed Logging Block Cleanout
    when they are next touched (and generate redo).

Hopefully someone can verify these points!
Thanks!

Best Answer

You have the basics right. There is only one type of commit (no normal, fast...).

from the concepts doc:

When a transaction commits, the following actions occur:

  • A system change number (SCN) is generated for the COMMIT.

    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.

So cleanout (full with redo) will be performed during commit if the blocks are still in the SGA.

In active systems, it is common for blocks with uncommited transactions to be written to disk and flushed from the SGA. In this case, the block is left as is and the next query that touches the block will perform delayed block cleanout (your point 5 doesn't happen in all cases).

Related Question