Commitment is such a loaded word these days. Maybe we humans can't quite get it right, but certainly we can count on our computers to get it right. After all, they're binary machines, 0 or 1, false or true: absolute logic. Or can we?
While debugging a PL/SQL block in APEX, I noticed that the code was marking the input data as processed, even if the procedure terminated with a raise_application_error. I was focused on debugging the error causing the raise_application_error, so I didn't care that the input was marked as processed. After fixing the problem, then it occurred to me -- wait, a minute -- why was the input marked as processed when the transaction terminated in error?
When we do transaction processing, we expect the transaction to be atomic. Either all parts of the transaction goes in, or none of the transaction goes in. Imagine going to the ATM machine, moving $50 from savings to checking, and the move fails in the middle of the financial transaction. That would leave the savings debited $50, nothing credited to checking, and $50 floating in the bit-ether. If we do transaction processing correctly, then either the money is successfully moved, or the money is left in savings.Nothing is left half-done.
Back to Apex. I clearly did not have a correctly-working transaction. There was nothing in my procedure that would do a commit. But, somewhere, somehow, something was committing the database changes, even though I didn't expect it to.
There's a lot going on here. Partly it's semantics and syntax. My APEX code was not doing anything I hadn't done in COBOL/DB2 with host variables, and the COBOL/DB2 transactions always worked correctly. Partly, it's an understanding, or misunderstanding of what's happening in Apex. Let's start with syntax and semantics first.
Imagine we have COBOL/DB2 program and some PL/SQL in an Apex block. Both code snippets will do the same thing. Here's the COBOL:
EXEC SQL
INSERT INTO MYTABLE(MY_COLUMN) VALUES(1)
END-EXEC.
EXEC SQL
SELECT DUMMY
INTO :WS-DUMMY
FROM SYSIBM.DUMMY1
END-EXEC;
EXEC SQL
ROLLBACK
END-EXEC.
Now lets look at the PL/SQL code in APEX. Each of these anonymous blocks runs in its own APEX process in the same page after the page is submitted.
begin
insert into mytable (my_column) values(1);
end;
begin
select dummy
into :p1_dummy
from dual;
end;
begin
rollback;
end;
Well, that's pretty simple. Except for wrapping the PL/SQL inside an anonymous block and wrapping the COBOL up for the DB2 pre-processor, these code snippets should do the same thing. But they don't. Run each piece of code several times. The COBOL example will never add a row to MYTABLE, while the ORACLE APEX page adds a row to MYTABLE. Notice I said "adds a row", but more on that later.
This isn't an Oracle/DB2 issue. Login to an Oracle SQLPLUS session, and try the PL/SQL code snippets in SQLPLUS. The SQLPLUS session yields the same results as the COBOL test.
Syntax and semantics: in an ideal world, there's a tight correlation between syntax and semantics. We would like things that look the same to behave the same. Our variables, :WS-DUMMY and :p1_dummy, look like two ordinary "host variables". An astute reader of Apex documentation will call :p1_dummy a "session variable", and that's our first clue. Session variables may look like host variables (syntax is the same), but session variables do not behave like host variables (semantics differ). Mostly they do behave the same, with an important exception:
changing a session variable causes APEX to take a commit.
Back to "adds a row": only one row is added. If we run our code snippet in Apex a few times, and examine the table, we find there is one row. Why aren't there a few rows? After all, didn't we select data into :p1_dummy each time? That's the second point:
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. This means that our code will behave differently depending on the data!
For gremlins, this is a good thing. For developers, this is not so good. Here are a few tips to avoiding obscure errors caused by buggy transaction processing:
- During development, turn on DEBUG. Examine the debug logs, looking for text like Session State: Saved Item "P1_DUMMY" New Value="". Anytime we see this, APEX has taken a commit.
- Use local variables declared in the anonymous block rather than session variables. Use session variables to store values that will be displayed on the web page, or to save values that you need between page submits. Otherwise, use local variables.
- If you pass session variables as IN OUT arguments to an external procedure, changing the parameter values in the procedure is the same as changing the session variables.
- APEX only performs commits after a process is run. If you find an error, rollback the transaction and set an error before your process exits. This is true for both anonymous blocks in APEX and external procedures called by APEX.
- If you need a commit, explicitly code the commit. Do not assume that APEX will take a commit just because the procedure set a session variable. If the value of the session variable didn't change, APEX won't take a commit.
- Be aware of how APEX processes tabular forms (more on that below).
Searching the APEX document is rather discouraging. Searching for keywords like COMMIT and TRANSACTION do not turn up any applicable information. Searching the Internet was more fruitful. Daniel McGhan
blogged this topic in August, 2012, and he notes seven situations where APEX takes a commit.
The Oracle forums are another good source of information. According to an Oracle employee responding to a question in the
Oracle forums:
Commits are issued when your code does so explicitly, when a page show
or accept request reaches the end, or when anything within your code
causes session state to be saved. Session state -altering actions
include PL/SQL assignments to (or "select into") bind variables which
are page- or application-item references, calls to session-state
updating APIs, and page or application computations.
Our pages often include some simple items(text boxes, select lists, date pickers, etc.) plus a tabular form for repeating elements. If we're going to process the page as one transaction properly, we need to know if there are any implicit commits in the APEX Page Processing post-submit processes. If we create a tabular region, we find that Apex adds two post-submit processes: ApplyMRU and ApplyMRD.
Ideally, we should be able add our post-submit process and have all of the post-submit process run as one transaction. Either everything is committed, or none of it committed. We do not want half a transaction.
In the absence of good documentation to guide us, let's do some testing. We'll test in APEX 4.2, the results may differ in other versions. We can test for implicit commits by building a one-page applicaton with a tabular region on the DEMO_CUSTOMER table. For our first test, let's try some inserts and updates. Our three post-submit processes will be:
- ApplyMRU
- Rollback
- ApplyMRD
Click the Submit button, wait for the page to redisplay, and the envelope, please: Any updates or added rows are committed. The rollback has no effect; any updates or added rows are committed. This is not the behavior we expect. We expect a rollback to undo any inserts or updates from ApplyMRU.
For our second test, let's try some deletes. We'll move our rollback step after the ApplyMRD. Our three post-submit processes will look like this:
- ApplyMRU
- ApplyMRD
- Rollback
Check a few delete boxes, click the Delete button, confirm that we want to delete the rows, wait for the page to refresh, and the envelope, please: APEX displays a message saying the rows are deleted, but in fact, the deleted rows are still in the table. The rollback worked as we expected.
There are two important points: First, ApplyMRU takes an implicit commit. Second, ApplyMRU and ApplyMRD do not behave the same way! ApplyMRU and ApplyMRD are not executed at the same time, their process is driven by either the Submit button or Delete button, so we don't need to worry about running them together if the tabular form is the only updatable elements on the page.
But we do need to be careful if we run other processes after the submit button is clicked. If ApplyMRU runs first and commits changes, and if a second process runs and fails, then we have an half-completed transaction. Clearly, that's a bad thing. So, to the list of suggestions above, let's add two more:
- Order post-submit process so that ApplyMRU and ApplyMRD run after any other process. If the early processes raise an error, the ApplyMRU and ApplyMRD will not run, and there will not be a half-completed transaction.
- In some cases, it will make more sense to split a page into two (or more pages), and save all the database processing until the last page. APEX can save session variables on the first pages, and the session variables are available on the last page when all the database updates occur.
APEX is powerful development environment. However, if we want our transactions to process properly, if we don't want to spend time tracking down obscure bugs, then we need to be aware of when APEX takes implicit commits.
