Database Reference
In-Depth Information
If you look at these application categories, you'll notice that all three of them are background, noninteractive
applications. They do not interact with a human being directly. Any application that does interact with a person—that
reports to the person “Commit complete”—should use the synchronous commit. Asynchronous commits are not a
tuning device for your online customer-facing applications. Asynchronous commits are applicable only to batch-oriented
applications, those that are automatically restartable upon failure. Interactive applications are not restartable automatically
upon failure—a human being must redo the transaction. Therefore, you have another flag that tells you whether this
capability can be considered—do you have a batch application or an interactive one? Unless it is batch-oriented,
synchronous commit is the way to go.
So, outside of those three categories of batch applications, this capability—
COMMIT WRITE NOWAIT
—should
probably not be used. If you do use it, you need to ask yourself what would happen if your application is told
commit
processed
, but later, the commit is undone. You need to be able to answer that question and come to the conclusion
that it will be OK if that happens. If you can't answer that question, or if a committed change being lost would have
serious repercussions, you should not use the asynchronous commit capability.
COMMITS in a Nondistributed PL/SQL Block
Since PL/SQL was first introduced in version 6 of Oracle, it has been transparently using an asynchronous commit.
That approach has worked because all PL/SQL is like a batch program in a way—the end user does not know the
outcome of the procedure until it is completely finished. That's also why this asynchronous commit is used only
in nondistributed PL/SQL blocks of code; if we involve more than one database, then there are two things—two
databases—relying on the commit being durable. When two databases are relying on the commit being durable,
we have to utilize synchronous protocols or a change might be committed in one database but not the other.
■
of course, pipelined pL/sQL functions deviate from “normal” pL/sQL functions. in normal pL/sQL functions,
the outcome is not known until the end of the stored procedure call. pipelined functions in general are able to return
data to a client long before they complete (they return “chunks” of data to the client, a bit at a time). But since pipelined
functions are called from
SELECT
statements and would not be committing anyway, they do not come into play in
this discussion.
Note
Therefore, PL/SQL was developed to utilize an asynchronous commit, allowing the
COMMIT
statement in
PL/SQL to not have to wait for the physical I/O to complete (avoiding the “log file sync” wait). That does not mean
that you can't rely on a PL/SQL routine that commits and returns control to your application to not be durable with
respect to its changes—PL/SQL will wait for the redo it generated to be written to disk before returning to the client
application—but it will only wait once, right before it returns.
■
the following example demonstrates a bad practice—one that i call “slow-by-slow processing” or
“row-by-row processing,” as row-by-row is synonymous with slow-by-slow in a relational database. it is meant just
to illustrate how pL/sQL processes a
COMMIT
statement.
Note
