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

5 from dept
6 where deptno = :deptno;
7 end;
8 /
PL/SQL procedure successfully completed.
which we can see is currently
EODA@ORA12CR1> select :deptno dno, :dname dname, :loc loc, :last_mod lm
2 from dual;
DNO DNAME LOC LM
------- ------------ ---------- ----------------------------------------
10 ACCOUNTING NEW YORK 15-APR-2014 07.04.01.147094 PM -06:00
would use this next update statement to modify the information. The last line does the very important check to
make sure the timestamp has not changed and uses the built-in function TO_TIMESTAMP_TZ ( tz is short for time zone )
to convert the string we saved in from the SELECT statement back into the proper data type. Additionally, line 3 of the
UPDATE statement updates the LAST_MOD column to be the current time if the row is found to be updated:
EODA@ORA12CR1> update dept
2 set dname = initcap(:dname),
3 last_mod = systimestamp
4 where deptno = :deptno
5 and last_mod = to_timestamp_tz(:last_mod, 'DD-MON-YYYY HH.MI.SSXFF AM TZR' );
1 row updated.
As you can see, one row was updated, the row of interest. We updated the row by primary key ( DEPTNO ) and
verified that the LAST_MOD column had not been modified by any other session between the time we read it first and
the time we did the update. If we were to try to update that same record again, using the same logic but without
retrieving the new LAST_MOD value, we would observe the following:
EODA@ORA12CR1> update dept
2 set dname = upper(:dname),
3 last_mod = systimestamp
4 where deptno = :deptno
5 and last_mod = to_timestamp_tz(:last_mod, 'DD-MON-YYYY HH.MI.SSXFF AM TZR' );
0 rows updated.
Notice how 0 rows updated is reported this time because the predicate on LAST_MOD was not satisfied. While
DEPTNO 10 still exists, the value at the moment we wish to update no longer matches the timestamp value at the
moment we queried the row. So, the application knows that the data has been changed in the database, based on the
fact that no rows were modified—and it must now figure out what it wants to do about that.
You would not rely on each application to maintain this field for a number of reasons. For one, it adds code to
an application, and it is code that must be repeated and correctly implemented anywhere this table is modified. In
a large application, that could be in many places. Furthermore, every application developed in the future must also
conform to these rules. There are many chances to miss a spot in the application code and thus not have this field
properly used. So, if the application code itself isn't responsible for maintaining this LAST_MOD field, then I believe that
the application shouldn't be responsible for checking this LAST_MOD field either (if it can do the check, it can certainly