Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
    
And this field is always part of the table, it's a Pseudocolumn? –  leeand00 Aug 15 '13 at 14:19
    
    
Really? I certainly wouldn't have gone in that direction. A fair few pitfalls exist. –  Phil Aug 15 '13 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. –  David Aldridge Aug 16 '13 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 Aug 16 '13 at 12:03
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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