Databases Reference
In-Depth Information
by Oracle to protect the transaction from changes by other users, which are detailed in
Chapter 8
.
The HR clerk already has the employee record on-screen and so the database block
containing the row for that employee is already in the database buffer cache. The steps
from this point would be:
1. The user modifies the employee name on-screen and the client application sends
a SQL UPDATE statement over the network to the server process.
2. The server process looks for an identical statement in the shared SQL area of the
shared pool. If it finds one, it reuses it. Otherwise, it checks the statement for syntax
and evaluates it to determine the best way to execute it. This processing of the SQL
statement is called
parsing and optimizing
. (The optimizer is described in more
detail in
Chapter 4
.) Once the processing is done, the statement is cached in the
shared SQL area.
3. The server process copies the old image of the employee data about to be changed
notes the changes in a rollback segment and to a redo segment. The rollback seg‐
ment changes are part of the redo. This may seem a bit odd, but remember that
redo is generated for all changes resulting from the transaction. The contents of the
rollback segment have changed because the old employee data was written to the
rollback segment for undo purposes. This change to the contents of the rollback
segment is part of the transaction and therefore part of the redo for that transaction.
4. Once the server process has completed this work, the process modifies the database
block to change the employee name. The database block is stored in the database
cache at this time. Control is passed back to the client process.
5. The HR clerk commits the transaction.
6. The Log Writer (LGWR) process writes the redo information for the entire trans‐
action from the redo log buffer to the current redo logfile on disk. When the op‐
erating system confirms that the write to the redo logfile has successfully completed,
the transaction is considered committed.
7. The server process sends a message to the client confirming the commit.
The user could have canceled or rolled back the transaction instead of committing it,
in which case the server process would have used the old image of the employee data
in the rollback segment to undo the change to the database block.
Figure 3-5
shows the steps described here. Network traffic appears as dotted lines.
