Understanding Asynchronous COMMIT

Before Oracle 10g Release 2, Life cycle of a transaction was as follows:

- Transaction Starts When User Issues the First DML, Oracle generates redo entries corresponding to the DML and write it to buffer in memory.
- While user is issuing DML, Oracle generates redo entries corresponding to the DML and write it to buffer in memory.
- Transaction Ends When User Issues a COMMIT (can be explicit or implicit), Oracle immediately writes this buffered redo to disk.

Main point to understand here is that once COMMIT is issued, Oracle does not return the control to the user until the redo entries corresponding to that commited transaction have been physically written to disk.

This way application can be 100% sure that once the transaction is commited, it is protected against instance failure and commited data can be recovered by “rolling forward” log files.

This is another words we can say is synchronous COMMIT.

Now Asynchronous COMMIT:

In Oracle 10g Release 2, keeping in view a very high speed transaction environment, Oracle has introduce two options:

1. One is to return immediately after the COMMIT is issued, rather than waiting for the log activity to complete.
2. Another option batches multiple transactions together in memory before writing to the disk.

The full syntax of the new WRITE clause is:

COMMIT [WRITE [IMMEDIATE | BATCH] [WAIT | NOWAIT] ]

By default, if no WRITE clause is specified, a normal COMMIT is equivalent to

COMMIT WRITE IMMEDIATE WAIT;

The COMMIT statement has a new clause, WRITE, which indicates these options. The default COMMIT statement is the same as:

COMMIT WRITE IMMEDIATE WAIT;

To specify that the transaction should be written individually but that the application should not wait for it, specify:

COMMIT WRITE IMMEDIATE NOWAIT;

or just

COMMIT WRITE NOWAIT;

Likewise, to specify that Oracle’s log writer process (LGWR) is allowed to batch multiple transactions together before writing, specify:

COMMIT WRITE BATCH NOWAIT;

The option can also be set system-wide by modifying the commit_write instance parameter. Multiple options are separated by commas, as:

ALTER SYSTEM SET commit_write = BATCH, NOWAIT;