0

Can someone show me an example of an optimistic offline lock in an Oracle Stored Procedure? Optimistic Offline Lock

2 Answers 2

2

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.

2

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.

6
  • And this field is always part of the table, it's a Pseudocolumn?
    – leeand00
    Commented 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.
    – leeand00
    Commented Aug 16, 2013 at 12:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.