APEX_UTIL.set_session_state may or may not commit

Updated for APEX 5 – refer below

When should you commit or rollback a transaction? As late as possible, I would have thought, based on most of the advice in the Oracle world. You certainly want this to be predictable and consistent, at least.

Unfortunately, if you use APEX_UTIL.set_session_state in your PL/SQL process, the result is not so predictable.

Thanks to Martin D’Souza who alerted me to this. I love learning new things, but occasionally you get a bad surprise like this and it’s not so pleasant.

Test case set up – create a table with a single row, and create a simple Apex application with one page, with one region, with an item (P1_N) and a Submit button.

CREATE TABLE test (N NUMBER);
INSERT INTO test VALUES (1);
COMMIT;

TEST CASE #1

Add an On Submit process to the page which fires when the Submit button is clicked, which executes the following:

BEGIN
  UPDATE test SET n = 3;
  COMMIT;
  APEX_UTIL.set_session_state('P1_N', 1);
  UPDATE test SET n = 2;
  APEX_UTIL.set_session_state('P1_N', 1);
  ROLLBACK;
END;

What value would you expect to see in the database table now? I would have expected that the table would hold the value 3 – and indeed, it does.

TEST CASE #2

Modify the process slightly – after the second update, set the item to something different:

BEGIN
  UPDATE test SET n = 3;
  COMMIT;
  APEX_UTIL.set_session_state('P1_N', 1);
  UPDATE test SET n = 2;
  APEX_UTIL.set_session_state('P1_N', 4); --changed here
  ROLLBACK;
END;

This time, the second update to the table has been committed before we issued our ROLLBACK. The new value 2 has been saved to the database. Why?

It’s because 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. I understand why a COMMIT is ultimately necessary (Apex session state is stored in a table) – but I disagree that it’s necessary for it to commit my (potentially partial) transaction along with it.

This means that if an exception is raised somewhere in my process, the resulting rollback may or may not rollback the entire transaction, depending on whether any prior calls to set_session_state happened to COMMIT or not. This is difficult to predict and therefore makes debugging harder. Not to mention the fact that it violates the general principle of “either the whole transaction succeeds and is COMMITted, or it fails and the whole transaction is rolled back”. I’m sorry, Apex, but you should not arbitrarily commit part of my transaction without at least telling me.

Mitigations for this? I’m not sure yet. One suggestion from this forum thread was to make the procedure use an autonomous transaction. This would align it more closely to what most developers would expect, I think. Unfortunately it appears the suggestion was rejected (or put on hold indefinitely).

I’m planning on refactoring my code to shift all calls to set_session_state to as late in the process as possible; in addition, I’m thinking that I would put an explicit COMMIT prior to these calls so that my code would have more predictable behaviour. But the idea of wrapping set_session_state in a wrapper procedure with an autonomous transaction seems good to try out as well.

UPDATE for APEX 5

As of Oracle APEX 5.0, APEX_UTIL.set_session_state supports a new optional parameter, p_commit (APEX_UTIL documentation). It is defaulted to true which preserves the old behaviour (i.e. it might or might not commit).

If you set p_commit to false, the procedure will not issue any commit. This removes the need for the autonomous transaction, and leaves the responsibility for committing to the developer; if it’s called from an APEX page process, it will be committed automatically.

Build your APEX application better – do less in APEX
Proposed wrapper for APEX_UTIL.set_session_state

Comments

  1. It’s (or at least was) even worse…

    Just assigning a value to a page item in your pl/sql-code, like “:P1_N := 3;”, can cause a commit to happen. At least it did at some point, couple of years ago, when I was hit by this too.

    I love Apex. But I hate the obsure TX-model, commits and rollbacks happening all over the place. Just do a SQL-trace of a submit process and start looking for all the XTEND’s in the tracefile.

    Another ‘feature’ is that the page-render stuff usually happens in a different database session as does the page-submit stuff. So you do a page-submit, all it’s logic is executed in db-session 1. Then a branch starts the render of the next page, all it’s logic is executed in db-session 2. Just something you should be aware of.

  2. Jeff,

    This is probably working as you explained. I am just trying to think about a case where an on submit process needs to explicitly set a value of a page item using apex_util.set_session_state and nothing comes to my mind. Using bind variables like :p1_n := value will not commit and is the way to go if there is an exceptional case where you still need to explicitly set a value on submit withing a PL/SQL process. apex_util.set_session_state is there to be used from a background process. It needs to commit in order to see the value in the application. In that case, if it ever happens to be required, you can use autonomous transaction.

    Regards,

    Denes Kubicek

    • Hi Denes,

      Thanks for your comments. If you’re wondering how often we would call set_session_state – the policy of my current client is perform ALL business logic in database packages, and separate it from the application – and for practicality instead of passing all the items to/from the procedures, the package uses v() and apex_util.set_session_state to manipulate the session state.

      The upshot of this is that they make hundreds of calls to v() and set_session_state(), mixed up with a whole lot of business logic including DML. The result is each page request entails a LOT of commits in fast succession.

      There are, however, some advantages to this approach (especially for complex applications) – see my earlier article http://jeffkemponoracle.com/2014/02/13/build-your-apex-application-better-do-less-in-apex/

      • Hello Jeff,

        I know your article. I have been using this approach for several years now with the difference of passing all the items to/from functions or procedures. I of course know many clients and many policies. A big downside of the approach of your client is that they need to name application items in their code as plain text and need to change their code as soon as the application changes – otherwise it will not work. It is also hard for debugging. Misspelling of an item name could take hours to locate. Invoking so many function and procedure calls for setting and getting item values must be slower in processing. However, I also know how clients are and sometimes the best arguments can’t help.

        Regards,

        Denes Kubicek

      • Hi Denes,

        Thanks for your comments, they are very appropriate.

        I have found that the approach works quite well in spite of all the items named in plain text, because the calls to v() and set_session_state() are all done in one procedure (by convention); when the application changes, the package is changed as well; debugging is usually not too difficult because you only have one procedure to look at.

        As far as performance is concerned, there are other issues (which are currently being addressed) which cause pages to load/process slowly, and the overhead of v() and set_session_state() is very low in comparison.

        I think you’re right, though – it would be more robust if we pass all apex items to/from the procedure, so that it doesn’t need to call v() or set_session_state() at all.

  3. I don’t see why all internal Apex session state manipulation couldn’t be wrapped in autonomous transactions, as suggested in that 2008 forum thread. They should probably keep the current behaviour for backwards compatibility, but use the “compatibility mode” feature to use autonomous transactions going forward.

    Perhaps you could add this suggestion to the Apex Feature Request application, and we could vote it up! 🙂

  4. Could you write a custom autonomous transaction version of the set_session_state fn for your own purposes? Of course it would need to be maintained with each new release…

    • Yes, I intend to. However, I would never just add it into the internal Apex schema – instead, I’d have a local procedure accessible to each workspace so that it won’t be affected by Apex upgrades.

  5. Christian Neumueller
    20 February 2014 - 9:09 pm

    The reason why these commits survived the session state code reorg in 4.2 was backwards compatibility. We were mainly concerned about introducing concurrency issues. Maybe using compatibility mode to move the commits into an autonomous transaction is the way to go, especially if people vote for it.

    Just for clarification, if you assign a value to a bind variable, APEX writes the value back to session state after the block finished it’s execution. This uses the same underlying procedure as apex_util.set_session_state, which commits if the value has actually changed. The difference is that the commit (maybe…) happens after the application code, not in the middle. Another consequence of this is that you should not mix session state modifications via bind variables and modifications via API calls.

  6. THANK YOU FOR THIS. I have been beating myself up with this inconsistent behaviour for the past 3 hours.

    Very insightful.

Leave a Reply

Your email address will not be published / Required fields are marked *