Database Reference
In-Depth Information
■
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
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:
MILLER
row back, and this row will be
•
If the underlying data has not changed, we will get our
locked from updates (but not reads) by others.
ORA-00054 resource
•
If another user is in the process of modifying that row, we will get an
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.
