Locking and Latching - Expert Oracle Database Architecture - page 209

The application you want to implement optimistic concurrency control would need only to save the value of this
additional column, not all of the before images of the other columns. The application would only need to verify that
the value of this column in the database at the point when the update is requested matches the value that was initially
read out. If these values are the same, then the row has not been updated.
Let's look at an implementation of optimistic locking using a copy of the SCOTT.DEPT table. We could use the
following Data Definition Language (DDL) to create the table:
EODA@ORA12CR1> create table dept
2 ( deptno number(2),
3 dname varchar2(14),
4 loc varchar2(13),
5 last_mod timestamp with time zone
6 default systimestamp
7 not null,
8 constraint dept_pk primary key(deptno)
9 )
10 /
Table created.
Then we INSERT a copy of the DEPT data into this table:
EODA@ORA12CR1> insert into dept( deptno, dname, loc )
2 select deptno, dname, loc
3 from scott.dept;
4 rows created.
EODA@ORA12CR1> commit;
Commit complete.
That code re-creates the DEPT table, but with an additional LAST_MOD column that uses the TIMESTAMP WITH TIME
ZONE data type. We have defined this column to be NOT NULL so that it must be populated, and its default value is the
current system time.
This TIMESTAMP data type has the highest precision available in Oracle, typically going down to the microsecond
(millionth of a second). For an application that involves user think time, this level of precision on the TIMESTAMP is
more than sufficient, as it is highly unlikely that the process of the database retrieving a row and a human looking at it,
modifying it, and issuing the update back to the database could take place within a fraction of a second. The odds of
two people reading and modifying the same row in the same fraction of a second are very small indeed.
Next, we need a way of maintaining this value. We have two choices: either the application can maintain the
LAST_MOD column by setting its value to SYSTIMESTAMP when it updates a record, or a trigger/stored procedure can
maintain it. Having the application maintain LAST_MOD is definitely more performant than a trigger-based approach,
since a trigger will add additional processing on top of that already done by Oracle. However, this does mean that you
are relying on all of the applications to maintain LAST_MOD consistently in all places that they modify this table. So, if
each application is responsible for maintaining this field, it needs to consistently verify that the LAST_MOD column was
not changed and set the LAST_MOD column to the current SYSTIMESTAMP . For example, if an application queries the row
where DEPTNO=10:
EODA@ORA12CR1> variable deptno number
EODA@ORA12CR1> variable dname varchar2(14)
EODA@ORA12CR1> variable loc varchar2(13)
EODA@ORA12CR1> variable last_mod varchar2(50)
EODA@ORA12CR1>