Wednesday, 10 January 2007

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.

2 comments:

Kris said...

Hi Dunc,
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.

muxiaoyu said...

INDIANAPOLIS – Searching for wow golda truly world of warcraft goldoriginal buy wow goldholiday cheap wow goldgift, one that wow power levelingcould bestow a wow powerlevelingbit of immortalityAge of Conan gold on a lovedAge Conan gold one or aage of conan power leveling friend?If so,aoc gold Purdue University aoc power levelinghas the goods: aoc levelingThe school iseve online iskuctioning eve iskthe naming rights toCity of Heroes influence seven newlycoh influence discovered bats City of Villains infamyand two turtles.COV infamyWinning bidders willlineage 1 adena be able to link alineage adena relative, friend or 2moons dilthemselves to an animal2moons gold's scientific Last Chaos Goldname for the ages.The first of the nineGW goldauctions began Monday, Guild Wars Goldwhen the school put up for grabsRagnarok Zeny the naming ro zenyrights to a tiny gold and black Rappelz goldinsect-munching bat found in CentralSecond Life Linden America.second life moneyThe winning bidderTabula Rasa Credit will be announced tabula rasa creditsjust beforevoyage century gold Christmas,potbs doubloon said John pirates of the burning sea goldBickham, Potbs golda Purdue professor of forestry and natural resources who discovered or co-discoveredpirates gold the nine speciesArchLord gold.He expects the auctions buy ArchLord goldto attract wide interest, EverQuest platwith the chance to include buy eq golda person's Latinized name inEverQuest gold a new species' scientificeq plat name — a tradition that dates to theeq gold mid-18th century."Unlike naming eq2 plata building or something like that,EverQuest 2 plat this is much more permanentHero online gold This will last asbuy silkroad gold long silkroad goldas we have our society.