APEX Interactive Grid: Preventing Lost Updates & Record Locking

Cards regions are a great way to present a variety of information and prior to th

I was recently asked about the Interactive Grid (IG) configuration settings that are associated with Locking and Preventing Lost updates. “Aren’t they the same thing?” I was asked…

Actually, no; they are not the same thing. However, they are both there to protect your data and therefore it is worth spending a little time to explore their true behaviour to understand them a little better.

Preventing Lost Updates

What are we trying to achieve here? As per most of my previous blogs … this is best explained with an example. The one am going to use has been taken from the Official APEX Documentation:

1. User A queries employee (EMP) records.
2. User B queries the same employee records.
3. User B updates employee details for EMPNO = 7839 and applies (commits) the changes to the database.
4. User A updates employee details for EMPNO = 7839 and is attempting to apply the changes to the database.

You guessed it … User B is on the verge of losing all their data entry work, which is going to be overwritten with the changes from User A. Therefore, it is a competition between user A and B; whoever saves first is going to lose their changes and whoever saves last is going to keep their changes.

Luckily, the IG process has a setting called Prevent Lost Updates which is set to On by default. If you really need the behaviour described in the scenario above, then you can always set it to off on your IG Process.

By enabling the Prevent Lost Updates setting it changes the above scenario around, so now the first to save gets to keep their changes, and an error message will be displayed to the user who is last to save. The last to save will now be unable to save their changes and will have to re-query their record. The IG has a Refresh Rows feature specifically for this scenario whereby the user can refresh records from the database – this picks up the latest changes from other users.

To ensure you have the Prevent Lost Updates feature fully configured, you’ll need to ensure your IG Process has Prevent Lost Updates set to Yes. Then select which Lost Update Type to use – you will find this on your Interactive Grid Attributes.

There are two types to choose from; Row Values (default) and Row Version Column. The general approach for both is the same; prevention of lost updates is achieved by re-querying the record just before saving to ensure the column values have not changed. In the case of Row Values it checks all updatable columns, and in case of Row Version Column, a single specified column is checked.

In the case of the Row Version Column; the documentation describes “a column which is incremented every time a record is updated”. Coincidently, this is exactly how I understood a version column to work, e.g. an automatic incrementing numerical column. When trying an example of this it works really well – lost updates were prevented. I even tried this with other columns of differing datatypes, e.g. When I used a LAST_UPDATED_DATE column it also worked. In an act of rebellion against the documentation, I even tried a decrementing column – this also worked and proves that all that is necessary is that the column value has not changed at the point of saving the IG.

In the case of Row Values, according to the documentation, it creates “a checksum of all updatable columns”. That sentence threw me at first when I saw Display Only columns (even Read Only columns) being included in the checksum; after a few tests, I confirmed that regardless of whether the IG column is Display Only/Read Only or not, the IG is going to update the DB with the value of that column. Therefore, when you read “a checksum of all updatable columns” take this to mean “a checksum of all Interactive Grid columns where their Source has a Type of Database Column and the Query Only attribute is set to No”.

When a Lost Update has been prevented, you’ll see this error message:

I know that many error messages have a user-friendly version; so I checked a users session and … unfortunately, it reads the same. I guess you have to hope your users are technical.

When it comes to locking rows; there’s a nifty approach when combining with the Prevent Lost Updates and the Lock Row setting. Whenever you set Prevent Lost Updates to Yes, it is going to perform a checksum of the existing DB record (based on the method and whatever you set as the PK, e.g. the ROWID) – so it needs to visit the DB to obtain these values to calculate the checksum. Whilst it is obtaining these values, if you set Lock Row to Yes, then APEX appends a “for update NOWAIT” to that select statement. Therefore the lock and the values it needs for the checksum can be achieved in one hit.

If you set Prevent Lost Updates to No and set Lock Record to Yes then APEX will still fetch the record with a “for update NOWAIT”; it just never applies the checksum.

The overhead of Prevent Lost Updates and Lock Record averaged 0.00410 seconds in my tests. Which is well worth activating to prevent lost changes.

Once again, the user is informed of the lock, and it too has a very technical error message to log with their IT department.

With APEX enforcing the NOWAIT row lock, there is no real declarative control you have of this; for example, the DB supports the WAIT integer seconds feature – should you be prepared to wait for a lock to become free (not to mention the SKIP LOCKED feature which would be undesirable in this context). APEX does not support this nor any other locking parameters declaratively.

Be aware that the NOWAIT lock cannot be applied for every interactive grid; for example, IGs based on a collection. In this scenario, it makes no sense as the APEX_COLLECTIONS object is a complex view with constructs which prohibit the FOR_UPDATE clause – namely the DISTINCT operator, CURSOR expression, set operators, group by clause and aggregate functions. Not only for collections, but the same applies to those constructs in any complex SQL you may use in your query source.

So what can you do when you encounter the above when using a collection? If collections are session specific, are you fine to disable the lock? Or maybe … you should write your own locking code?

There is actually an option to lock rows with PL/SQL Code … but it sadly doesn’t work in APEX 5.1 or 5.2EA1. Any code (it really doesn’t matter if it validates or not) is not evaluated which is a real shame and you have to use a workaround. Apparently the PL/SQL code to lock the row should be available for testing in the next EA update.

The workaround can be applied by creating a PL/SQL process on your editable IG region and having this fire before your IG process. In my tests, though I can’t combine it with an apex_error to report the error in a nicer way because the process will wait for the lock to be released even with a NOWAIT.

So how can we update a collection through an IG process? We’ll need to access the APEX_COLLECTION API, so we have to change the Target Type to PL/SQL. This is going to work for us; but can we also prevent lost updates? – Well yes, the checksum will still be generated using your selected Lost Update Type setting. Be aware it just can’t be locked due to the prohibited constructs of the APEX_COLLECTIONS view.

This brings us to views or joins. In a join over 2 tables (e.g. Employee and Job) this is a problematic and possibly requires a rethink in this approach. The question to ask yourself is what do you expect to happen when you press save? Is it suited to an Editable \Interactive Grid; could it be split into master-detail IG set or is one table (Job) just used as a lookup for another column (employee.job_id)?

Should you really need this type of set up; you can set the Target Type to table/View and choose a single table (employees for example). Setting the other columns (e.g. Job table columns) to Query only, you can still lock the (employees) table providing you use a PK on that table. You can still prevent lost updates in this method too as the lock applies to the table in the Target Type and the checksum does not apply to Query Only columns – i.e. the lock and checksum can only apply to a single table.

So there you have it; Preventing Lost Updates and Record Locking are enabled by default on your IG, making your data secure and robust. Preventing Lost Updates can adapt to your schema design by using record versioning columns and Record Locking can be disabled or manually configured. All this is performed out of the box and rapidly by the APEX Framework making developers more productive and feature focussed.

If you would like to find out more information speak to one of our Oracle APEX experts, get in touch through enquiries@dsp.co.uk or book a meeting...

Author: Matt Mulvaney

Job Title: Senior Oracle APEX Development Consultant

Bio: Matt is an experienced APEX solution designer having designed numerous complex systems using a broad range of Oracle Technologies. Building on his previous experience of Oracle Forms & PL/SQL, he is entirely focused on providing functionally rich APEX solutions. Matt promotes APEX as a software platform and openly shares best practises, techniques & approaches. Matt has a passion for excellence and enjoys producing high quality software solutions which provide a real business benefit.