Databases Reference
In-Depth Information
A lock manager called the Distributed Lock Manager (DLM) has historically been used
with Oracle Parallel Server to track locks across multiple instances of Oracle. This is a
completely different and separate locking scheme that doesn't affect the way Oracle
handles row locks. The DLM technology used in Oracle Parallel Server was improved
and integrated into a core product in Oracle9
i
, Real Application Clusters. Real Appli‐
cation Clusters are described in more detail in
Chapter 9
.
How Oracle Handles Locking
If you've read this chapter from the beginning, you should now know enough about the
concepts of concurrency and the features of Oracle to understand how the Oracle Da‐
tabase handles multiuser access. However, to make it perfectly clear how these features
interact, we'll walk you through three scenarios: a simple write to the database, a situa‐
tion in which two users attempt to write to the same row in the same table, and a read
that takes place in the midst of conflicting updates.
For the purposes of these examples, we'll use the scenario of one or two users modifying
the EMP table, a part of the standard sample Oracle schema that lists data about em‐
ployees via a form.
A Simple Write Operation
This example describes a simple write operation, in which one user is writing to a row
in the database. In this example, an HR clerk wants to update the name for an employee.
Assume that the HR clerk already has the employee record on-screen. The steps from
this point are as follows:
1. The client modifies the employee name on the screen. The client process sends a
SQL UPDATE statement over the network to the server process.
2. The server process obtains a System Change Number and reads the data block
containing the target row.
3. The server records row lock information in the data block.
4. The server writes the old image of the data to the redo buffers in memory, and then
writes the changes to an UNDO segment and modifies the employee data, which
includes writing the SCN to the ORA_ROWSCN pseudocolumn in Oracle Database
10
g
or newer database releases.
5. The server process writes the redo buffers to disk, and then writes the UNDO
segments and the changed data to disk. The UNDO segment changes are part of
the redo, since the redo log stores all changes coming from the transaction.
6. The HR clerk commits the transaction.
