Locking and Latching - Expert Oracle Database Architecture - page 206

Pessimistic Locking
The pessimistic locking method would be put into action the instant before a user modifies a value on the screen. For
example, a row lock would be placed as soon as the user indicates his intention to perform an update on a specific row
that he has selected and has visible on the screen (by clicking a button on the screen, say). That row lock would persist
until the application applied the users' modifications to the row in the database and committed.
Pessimistic locking is useful only in a stateful or connected environment—that is, one where your application
has a continual connection to the database and you are the only one using that connection for at least the life of your
transaction. This was the prevalent way of doing things in the early to mid 1990s with client/server applications. Every
application would get a direct connection to the database to be used solely by that application instance. This method
of connecting, in a stateful fashion, has become less common (though it is not extinct), especially with the advent of
application servers in the mid to late 1990s.
Assuming you are using a stateful connection, you might have an application that queries the data without
locking anything:
SCOTT@ORA12CR1> select empno, ename, sal from emp where deptno = 10;
EMPNO ENAME SAL
---------- ---------- ----------
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
Eventually, the user picks a row she would like to update. Let's say in this case, she chooses to update the MILLER
row. Our application will, at that point, (before the user makes any changes on the screen but after the row has been
out of the database for a while) bind the values the user selected so we can query the database and make sure the
data hasn't been changed yet. In SQL*Plus, to simulate the bind calls the application would make, we can issue the
following:
SCOTT@ORA12CR1> variable empno number
SCOTT@ORA12CR1> variable ename varchar2(20)
SCOTT@ORA12CR1> variable sal number
SCOTT@ORA12CR1> exec :empno := 7934; :ename := 'MILLER'; :sal := 1300;
PL/SQL procedure successfully completed.
Now in addition to simply querying the values and verifying that they have not been changed, we are going to
lock the row using FOR UPDATE NOWAIT . The application will execute the following query:
SCOTT@ORA12CR1> select empno, ename, sal
2 from emp
3 where empno = :empno
4 and decode( ename, :ename, 1 ) = 1
5 and decode( sal, :sal, 1 ) = 1
6 for update nowait
7 /
EMPNO ENAME SAL
---------- ---------- ----------
7934 MILLER 1300