Implicit Commits in APEX

The catalyst

It never fails.. Just when you think you know something well you learn something new. Roel recently blogged about having to hunt down a strange bug that was ultimately related to a missing commit. When I looked over his post I didn't understand why he needed the commit. I had written similar code in the past that didn't have a commit and it worked fine. What was the difference? Why the inconsistency???

These questions piqued my interest and I was surprised to learn what I did while trying to answer them. It all has to do with when APEX issues commits implicitly. What follows is an explanation of what I found. My hope is that this post will help educate APEX developers so that we can better tame our applications. This post is undoubtedly incomplete and I will make updates as I learn more. Also, this post is based on testing performed with APEX 4.1.1.00.23. The behavior observed will most likely change with a future release of APEX (more details on that below) and may have been different in the past.

The riddle

To understand why implicit commits matter one must first know how what transactions are and how Oracle handles them. When I was new to Oracle I attended a local user group meeting and the president of the group asked a relatively simple question:

What starts and ends a transaction in Oracle? 

The room was full of Oracle professionals and but not a single hand went up. This is not good. The question deals with some of the fundamental concepts of database development, such as transaction and concurrency control. A lack of knowledge here will inevitably manifest itself in the form of bugs. These bugs will be hard to find and perhaps harder to fix (if they made it into production code).

Background details

If you do not know the answer to that question, then please, pretty please with a cherry on top, read Part III of the Oracle Database Concepts Guide which covers Oracle transaction management. This is two chapters which should only cost you 1 morning and 1 or 2 cups of coffee:

• Chapter 10, "Transactions"
• Chapter 9, "Data Concurrency and Consistency"

The part in chapter 10 that describes what ends a transaction is interesting. The first bullet is really the meat and potatoes of it:

A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.

In a commit, a user explicitly or implicitly requested that the changes in the transaction be made permanent. Changes made by the transaction are permanent and visible to other users only after a transaction commits. 

If you're the type of person that shies away from COMMITment, then that word "permanent" probably makes you uneasy. It should, as that's exactly what it means! Okay, you could get help from your DBA or make use of Oracle Flashback technology to undo committed work, but you get the point...

Implicit COMMITs in APEX

Now, knowing how serious the commit statement is, you'd probably like to get as much control as possible over when that statement is issued, right? Well unfortunately you may have less control then you'd like in APEX - at least for now. APEX currently issues implicit commits at the following points:

1. On load, after a page finishes rendering 
2. On submit, before branching to another page
3. On submit, if one or more validations fail, before re-rendering the page
4. After a PL/SQL process that contains one or more bind variables has completed
5. After a computation
6. When APEX_UTIL.SET_SESSION_STATE is called
7. When APEX_MAIL.PUSH_QUEUE is called

The Expected

Point 2 in the list is perhaps what most people would expect with APEX. Usually, when a page is submitted in APEX, data is validated, processes are executed, and then a branch takes the user to the next page. If an exception occurs at any point a rollback is performed and the error message is displayed. However, if everything completes without an exception being raised then a commit is issued before the next page is rendered. 

A page render works very much like a page submit in that a commit (point 1) is added at the end if there are no exceptions. The commit for both the render and the submit phases are made obvious to developers if they run a page in debug mode. The second to the last line in the debug output will read "Final commit".

The other points in the list are not very well known and can lead to bugs if not properly understood. One point deals with validations, many of the others deal with updating session state in one form or another, and the last one is related to an API call I grouped under "other". Hopefully the "other" section will not grow but it's there just in case.

The Unexpected - Validations

When a page is submitted for processing it is possible to run processes prior to validations. Such processes are common in manual tabular forms where data is moved from the web page to an APEX collection prior to performing validations. This is the type of process that lead to this post.

In Roel's case, his process didn't contain a commit statement (one was added later). Also, his page didn't contain APEX validations as the data was validated via stored procedures in the database that worked as the data was moved from the collection to regular tables for permanent storage. If any data integrity rules were broken then an exception was raised and the page was re-rendered with the error message. This type of thing will become more and more popular using the APEX_ERROR API which gives us lots of control over handling exceptions in APEX.

Had the integrity rule been enforced via a standard APEX validation, APEX would have added an implicit commit (point 3) before the page was re-rendered to display the error message. In the case of an exception being raised APEX performs a rollback before re-rendering the page. This is why Roel had to add the commit.

The Unexpected - Session State

Points 4, 5, and 6 could be summed up by saying that when session state is updated, APEX performs an implicit commit. This was very surprising to learn. All of these assume that no exceptions are raised although the timing of the implicit commit can vary.

If a computation or call to APEX_UTIL.SET_SESSION_STATE completes successfully then you've committed your work up to that point. The only workarounds here would be to avoid computations and calls to APEX_UTIL.SET_SESSION_STATE. If you simply most make the call to SET_SESSION_STATE  then you could wrap the call in a procedure that uses PRAGMA AUTONOMOUS_TRANSACTION. I highly discourage the use of autonomous transactions (unless used for error logging) but if the wrapper only calls SET_SESSION_STATE then it should be safe.

In the case of a PL/SQL process that references bind variables, it is not until the process completes successfully that APEX pushes the updated values back into session state and then performs the implicit commit. Because of this, if you can consolidate your page processes into a single process that only uses bind variables and avoids the other triggers for implicit commits, then you will have atomic control of your transaction.

The Unexpected - Other

The only other case I've confirmed, thus far, that performs an implicit commit is point 7, when a call is made to APEX_MAIL.PUSH_QUEUE. Of course the easiest way to avoid this issue is to simply not make the call. Mail is pushed automatically via a scheduled job that runs every 5 minutes by default. If, however, you need to send your email immediately the only real workaround is similar to that of calling APEX_UTIL.SET_SESSION_STATE, using a wrapper procedure that uses PRAGMA AUTONOMOUS_TRANSACTION over a direct call.

Future enhancements

The APEX team at Oracle recognizes the lack of control we currently have over transactions in APEX and plans to address the issue in a future release of the product. With APEX 4.2 , there will be a new Compatibly Mode attribute (found in the application definition) that could be used to fix this behavior safely without affecting existing applications. However, without a crystal ball, it's impossible to know when we'll see any changes to the current functionality.

Many Thanks

I'd like to thank the many people that have contributed, in some form or another, to the content of this post. This includes...

• Roel Hartman
• Joel Kallman
• Patrick Wolf
• Kris Rice
• Martin D'Souza