Re: Usage of RowId... -- optimistic locking

From: Saad Ahmad <saad.ahmad_at_mchugh.com>
Date: 1998/04/16
Message-ID: <01bd68e1$f70d0d80$2207030a_at_sahmad-pc.mfa.com>#1/1

Consider table tab1

It has columns
PK_COL
COL1
COL2 For this support, we will add another column update_version number null;

trigger tab1_br before insert or update of pk_col, col1, col2 (NOT update_version)
for each row
if inserting then

• we are always ok here proc_update_version ( :new.update_version ); else
• now we have a problem. did someone else change
• this row while i had the data on the form?
• i cant query this table because i am in before-row trigger add_to_a_plsql_table ( :new.pk_col, :new.update_version ); end if; end trigger

trigger tab1_bs after update (statement level) begin

   loop on plsql_table_of_before_row

      select update_vrsion into :v_version from tab1 where pk = pk_from_tab;

• this will give us the u_version for this row from the table.
• if no one changed it on us, while we were on the row, we should still
• have the value that is stored in the array if v_version != plsql_table.update_version then let package.msg := 'Information Changed for '|| pk_info raise a_named_exception_bound_to_20001; else
  • we know that we are ok now update tab1 set update_version = proc_update_version ( plsql_tab.version ) where pk = plsql_tab.pk;
  • dont worry the beforfe statement won't fire again end if; end loop; end trigger;

procedure proc_update_version ( io_update_version (in/out) ) is begin
if io_update_version is null
then
  io_update_version := 0;
else
  io_update_version := mod ( io_update_version, 100000 ); end if;
end

CLIENT SOFTWARE

---

Forms or PowerBuilder or whatever ...

Create a centralized routine say AFTER_STORE, that does the following if ( sqlcode_from_oracle != 0 )
then

   if sqlcode = -200001

      show_message_to_user ( package.msg [from PL/SQL package] )    end if
   rollback;
else

   commit;
endif

Scenerio 1 - Update was supposed to succeed Retrieve the data for a PK value ( say the u_version was 10 and PK PK1 ) Update it on the form
Store it (implying that client software issues the actual update statement)

• before row fires, stores in the pl/sql array PK1/10
• <does whatever else the trigger was supposed to do>
• after statement eventually fires
• table queried and the u_version was still 10, so make it 11 call AFTER_STORE

Scenerio 2 - Update was supposed to fail Retrieve data for PK value (u_version 11, PK PK1) Update it on the form

	In another session retrieve PK value ( u_version 11, PK PK1)
	Update 
	Store (u_version now 12 because of triggers)
	commit
Store it 


	-	before row saves PK1/11 in the array


	-	after statement queries tab1 and sees that value is 12 now so raise

error
AFTER_STORE will show the message and rollback

Summary

---

The triggers seem to be a lot of work, but they can be generated quite easily and latest versions
of Oracle (7.3.4 or 8) do allow for multiple triggers on same event. So the creation of the triggers
should not pose a huge problem.

Some FrontEnd softwares may already allow optimistic locking, but the trigger approch has the
potential advantage of working with all technologies.          Received on Thu Apr 16 1998 - 00:00:00 CEST