Thank you. Test case worked in showing that ORA_ROWSCN does not work for optimistic locking.

Also, others may want to look at: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:22948373947565 where ORA_ROWSCN is talked about, though that conversation, to me, sounded like this was a bug that might be fixed by now.

Hi Tom, can we say that optimistic locking with ora_rowscn is safe if we add a
'select for update' just before the update ?

Hello Sir,

I have below question with respect to your above example for lost update to OP

(1) In your above example when we commit in first session did not ora_rowscn change so that the commit
in the second session fail?

(2) I see from example that ora_rowscn does not change and hence there is a lost update so when did ora_row_scn change?


Thanks

>> for update has the same issue as update does. so, no, it would not work in all cases.

I understand that the 'select for update' has the same issue because it does write consistency in the same way as the update.
But then, the following update should be safe: row has been locked and ora_rowscn is now evaluated.

It is like doing 'manually' an update restart each time, just in case. Or am I missing something ?

Yes, this is what I mean. Thanks for your opinion on that.
Regards,
Franck.

Hi Tom,

Our data is not only accessed/modified through the web (e.g. APEX), but also through client-server applications (e.g. Forms or SQL+). All clients use the same transactional API to insert/update/delete data.

I was under the impression that a combination of pessimistic locking (select .. for update) and optimistic locking (e.g. through ora_rowscn) is a good solution for this kind of applications.

Example:

drop table t purge;
create table t rowdependencies as select object_id, object_name from user_objects;
alter table t add constraint t_pk primary key (object_id);

create or replace procedure update_t
   (p_object_id   number,
    p_object_name varchar2,
    p_scn         number) 
is
  l_scn number;
begin
  select ora_rowscn
    into l_scn
    from t
   where object_id = p_object_id
     for update nowait;

  if l_scn = p_scn then
    update t set object_name = p_object_name where object_id = p_object_id;
  else
    raise_application_error(-20000,'Record is changed');
  end if;
end update_t;
/

Suppose procedure update_t is the transactional API used in web (stateless) + client-server (stateful).

Can you please explain the "performance hit you are not willing to take" or other issues you think might happen with this approach?

Regards,
Wouter

Hi Tom,

Thanks for confirming that this approach is "safe".

I read something about the performance hit in:
Followup September 30, 2010 - 3pm Central time zone:

I don't see what you mean by:

... But then, the following update should be safe: row has been locked and
ora_rowscn is now evaluated. ...

are you saying

a) select for update the row using ora_rowscn (or not)
b) update the row using ora_rowscn


If so, yes that would work - but it would be a performance hit I would not be willing to take. And it also means you deviate from a standard approach - you have a "workaround" and I'd rather not use a workaround when viable alternatives (that do not require a workaround) exist.

update t
  set ...., version_counter = version_counter+1
 where primary_key = p_PRIMARY_KEY
   and version_counter = p_VERSION_COUNTER_AS_READ_OUT_INITIALLY

Hi Tom,
   I read your link about restart and I did a test:

SQL> create or replace trigger t_row_trigger before update on t_row for each row
  2  begin
  3  dbms_output.put_line('fired');
  4  end;
  5  /
SQL> select *  from t_row;

         X          Y
---------- ----------
        10         88
         2          3
         3          4

Then in session 1:

SQL> update t_row set y=89 where x=10;
fired

1 row updated.

In session 2 : SQL> update t_row  set x=22 where y=88;
and it is normally blocked by session 1, and I assume that there should be a update restart since y was modified by session 1. however when i commit in session 1, i supposed to see 2 fired in session 2, but only 1 fired:
SQL> update t_row  set x=22 where y=88;
fired

0 rows updated.

is it expected?

Thanks,

Hi Tom,
I was reading your blog:
---
If we just skipped this record at this point and ignored it, then we would have a nondeterministic update. It would be throwing data consistency and integrity out the window. The outcome of the update (how many and which rows were modified) would depend on the order in which rows got hit in the table and what other activity just happened to be going on. You could take the same exact set of rows and in two different databases, each one running the transactions in exactly the same mix, you could observe different results, just because the rows were in different places on the disk.
---

Can you elaborate more on the nondeterministic outcome if oracle does not restart?

Thanks,

Hi Tom,

Looking for your kind advise in the following scenario

1. Application A writes (insert / updates) data into table (T1) and maintains an update_date timestamp (SYSDATE). This application is goldengate and there are 5 parallel threads running continuously processing records.

2. Application B (ETL program for real-time active warehouse) reads data from table (T1) for the incremental changes and carries out transformations.

The application is suffering from the standard problem of missing records since they maintained a max update date when they read the records in (2) above and read only after this timestamp. This meant any transactions that started before they started reading but not committed were missed.

They they decided to re-process by adding a condition (- 30 mins) but they landed up in another problem because of the way the application was written. So they had to avoid duplicate processing

They may occasionally get a peak load of 1,000,000 records in a 5 min interval. The ETL program is scheduled every 5 mins.

What is the best option considering its a warehouse because I was a little confused on the various options and which ones fits when?

1. Ruled out v$transaction usage since it can also re-process records , or
2. ora_rowscn, or
3. version_control, or
4. trigger

Thanks
Prashant

Hi Tom
Thanks for your prompt response.

If I understand correctly the best option in the order of performance

1) Let goldengate handle the transformations, remove the ETL
2) Use v$transaction
3) Mark the records or delete the records as its processed (this avoids duplicate processing but can introduce locks if the same record is also updated by goldengate, right?)

Please correct my understanding.

Thanks
Prashant