For anyone who has manually written an Update Statement in a Web Application, the issue of Optimistic Locking always took a while to address.
The normal convention is to use a series of hidden elements for every form element that is submitted and doing an "old vs new" comparison in the Update statement to see if any valus in the database have changed.
I found the main drawback was the need to create large amounts of hidden elements that are then passed into the DML procedure for evaluation.
I came across ORA_ROWSCN in 10GR2 that reduces the amount of hidden form elements you need to submit.
Its basically a new pseudo column in that database that provides the SCN (System Clock Number for the last time the row was updated) associated with individual rows when they were read.
The basic steps to utilise this approach are as follows:
1) create table users (id number,
name VARCHAR2 (100),
address VARCHAR2 (100),
ROWDEPENDENCIES need to set as SCN by default is implemented at block level. By setting ROWDEPENDENCIES, SCN should now track at row level.
2) query that a form on page 100 is based on would look like this (note that a hidden element P100_USER_RCN should be created at page level):
SELECT name, address, tel, ORA_ROWSCN
INTO :P100_USER_NAME, :P100_ADDRESS, :P100_TEL, :P100_USER_RCN
WHERE id = 1;
3) Your update statement would then look like this:
SET id = :P100_USER_ID,
name = :P100_USER_NAME,
WHERE id = 1
AND ORA_ROWSCN = :P100_USER_RCN;
-- Check to see if update took place
IF SQL%ROWCOUNT <= 0
RAISE_APPLICATION_ERROR (-20002, 'Optimistic Locking Violation');
Of course my first advice would be let Application Express handle your DML updates if you can, but for the times when you have to manually create your own update, this may be of some help to you.
Here are some benchmarked examples of this in operation on AskTom
I also posted this in the Application Express Forum a while back. There were some good comments / additions made by others so here is the link to the post.