Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

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 handles issues related to 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.

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 (SCN) and reads the data block containing the target row.

3. The server records row lock information in the data block.

4. 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.

5. The server process records the ...