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.
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.
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.
- 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.
- 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.