Oracle Essentials [Electronic resources] : Oracle Database 10g, 3rd Edition نسخه متنی

7.5 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 database can handle
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 situation 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 employees via a
form.

7.5.1 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:

The client modifies the employee name on the screen. The client
process sends a SQL UPDATE statement over the network to the server
process.

The server process obtains a System Change Number (SCN) and reads the
data block containing the target row.

The server records row lock information in the data block.

The server process copies the old image of the employee data about to
be changed to a rollback segment and then modifies the employee data,
which includes writing the SCN to the ORA_ROWSCN pseudocolumn with
Oracle Database 10g.

The server process records the changes to the rollback segment and
the database block in the redo log buffer in the SGA. The rollback
segment changes are part of the redo, because the redo log stores all
changes resulting from the transaction.

The HR clerk commits the transaction.

Log Writer (LGWR) writes the redo information for the entire
transaction, including the SCN that marks the time the transaction
was committed, from the redo log buffer to the current redo log file
on disk. When the operating system confirms that the write to the
redo log file has successfully completed, the transaction is
considered committed.

The server process sends a message to the client confirming the
commit.

7.5.2 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:

Client A modifies the employee name on the screen. Client A sends a
SQL UPDATE statement over the network to the server process.

The server process obtains an SCN for the statement and reads the
data block containing the target row.

The server records row lock information in the data block.

The server process then copies the old image of the employee data
about to be changed to a rollback segment. Once the server process
has written the old employee data to a rollback segment, the server
process modifies the cached database block to change the employee
name, which includes writing the SCN to the ORA_ROWSCN pseudocolumn
with Oracle Database 10g.

The server process records the changes to the rollback segment and
the database block in the redo log buffer in the SGA. The rollback
segment changes are part of the redo, because the redo log stores all
changes resulting from the transaction.

Client B modifies the employee name on the screen and sends a SQL
UPDATE statement to the server.

The server process obtains an SCN and reads the data block containing
the target row.

The server process sees that there is a lock on the target row from
the information in the header of the data block, so it takes one of
two actions. If the isolation level on Client B's
transaction is READ COMMITTED, the server process waits for the
blocking transaction to complete. If the isolation level for Client
B's transaction is SERIALIZABLE, an error is
returned to the client.

Client A commits the transaction, the server process takes the
appropriate action, and the server sends a message to Client A
confirming the commit.

If Client B executed the SQL statement with the READ COMMITTED
isolation level, the SQL statement then proceeds through its normal
operation.

The previous example illustrates the default behavior of Oracle when
it detects a problem caused by a potential lost update. Because the
SERIALIZABLE isolation level has a more drastic outcome when it
detects a write conflict than the READ COMMITTED isolation level,
many developers prefer the latter level. They can avoid some of the
potential conflicts by checking for changes prior to issuing an
update, by either comparing values in a row or using the Oracle
Database 10g row SCN. Alternatively, they can
use the SELECT FOR UPDATE syntax in their SQL to avoid the problem
altogether.

7.5.3 A Read Operation

By
looking at how a user reads data from the table, you can appreciate
the beauty of Oracle's read consistency model. In
this scenario, Client A is reading a series of rows from the EMP
table, while Client B modifies a row before it is read by Client A,
but after Client A begins her transaction:

Client A sends a SQL SELECT statement over the network to the server
process.

The server process obtains an SCN for the statement and begins to
read the requested data for the query. For each data block that it
reads, it compares the SCN of the SELECT statement with the SCNs for
any transactions that were uncommitted in the relevant rows of the
data block. If the server finds an uncommitted transaction with a
later SCN than the current SELECT statement, the server process uses
data in the rollback segments to create a
"consistent read" version of the
data block, current as of the time the SELECT was issued. This is
what provides the multiversion read consistency.

Client B sends a SQL UPDATE statement for a row in the EMP table that
has not yet been read by Client A's SELECT
statement. The server process gets an SCN for the statement and
begins the operation.

Client B commits his changes. The server process completes the
operation, which includes recording information in the data block
that contained the modified row that allows Oracle to determine the
SCN for the update transaction.

The server process for Client A's read operation
comes to the newly modified block. It sees that the data block
contains changes made by a transaction that has an SCN that is later
than the SCN of the SELECT statement. The server process looks in the
data block header, which has a pointer to the rollback segment that
contains the data as it existed when Client A's
transaction started. The rollback segment uses the old version of the
data to create a version of the block as it existed when the SELECT
statement started. Client A's SELECT statement reads
the desired rows from this consistent version of the data block.

Figure 7-2 illustrates the process of reading with multiversion read
consistency.

Figure 7-2. Multiversion read consistency