Duplicate page submits cause double database entries

No more double entries, please

In many applications the result of a page submit is the creation of one or more lines in some database table(s).
When these processes become heavy and have to take a lot of business rules into account and that perhaps in combination with sometimes not optimal network trafic, it may happen that the user doesn't see that APEX is processing his stuff.
As a result he or she may become impatiant and start hitting that Save button over and over again ...

When this results in multiple occurance of the record in the database, this creates data inconsistency. Something that every developer is - quite rightfully - scared as hell for.

There are some ways to prevent this behaviour from happening.

This blogpost is about preventing those double entries. But while doing my research and in fact the winning tip for the solution was coming from my good friend and APEX mentor Dimitri, I found a very nice extra. 

Did you, just like me, wonder how the APEX Builder has this spinning wheel showing up when saving your changes? And could you, just like me, not figure out how that was done? Well ... just read on!

So here's my business case.

A reasonably complex APEX page has a couple of buttons:

1. Cancel - needs no explanation
2. Save - simply writes the entered date to several tables in the database
3. Finalize - does the same things as "Save", but also starts an approval workflow process

In our case, this Finalize was the issue.

Kicking off that workflow requires some business logic, some validations, well, it's a process that wasn't done in milliseconds.

Nothing was done on the APEX page to prevent the page from submitting multiple times. And nothing was done to show the end user that a process was running.

The only thing: when the process was done, then a message was shown, something like "Well done, your request has been sent to your manager".

The first week after go live of the system, we noticed that our end users were much more impatient then our test users. Because they didn't immediately saw the result of the 'press on the button', they started hitting that button multiple times. And so submitting the page multiple times. And so creating the same record multiple times.

So let me show you in this very much simplified sample what was the first setup of the APEX application.

The old way

Nothing was done to prevent double entries at all.
Base table used is hr_employee.  And it contains some very basic personnel information.

Only 1 record in there before the exercise:

On this table a form with report was created.

So I start hitting this Apply Changes button like crazy and it's almost impossible to mimic the customer's case with this simplified example.

But believe me, it happened and let say Mr. McEnroe is created twice in the db.

An important setting to keep in mind in this example is on page level

This setting can allow or prevent the page to be resubmitted multiple times. This feature was called into place to prevent a page to be resubmitted e.g. after a page reload in the browser. Or when hitting the back and forward button of your browser.

So ... there is our first clue!

The button (Save and Apply Changes) are both performing a Page Submit.

One step up and prevent page from being reposted

So I could redo all the steps as described above, with this option set to "No - Prevent this page from being re-posted". But I'm not gonna, because you will not see the difference, except for this one single setting:

In most situations this setting will save you. Although, if you remember the customer case, this setting was already set to prevent the re-posting.

So our example wasn't about being reposted or not. It really was due to multiple submissions. It was like the process was launched a second time, before the first time had come to an end.

There are other reasons why we could assume this, because at some point in the process it was checked if a request could be approved and then some status was changed so the same person could never approve twice the same request.  Because this little piece of functionality was in there and it had never occurred during test cycles, we were pretty confident that we had built a safe system.

Until ... one week after go live the customer came back to us and said: "multiple occurrences of the same record exist, created within less than a second from one and another. Please help!!!"

And then, the main test user had a brilliant idea. She sat down behind her computer, opened the specific page, entered some data and pointed the button and started clicking it like mad.

And there it was ... not two or three, but at least nine or ten records were saved to the db. 

So we needed another solution. And when I ran out of ideas, I called a help line.

Disable the button after having pressed it

The solution is as easy as it is simple. Prevent the the button from being pressed more then once.

In example one and two, the process was launched upon page submit. The button basically submitting the page.

Compared to both examples above, the change is with the button definition. No longer use the Page Submit, but set its behaviour to Defined by Dynamic Action.

Note: in this example we need the Database Action. In many cases you will not need that, but you will launch your actions via a page process.

 Now create the dynamic action:

First DA True Action:

Disable the button, making sure it is NOT fired on Page Load (by default it will fire on Page Load).

Finally add a second DA True Action:

To stick to the original behaviour, it's quite simple: just perform a Page Submit and set the Request to the Button name (Create). This is important, otherwise the defined page process that was previously directly linked to the button, will not be fired.

For the Page Submit obviously the default is set that the action should not fire on Page Load.

And look what came with this solution for free!

Yup ... the spinning wheel. There is that very nice extra that I was talking about in the introduction.

From now on - until further notice - I believe this will be my standard way of building an APEX page that submits something to the DB. 

I will no longer submit the page directly from the button, but will have a dynamic action do this, after I have disabled the button.

Also when you stay on the same page after the process is done, you do not need to enable the button again. Your Page Load will do this for you.

For me this really was an eye opener. For so many years I was used to write as much as possible in page processes that got fired on Page Submit.

I even made a habit out of it to - when possible of course - only write something to the DB after a page submit.

And it never occurred to me to do this in another way than via the button behaviour "Submit Page".

As simple as it may seem and as logic as it in fact is ... I only discovered this recently. I don't know if the APEX Builder is using the same method, but it looks the same. And that's good enough for me ;-)

Happy to share ...