Locking and Latching - Expert Oracle Database Architecture - page 211

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
do the update). So, in this case, I suggest encapsulating the update logic in a stored procedure and not allowing the
application to update the table directly at all. If it cannot be trusted to maintain the value in this field, then it cannot
be trusted to check it properly either. So, the stored procedure would take as inputs the bind variables we used in the
previous updates and do exactly the same update. Upon detecting that zero rows were updated, the stored procedure
could raise an exception back to the client to let the client know the update had, in effect, failed.
An alternate implementation uses a trigger to maintain this LAST_MOD field, but for something as simple as this,
my recommendation is to avoid the trigger and let the DML take care of it. Triggers introduce a measurable amount
of overhead, and in this case they would be unnecessary. Furthermore, the trigger would not be able to confirm that
the row has not been modified (it would only be able to supply the value for LAST_MOD , not check it during the update),
hence the application has to be made painfully aware of this column and how to properly use it. So the trigger is not
by itself sufficient.

Optimistic Locking Using a Checksum
This is very similar to the previous version column method, but it uses the base data itself to compute a “virtual”
version column. I'll quote the Oracle Database PL/SQL Packages and Types Reference manual (before showing how to
use one of the supplied packages) to help explain the goal and concepts behind a checksum or hash function:

“A one-way hash function takes a variable-length input string, the data, and converts it to a fixed-
length (generally smaller) output string called a hash value. The hash value serves as a unique
identifier (like a fingerprint) of the input data. You can use the hash value to verify whether data
has been changed or not.

Note that a one-way hash function is a hash function that isn't easily reversible. It is easy to compute a
hash value from the input data, but it is hard to generate data that hashes to a particular value.”

We can use these hashes or checksums in the same way that we used our version column. We simply compare
the hash or checksum value we obtain when we read data out of the database with that we obtain before modifying
the data. If someone modified the row's values after we read it out, but before we updated it, then the hash or
checksum will almost certainly be different.
There are many ways to compute a hash or checksum. I'll list several of these and demonstrate one in this
section. All of these methods are based on supplied database functionality.

•

OWA_OPT_LOCK.CHECKSUM : This method is available on Oracle8i version 8.1.5 and up. There
is a function that, given a string, returns a 16-bit checksum, and another function that,
given a ROWID, will compute the 16-bit checksum of that row and lock it at the same time.
Possibilities of collision are 1 in 65,536 strings (the highest chance of a false positive).

•

DBMS_OBFUSCATION_TOOLKIT.MD5 : This method is available in Oracle8i version 8.1.7 and up.
It computes a 128-bit message digest. The odds of a collision are about 1 in 3.4028E+38
(very small).

•

DBMS_CRYPTO.HASH : This method is available in Oracle 10 g Release 1 and up. It is capable
of computing a Secure Hash Algorithm 1 (SHA-1) or MD4/MD5 message digests. It is
recommended that you use the SHA-1 algorithm.