Monday, November 17, 2008

Oracle - Stages in processing commits

Issuing a commit statement says that the current transaction is closed and committed. Even roll back says the same that transaction is completed but the data changes are not be committed or discarded.

For roll backing a transaction undo segments helps in achieving it. As undo segment holds the new and old value of a data change transaction.
Commit does not mean that, server process to immediately perform post or data change to disk from memory (buffered cache). This is the functionality or part of back ground process (DBW0). So what commit statement do?

1) Release table/row locks acquired by transaction

As discussed earlier that when a DML statement is executed by user , there are several steps one among which is to acquire lock on the row/table to avoid another user doing the same.
So those locks are released, as another user can go ahead to perform data changes on the same row/table.

2) Release UNDO segment locks acquired by transaction

As updated, a lock is said to be acquired on the UNDO segment when user executes a DML statement. Such lock is set be released when transaction is completed. So the space acquired by this old values are new values for data change are said to be released which can be used by another transaction.
Oracle does not clean the information from UNDO segment when a transaction is completed instead it is over written by another transaction that performs DML.

3) Generate redo information for committed transaction
Once a transaction is committed, redo log entry is generated by the user process stating that all the data changes made by this transaction are committed or finalized.
This also results in flushing the content of redo log buffer into redo log files for both committed and roll back as well.

No comments:

Post a Comment