Let’s walk through the preceding example and count the locking operations that happen in an OPS and non-OPS world. To simplify the example greatly, we will ignore usage of rollback segment space and temporary tablespace for the moment. We will also assume that the table being modified has no indexes or referential integrity constraints. After we walk through the simplified example, we will discuss the effect these things would have on the results. The reader should also note that the example assumes hash locking.

1.  Instance Clerks becomes the owner of the PCM lock covering data block 12345, containing row 1 of table purchase_order, and updates the row.
To update a row, the data block 12345 must first be read. Depending upon what happened before this call, Instance Clerks may have had to issue a ping to acquire the PCM lock. Instance Clerks may have had to perform a physical I/O as well.

2.  Instance Supervisors requests the block to update row 4.
At this point, the IDLM issues a ping to Instance Clerks, because it presently holds a PCM lock from step 1. Until a PCM lock is granted, the end user issuing the update for row 4 waits.

3.  Instance Clerks writes the data block to disk and releases the PCM lock (but still has a row-level RX lock on row 1).
Instance Clerks incurs a physical I/O to write the data block to disk. Note that even though the PCM lock is being released, Instance Clerks still has a row-level exclusive lock on row 1.

4.  Instance Supervisors becomes the owner of the block and the PCM lock and then updates row 4. (Note: Instance Supervisors must reread the block to get the current copy.)
Instance Clerks obtains the PCM lock and also issues a row-level exclusive lock for row 4. Note that Instance Supervisors must reread the block to get a current copy of the block, because it has changed since it was last used by this instance. Depending upon operating system-level buffering, this may or may not result in a physical I/O.

5.  Instance Clerks requests the block to update row 7.
At this point, the IDLM issues a ping to Instance Clerks, because it presently holds a PCM lock from step 1. Until a PCM lock is granted, the end user issuing the update for row 7 waits.

6.  Instance Supervisors writes the data block to disk and releases the block and the PCM lock (but still has a row-level RX lock on row 4).
Instance Clerks incurs a physical I/O to write the data block to disk. Note that even though the PCM lock is being released, Instance Supervisors still has a row-level exclusive lock on row 4.

7.  Instance Clerks becomes the owner of the block and PCM lock and updates row 7. (Note: Instance Clerks must reread the block to get the current copy.)
Instance Clerks obtains the PCM lock and also issues a row-level exclusive lock for row 7. Note that Instance Supervisors must reread the block to get a current copy of it, because it has changed since it was last used by this instance. Depending upon the operating system-level buffering, this may or may not result in a physical I/O.

8.  Instance Clerks commits its transaction, and still owns the PCM lock and the master copy of the block until another instance requests the block. (All row-level locks for rows 1 and 7 are released—Instance Supervisors still has an RX lock on row 4 with the transaction in progress.)
As a result of the commit, row-level locks for rows 1 and 7 are released. Note that even though Instance Supervisors does not have a PCM lock on block 12345, it has a row-level lock on row 4 with a unit of work in progress.

Let’s summarize locking activity for the preceding example and examine it in relation to the relative times to acquire locks presented earlier. In the example, locks were allocated in steps 1, 4 and 7. Both latches and enqueues are required for the row-level exclusive (type RX) locks on rows 1, 4, and 7. We count latches and enqueues separately because they have different relative weights. Remember that it takes 1,000 times as long to acquire an enqueue as it does a latch. Furthermore, remember that it takes 100,000 times as long to acquire a PCM lock as it does a latch, and 100 times as long as it takes to acquire an enqueue.

In Table 31.2, I display the number of latch, enqueue, and PCM-lock acquisitions made for steps 1, 4, and 7. When you multiply the lock acquisitions by the appropriate relative weights and add them up, you find that it takes considerably longer to acquire the resources involved in an OPS world than it would in a non-OPS world. In other words, you would expect both transactions to take 100 times as long in an OPS world as they would in a non-OPS world in this example.