Optimistic Locking Using ORA_ROWSCN
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),
tel NUMBER)
ROWDEPENDENCIES
/
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
FROM users
WHERE id = 1;
3) Your update statement would then look like this:
UPDATE users
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
THEN
RAISE_APPLICATION_ERROR (-20002, 'Optimistic Locking Violation');
END IF;
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.
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),
tel NUMBER)
ROWDEPENDENCIES
/
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
FROM users
WHERE id = 1;
3) Your update statement would then look like this:
UPDATE users
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
THEN
RAISE_APPLICATION_ERROR (-20002, 'Optimistic Locking Violation');
END IF;
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.
Comments
I am playing devils advocate now but the following bugs are registered against ora_rowscn in metalink.
4947170 - ORA_ROWSCN DOES NOT WORK WITH VPD POLICY ON TABLE ORA-904
4190906 - ORA_ROWSCN RETURNS NULL FROM THE ROOT NODE IN A CONNECT BY PRIOR TREE WALK
5055046 - DELETE WITH THE WHERE CONDITION USING ROWID AND ORA_ROWSCN DOES NOT SEE ROW
4103979 - ORA_ROWSCN DOES NOT RESPECT TRANSACTION INTEGRITY
5270479 - ORA_ROWSCN SHOWING INCORRECT BEHAVIOUR THEREBY VIOLATING DATA INTEGRITY
5055304 - ORA_ROWSCN NOT COMPUTED WHEN ONLY PSUEDO COLS. IN PREDICATE
3865487 - ROWDEPENDENCIES WITH ORA_ROWSCN ON AN IOT DOES NOT WORK, ROWSCN IS CORRUPTED
2746476 - INCORRECT OUTPUT FROM COUNT(*) VERSION QUERIES; PSEUDO-COLUMN NOT RECOGNIZED
Like Duncan I have also used this in a web application, and it works fine under most circumstances. Just remember to turn rowdependencies on when you create the table.