Cloud

O SpecGold OracleBusIntApps7 clr

 Gcloud

 

   Call us now 

  Manchester Office

  +44 (0) 8450 940 998

 

  

 

John's Blog

This is my blog of various topics, from things that I think might be useful to others, to things that I just find interesting personally. If you have any comments or questions on any of my posts then please do ask - any positive contribution is very much welcomed.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.
  • Team Blogs
    Team Blogs Find your favorite team blogs here.
  • Login
    Login Login form

Optimistic Row Locking in Oracle Application Express (APEX)

  • Font size: Larger Smaller
  • Subscribe to this entry
  • Print

As is common with stateless web-based applications, Oracle Application Express (APEX) utilizes an Optimistic locking mechanism when updating data via the built in Automatic Row Processing functionality. When a row is read from the database an MD5 checksum is generated. When the record is written back, the MD5 checksum is regenerated prior to writing - if the result now differs, we know that the record on the database must have changed. However developers often find that the automatic processing is not powerful of flexible for their needs and instead write custom PL/SQL processes to perform their updates. The problem with this however is that such procedures very often look like this:

Procedure update_row(p_id In xxMyTable.id%Type, p_value In xxMyTable.value%Type)
Is
Begin
  Update xxMyTable
     Set value=p_value,
         last_update_date=Sysdate,
   Where id=p_id
     And value!=p_value;
End update_row;

The clear problem here is that there is no protection against a situation like the following, whereby two users simultaneously modify a record and the user who saves first ends up losing their changes.

Locking Issue

As an aside, non web-based tools used a Pessimistic locking mechanism, whereby a row is locked when a user begins modifying it (via a Select For Update Nowait statement), and the lock is then released at the point of commit. Any other user then attempting to modify the record (and thus obtain a lock) is refused.

So to implement optimistic locking, we need to do so manually and the purpose of this blog is to illustrate one mechanism for doing so, which builds upon the method used by Oracle E-Business Suite self-service screens. This method stores a value against each row - object_version_number (OVN) - which is read at the point the record is read. The user changes the record, and then at the point of saving, the value is re-read from the table. If this is different from what was was read originally then we know the record has changed. If it is the same, we are ok to make the update and we then increment the OVN.

OVN Mechanism

What is critically important here however is to realise that even now the update routine can be called multiple times and we need to recognise that each session maintains a consistent view of the data unless the other session commits. So we could still be in a situation where two users read a value of X from a the table and both then call our update routine. If both read the value from table before either commits then the value read will be the same for each (i.e. the same value that was read when the record was loaded into the form) and thus the transaction will continue. We are in the same situation as before. So what we need to do here is obtain a lock on the records when we read the OVN. We are marking our intention to update the records. Therefore when the second transaction attempts to obtain a lock on the same row, they are blocked from doing so. We do this in the same way as explained previously in the optimistic example.

Procedure update_row(p_id In xxMyTable.id%Type, p_value In xxMyTable.value%Type, p_ovn In xxMyTable.object_version_number%Type)
Is
  e_record_changed  Exception;
  ln_ovn            xxMyTable.object_version_number%Type;
Begin
  Select object_version_number
    Into ln_ovn
    From xxMyTable
   Where id=p_id
   For Update Nowait;
   
  If ln_ovn != p_ovn Then
    Raise e_record_changed;
  End If;
   
  Update xxMyTable
     Set value=p_value,
         last_update_date=Sysdate,
   Where id=p_id,
         object_version_number = object_version_number + 1
     And value!=p_value;
End update_row;

We can now be sure that a user cannot retrieve a record from the database, modify it, and be affected by changes made by another user at the same time. If such a situation does occur then one of two things will happen.

  1. An ORA-00054 exception will be raised alerting the user that an attempt to obtain a lock could not be satisfied. This occurs when the second session attempts to obtain the lock prior to the first session committing its changes.
  2. Our custom e_record_changed exception will be raised. This occurs when the first session has committed its changes prior to the second session attempting to obtain a lock.

With regards to the (1), we may actually want to catch that exception and re-raise it as something a bit more user friendly (such as "Warning - another user is editing this record"). It should go without saying that this rule equally applies to Delete operations as well as Updates. Both procedures should take the OVN that was read at point of record loading as a parameter.
So if you are writing custom table handler routines using a web-based tool such as Oracle APEX in a multi-user environment (however it's good practice regardless) then it is imperative you consider and cater for such situations. Users become very frustrated when data they know they saved suddenly gets overwritten or vanishes completely, and they will soon lose faith in your system if this is allowed.

Last modified on
Tagged in: APEX
in Technical Hits: 5386 0 Comments
0
My primary area of interest and expertise is Oracle E-Business Suite, particularly Foundation, Human Resources, Payroll, Time and Labor, Warehouse Management and Inventory. Whilst I prefer to focus on functional aspects I do have a keen interest in technical areas too such as SQL tuning/the optimizer, Application Express (APEX), Unix (particularly Linux) and general development.


Comments

  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest
Guest Tuesday, 24 October 2017