If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Committing Transactions
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:

Oracle has generated rollback segment records in rollback segment buffers of the system global area (SGA). The rollback information contains the old data values changed by the SQL statements of the transaction.

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.

The changes have been made to the database buffers of the SGA. These changes may go to disk before a transaction actually 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 may happen before the transaction commits or, alternatively, it may happen some time after the transaction commits.

/* AND ALSO TO YOUR PREVIOUS Q ABOUT THE TRANSACTION DEFFERING,ALTHOUGH IT IS CNSTR DEFERRING.......... */

Discrete Transaction Management
Application developers can improve the performance of short, nondistributed transactions by using the BEGIN_DISCRETE_TRANSACTION procedure. This procedure streamlines transaction processing so that short transactions can execute more rapidly.

During a discrete transaction, all changes made to any data are deferred until the transaction commits. Of course, other concurrent transactions are unable to see the uncommitted changes of a transaction whether the transaction is discrete or not.

The following events occur during a discrete transaction:

Oracle generates redo information, but stores it in a separate location in memory.

When the transaction issues a commit request, Oracle writes the redo information to the redo log file along with other group commits.

Oracle applies the changes to the database block directly to the block.

Oracle returns control to the application after the commit completes.

This transaction design eliminates the need to generate undo information, since the block is not modified until the transaction is committed, and the redo information is stored in the redo log buffers.

There is no interaction between discrete transactions, which always generate redo, and the NOLOGGING mode, which applies only to direct path operations. Discrete transactions may therefore be issued against tables that have the NOLOGGING attribute set.

Tarry: You said "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 may happen before the transaction commits or, alternatively, it may happen some time after the transaction commits"

Does this mean that -- those transactions that went to the disk (datafiles) before getting committed are still available say after a Power Failure? Can you kindly substantiate this?

Or more specific asnwer to anand's Q about oracle's write ahead logging logic.

The database writer writes modified blocks from the database buffer cache to the datafiles. Since Oracle uses write-ahead logging, DBWn does not need to write blocks when a transaction commits. Instead, DBWn is designed to perform batched writes with high efficiency. In the most common case, DBWn writes only when more data needs to be read into the system global area and too few database buffers are free. The least recently used data is written to the datafiles first.

They won't be SEEN. but the instance recovery initiated by the SMON will do the following.................

Due to the way in which DBWn writes database buffers to datafiles(TARRY SAYS: here i would assume that the write ahead logging forced by sga flooded with subsequent queries,and limited space in the db buffer,will prompt the DBWn to start writing modifictions the last firsts ...how don't ask me this is what oracle says, maybe the experienced oracle guys can explain why that happens) being read at any given time a datafile may contain some tentative modifications by uncommitted transactions and may not contain some modifications by committed transactions. Therefore, two potential situations can result after a failure:

Data blocks containing committed modifications were not written to the datafiles, so the changes may only appear in the redo log. Therefore, the redo log contains committed data that must be applied to the datafiles.

Since the redo log may have contained data that was not committed, uncommitted transaction changes applied by the redo log during recovery must be erased from the datafiles.

To solve this situation, two separate steps are always used by Oracle during recovery from an instance or media failure: rolling forward and rolling back.

Rolling Forward
The first step of recovery is to roll forward, that is, reapply to the datafiles all of the changes recorded in the redo log. Rolling forward proceeds through as many redo log files as necessary to bring the datafiles forward to the required time.

If all necessary redo information is online, Oracle rolls forward automatically when the database starts. After roll forward, the datafiles contain all committed changes as well as any uncommitted changes that were recorded in the redo log.

Rolling Back
The roll forward is only half of recovery. After the roll forward, any changes that were not committed must be undone. After the redo log files have been applied, then the rollback segments are used to identify and undo transactions that were never committed, yet were recorded in the redo log. This process is called rolling back. Oracle completes this step automatically.

However, this leaves another Question for me: If redo log contained committed and uncommitted data -- Why did they design such that Oracle Rolls-back after Roll Forward? Why not retain even the uncommitted?? Wouldn't this have been a big advantage during catastrophic instances? Tarry, I would really appreciate if you could shed some light over this.