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

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

■ Why did we use “ decode( column, :bind_variable, 1 ) = 1 ”? it is simply a shorthand way of expressing
“ where (column = :bind_variable OR (column is NULL and :bind_variable is NULL) ”. You could code either
approach, the decode() is just more compact in this case, and since NULL = NULL is never true (nor false!) in sQL, one of
the two approaches would be necessary if either of the columns permitted NULLs .

Note