Locking and Issues - Oracle Database Transactions and Locking Revealed - page 11

The application supplies values for the bind variables from the data on the screen (in this case 7934 , MILLER ,
and 1300 ) and re-queries this same row from the database, this time locking the row against updates by other
sessions; hence this approach is called pessimistic locking. We lock the row before we attempt to update because we
doubt— we are pessimistic —that the row will remain unchanged otherwise.
Since all tables should have a primary key (the preceding SELECT will retrieve at most one record since it includes
the primary key, EMPNO ) and primary keys should be immutable (we should never update them), we'll get one of three
outcomes from this statement:
If the underlying data has not changed, we will get our •

MILLER row back, and this row will be

locked from updates (but not reads) by others.
If another user is in the process of modifying that row, we will get an •

ORA-00054 resource

busy error. We must wait for the other user to finish with it.
If, in the time between selecting the data and indicating our intention to update, someone has •

already changed the row, then we will get zero rows back. That implies the data on our screen
is stale. To avoid the lost update scenario previously described, the application needs to
re-query and lock the data before allowing the end user to modify it. With pessimistic locking
in place, when User2 attempts to update the telephone field, the application would now
recognize that the address field had been changed and would re-query the data. Thus, User2
would not overwrite User1's change with the old data in that field.

Once we have locked the row successfully, the application will bind the new values, issue the update, and commit
the changes:

SCOTT@ORA12CR1> update emp
2 set ename = :ename, sal = :sal
3 where empno = :empno;

1 row updated.

SCOTT@ORA12CR1> commit;
Commit complete.

We have now very safely changed that row. It is not possible for us to overwrite someone else's changes, as we
verified the data did not change between when we initially read it out and when we locked it—our verification made
sure no one else changed it before we did, and our lock ensures no one else can change it while we are working with it.

Optimistic Locking
The second method, referred to as optimistic locking, defers all locking up to the point right before the update is
performed. In other words, we will modify the information on the screen without a lock being acquired. We are optimistic
that the data will not be changed by some other user; hence we wait until the very last moment to find out if we are right.
This locking method works in all environments, but it does increase the probability that a user performing an
update will lose. That is, when that user goes to update her row, she finds that the data has been modified, and she has
to start over.
One popular implementation of optimistic locking is to keep the old and new values in the application, and upon
updating the data, use an update like this:

Update table
Set column1 = :new_column1, column2 = :new_column2, ....
Where primary_key = :primary_key
And decode( column1, :old_column1, 1 ) = 1
And decode( column2, :old_column2, 1 ) = 1
...