Home/ Questions/Q 742
In Process

vinish.ai Latest Questions

  • 1
  • 1
Asked: 2020-09-15T23:51:48+00:00

Pessimistic Locking in Oracle Apex

  • 1
  • 1

Hi!

Is a good practice to use pessimistic locking in Oracle Apex? For instance, if the report has a long form, which needs some time for an update. User can waste time to write and then get a notification that record is not the same as before opening form. In this case, the user should read data and populate form again.

I implemented the pessimistic locking, by adding columns eg. one for user ID who editing a record and another flag which contain 1 or 0, when the flag column is 1 and column which contain the user ID is not the ID of logged in user. The user getting alert that someone editing this record and can’t see SAVE button.

I created application processes (before header) which populate these columns when the user opens the page which contains the form. When the user submits or changes the page columns are reset. There will be cron job every 15-20 minutes, in case that user leaves the page without submits or change page.

Is my implementation of pessimistic locking good or there is a better way to do it?

Thanks in advance!

oracle apexpessimistic-lockingquestion
Share
Leave an answer

Leave an answer

Browse

6 Answers

  1. Hi Pavlos,

    I see you already asked this question on the APEX discussion forum.  It’s an interesting topic.

    Is this a common occurrence (that multiple people will attempt to edit the same row)?  What I don’t like about this is that you lock other people out for 15-20 minutes.  It’s very common for people to close a browser tab, or outright navigate away from your application.  If you don’t release this lock immediately, then you are impeding others from maintaining data in the system.

    One other comment – you should consider using an API and then using this API in APEX.  Because if you wish to share this data with some technology other than APEX, you’ll have to implement the same pessimistic-locking logic all over again.

    Joel

  2. Hi Pavlos,

    So far, your logic seems to be perfect for implementing the pessimistic locking in Oracle Apex.

    I understand that part where the user can leave the form without submitting, but you are handling it using the corn job, which is another good idea.

    I am thinking on it, and will share if I will get any other idea to do this.

    Also, expecting from other members to give their suggestions on it.

  3. You might have already tried, but I am just pointing out to prevent the below issue:

    if the report has a long form, which needs some time for an update. User can waste time to write and then get a notification that record is not the same as before opening form. In this case, the user should read data and populate form again.

    You can turn-off the Prevent Loss Update setting for the process. Below is the screenshot:

    Pessimist Locking in Oracle Apex.

    But if any changes have been made by other users will be overwritten.

  4. This answer was edited.

    Hi Vinish!

    I use custom PL/SQL process. I tried with hash values to compare data when the form is open and when the save button is pressed (optimistic locking) I think that is similar to Prevent Lost Update button set to enable, isn’t it?

    In case that I can disable Prevent Lost Update button, I think that lost update is not a good idea in my case, as you mentioned, data can be overwritten.