Skip to Main Content

Rollback & Commit

"Commits are performed when you explicitly issue them or when you alter session state with assignment statements, "select into" queries, or OUT variable assignments to bind-variable notated page- or application-item names or when you call apex_util.set_session_state. If you don't do any of that, no commits happen until the page processing completes."

APEX commits:
  • At the end of page processing (ACCEPT processing)
  • At the end of page rendering (SHOW processing)
  • After session state is changed for page/application items by a page-process
  • After User issued 'commit' statement
So a Page Process should look like this to ensure a rollback is performed when a called pl/sql procedure/function fails: Note: The savepoint_0 construct is used to ensure that we rollback to the correct place. Otherwise, we would not notice if another commit was done somewhere in the code (because we would roll back to that one, not to savepoint_0). Now, if another commit was done somewhere in the code and we try to roll back to savepoint_0, we will get "ORA-01086 - savepoint 'string' never established in this session or is invalid", which is correct here.


Implicit Commit

Dan McGhan's article "Implicit Commits in APEX" can be found here:
His list of Apex Commit 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 article contains some excellent background information on this topic, worth a read.

And yet another good article:
His conclusion:
  • Changing a session variable causes APEX to take a commit. [...] If we move the same value into a session variable, if the value does not change, then APEX does not take a commit.

APEX_UTIL.set_session_state may or may not commit

A very interesting article titled "APEX_UTIL.set_session_state may or may not commit" by Jeff Kemp:

[...] APEX_UTIL.set_session_state will issue a COMMIT – but only if the value of the item is changed. If you happen to call set_session_state with the value that the item already has, it does nothing, and does not COMMIT. [...]

Deferred Commit

Related to the articles above: After a page/form submit, you might see something like this in the Debug Message Data:
Session State: Save form items and p_arg_values
...Session State: saved "P1082_SEARCH_PATTERN_1" new value="" (deferred commit)
...Session State: Save "P1082_SEARCH_PATTERN_2" - saving same value: "Sunshine"
...Session State: commit previous changes

So the new value for "P1082_SEARCH_PATTERN_1" is not committed! I have been caught out by this a couple of times now; it's hard to spot and even harder to debug...

A workaround (because I have still not found the root cause, grrr): Add a hidden page item to the page, place it in the first region of the page, and make sure its ID is smaller than all others. This item will "absorb" the deferred commit, so that the next items get their correct values. Not elegant but it works.
For example, on pages 1063, 1071, 1072 and 1082 of this site I needed this rather ugly solution.

Check if a Commit has occurred

If you need to check if a commit has occurred at some point during execution of your code, you can use something like this function:
The function will return true if there are no pending (i.e. non-committed) transactions within the session.
So a test run to use this, would look like this:
  • Issue a commit - or otherwise make sure that within the current session, only your test run creates transactions.
  • Create a transactions, e.g. an insert.
  • Run the suspect (i.e. possibly committing) code you want to check.
  • Call the function above - if it returns true then no transactions are pending and therefore the suspect code did indeed commit.
The user executing this function, needs the following grants: