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?
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
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.
20 February 2014 - 2:31 pm
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.
20 February 2014 - 2:33 pm
All in the name of making life simpler and easier for the developer, of course… 🙂
20 February 2014 - 3:41 pm
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.
20 February 2014 - 3:47 pm
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/
21 February 2014 - 4:00 pm
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.
21 February 2014 - 4:09 pm
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.
20 February 2014 - 3:56 pm
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! 🙂
R Andrew Cocks
20 February 2014 - 5:42 pm
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…
20 February 2014 - 5:49 pm
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.
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.
1 September 2020 - 6:47 pm
THANK YOU FOR THIS. I have been beating myself up with this inconsistent behaviour for the past 3 hours.