Redo and Undo - Oracle Database Transactions and Locking Revealed - page 119

When we COMMIT , all that is left to happen is the following:
A System Change Number (SCN) is generated for our transaction. In case you are not familiar •

with it, the SCN is a simple timing mechanism Oracle uses to guarantee the ordering of
transactions and to enable recovery from failure. It is also used to guarantee read-consistency
and checkpointing in the database. Think of the SCN as a ticker; every time someone COMMIT s,
the SCN is incremented by one.

•

LGWR writes all of our remaining buffered redo log entries to disk and records the SCN in
the online redo log files as well. This step is actually the COMMIT . If this step occurs, we have
committed. Our transaction entry is “removed” from V$TRANSACTION —this shows that we have
committed.

• All locks recorded in

V$LOCK held by our session are released, and everyone who was
enqueued waiting on locks we held will be woken up and allowed to proceed with their work.

• Some of the blocks our transaction modified will be visited and “cleaned out” in a fast mode if

they are still in the buffer cache. Block cleanout refers to the lock-related information we store
in the database block header. Basically, we are cleaning out our transaction information on
the block, so the next person who visits the block won't have to. We are doing this in a way that
need not generate redo log information, saving considerable work later (this is discussed fully
in the next chapter).

As you can see, there is very little to do to process a COMMIT . The lengthiest operation is, and always will be, the
activity performed by LGWR , as this is physical disk I/O. The amount of time spent by LGWR here will be greatly reduced
by the fact that it has already been flushing the contents of the redo log buffer on a recurring basis. LGWR will not buffer
all of the work you do for as long as you do it. Rather, it will incrementally flush the contents of the redo log buffer in
the background as you are going along. This is to avoid having a COMMIT wait for a very long time in order to flush all of
your redo at once.
So, even if we have a long-running transaction, much of the buffered redo log it generates would have been
flushed to disk, prior to committing. On the flip side is the fact that when we COMMIT , we must typically wait until all
buffered redo that has not been written yet is safely on disk. That is, our call to LGWR is by default a synchronous one.
While LGWR may use asynchronous I/O to write in parallel to our log files, our transaction will normally wait for LGWR to
complete all writes and receive confirmation that the data exists on disk before returning.

■ oracle 11 g release 1 and above have an asynchronous wait. however, that style of commit has limited
general-purpose use. Commits in any end-user-facing application should be synchronous.

Note

Now, earlier I mentioned that we were using a Java program and not PL/SQL for a reason—and that reason is a
PL/SQL commit-time optimization. I said that our call to LGWR is by default a synchronous one and that we wait for it
to complete its write. That is true in Oracle 12c Release 1 and before for every programmatic language except PL/SQL .
The PL/SQL engine, realizing that the client does not know whether or not a COMMIT has happened in the PL/SQL
routine until the PL/SQL routine is completed, does an asynchronous commit. It does not wait for LGWR to complete;
rather, it returns from the COMMIT call immediately. However, when the PL/SQL routine is completed, when we return
from the database to the client, the PL/SQL routine will wait for LGWR to complete any of the outstanding COMMIT s. So,
if you commit 100 times in PL/SQL and then return to the client, you will likely find you waited for LGWR once—not 100
times—due to this optimization. Does this imply that committing frequently in PL/SQL is a good or OK idea? No, not
at all—just that it is not as bad an idea as it is in other languages. The guiding rule is to commit when your logical unit
of work is complete—not before.