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

As you can see, the hash is just some number. It is the value we would want to use before updating. To update
that row, we would lock the row in the database as it exists right now, and then compare the hash value of that row
with the hash value we computed when we read the data out of the database. The logic for doing so could look like
the following:
EODA@ORA12CR1> exec :dname := lower(:dname);
PL/SQL procedure successfully completed.
EODA@ORA12CR1> update dept
2 set dname = :dname
3 where deptno = :deptno
4 and ora_hash( dname || '/' || loc ) = :hash
5 /
1 row updated.
EODA@ORA12CR1> select dept.*,
2 ora_hash( dname || '/' || loc ) hash
3 from dept
4 where deptno = :deptno;
DEPTNO DNAME LOC HASH
---------- ---------- ---------- ----------
10 accounting NEW YORK 2818855829
Upon re-querying the data and computing the hash again after the update, we can see that the hash value is
different. If someone had modified the row before we did, our hash values would not have compared. We can see this
by attempting our update again, using the old hash value we read out the first time:
EODA@ORA12CR1> update dept
2 set dname = :dname
3 where deptno = :deptno
4 and ora_hash( dname || '/' || loc ) = :hash
5 /
0 rows updated.
As you see, there were zero rows updated, since our hash value did not match the data currently in
the database.
In order for this hash-based approach to work properly, we must ensure every application uses the same approach
when computing the hash, specifically they must concatenate dname with ' / ' with loc - in that order. To make that
approach universal, I would suggest adding a virtual column to the table (in Oracle 11 g Release 1 and above) or using
a view to add a column, so that the function is hidden from the application itself. Adding a column would look like
this in Oracle 11 g Release 1 and above:
EODA@ORA12CR1> alter table dept
2 add hash as
3 ( ora_hash(dname || '/' || loc ) );
Table altered.