Can someone show me an example of an optimistic offline lock in
an Oracle Stored Procedure?
2 Answers
Alas, the ora_rowscn approach is not going to work very well. It still enables "lost updates".
I have my information from AskTom: ORA_ROWSCN for optimistic locking
Copied example here to anticipate dead links...
set echo on
drop table t;
create table t ( x int primary key, name varchar2(30) ) rowdependencies;
insert into t values ( 1, 'john' );
insert into t values ( 2, 'mary' );
commit;
variable ora_rowscn number
set autoprint on
exec select ora_rowscn into :ora_rowscn from t where x = 2;
set echo off
prompt in another session issue:
prompt variable ora_rowscn number
prompt set autoprint on
prompt exec select ora_rowscn into :ora_rowscn from t where x = 2;;
prompt and come back here and hit enter...
pause
set echo on
update t set name = 'beth' where x = 2 and ora_rowscn = :ora_rowscn;
set echo off
prompt in another session issue:
prompt update t set name = 'sally' where x = 2 and ora_rowscn = :ora_rowscn;;
prompt and come back here and hit enter...
pause
commit;
set echo off
prompt now commit in the other session and notice your lost update :(
What you should do IMHO, is to add an extra column (eg: Version_Number) to your table where you set the nextval of a sequence on update of a record (using a trigger, or do this in the package code).
WorkFlow: Client queries some rows and obtains the data with the version_Number. When updating this data he needs to check the version_number with the current version_number in the record. If they differ, no row will be updated and you should return an error to the client (No_Records_Found).
create table t ( x int primary key, name varchar2(30), version_Number number);
create sequence s_t start with 1 increment by 1;
insert into t values ( 1, 'john', s_t.nextval);
insert into t values ( 2, 'mary', s_t.nextval);
commit;
select * from t;
You see something like this:
X NAME Version_Number
1 john 1
2 mary 2
.
-- first session
update t set name = 'beth', version_number = s_t.nextval where x = 2 and version_number = 2;
-- second session
update t set name = 'sally', version_number = s_t.nextval where x = 2 and version_number = 2;
-- First session
commit;
-- Second session returns with no updated records...
As I said, you should notify the client that there was no record updated, but this could have 2 reasons:
- outdated record
- deleted record
In both cases the client has to refresh this record again and determine what to do next.
Typically I think that systems would select the ora_rowscn value when they read the data, and when they write it back they would specify that value as part of the update statement. If no rows are updated then either the record has been deleted or the record has changed.
-
And this field is always part of the table, it's a Pseudocolumn?– leeand00Commented Aug 15, 2013 at 14:19
-
-
Really? I certainly wouldn't have gone in that direction. A fair few pitfalls exist.– PhilᵀᴹCommented Aug 15, 2013 at 19:04
-
It's how Oracle Apex does it, I believe, and SQL*Developer also. Hence having to set row dependencies on tables. It's a pretty lightweight approach, and very robust. Commented Aug 16, 2013 at 7:34
-
So I just pull in what version it is in my SELECT, and then later when the user edits it I add the
ora_rowscn
to the WHERE clause and if it matches it'll update the rows, if not it won't...pretty slick.– leeand00Commented Aug 16, 2013 at 12:03