Databases Reference
In-Depth Information
7. Log Writer (LGWR) writes the redo information for the entire transaction, includ‐
ing the SCN that marks the time the transaction was committed, from the redo log
buffer to the current redo logfile on disk. When the operating system confirms that
the write to the redo logfile has successfully completed, the transaction is considered
committed.
8. The server process sends a message to the client confirming the commit.
Oracle Database 10
g
Release 2 introduced the ability to have the server process return
control to the client without waiting for all the redo information to be written. The plus
side of this enhancement is that high-volume OLTP applications may benefit from im‐
proved performance. The downside of this feature is that it opens a window of vulner‐
ability—the database could crash after a transaction had been committed, but before
the redo was written, which would make it impossible to recover the committed trans‐
action, so this feature should be used with caution.
Oracle Database 12
c
introduces a new feature called Transaction Guard. If you followed
the description above closely, you can see that a transaction could be between step 7
and step 8 when some type of failure occurs that prevents the message relaying the
successful commit to the application. (This failure could have nothing to do with the
database and be quite short-lived—such as a network failure.)This series of events would
mean that the Oracle Database has committed data, but the application does not know
whether the commit failed or succeeded. Transaction Guard provides an API that allows
an application to specifically check on the outcome of a potentially failed transaction.
Transaction Guard is described in more detail in
Chapter 9
.
A Conflicting Write Operation
The write operation previously described is a little different if there are two users, Client
A and Client B, who are trying to modify the same row of data at the same time. The
steps are as follows:
1. Client A modifies the employee name on the screen. Client A sends a SQL UPDATE
statement over the network to the server process.
2. The server process obtains an SCN for the statement and reads the data block con‐
taining the target row.
3. The server records row lock information in the data block.
4. The server process writes the changes to the redo log buffer.
5. The server process copies the old image of the employee data about to be changed
to an UNDO segment. Once the server process has completed this work, the process
modifies the employee data, which includes writing the SCN to the ORA_ROWSCN
pseudocolumn in Oracle Database 10
g
or newer database releases.
