FORALL and ROLLBACKs


I received this question in my In Box over the weekend:

Hi Steven,

I have grown up reading the PL/SQL bible, Oracle PL/SQL Programming. I have the 5th edition with me right now, Indian reprint, ISBN - 971-81-8404-949-7.

I was going through the chapter 21 : Optimizing PL/SQL performance. Please refer to the section 'ROLLBACK behaviour with FORALL'.  In point number 2, it's mentioned that "Any previous DML operations in that FORALL statement that already completed without any error are NOT rolled back."

Can you please explain this ? As far as I understand a FORALL can have only one DML statement, so all the previous work done by the DML will be rolled back.

Thanks for your time. Hoping a response soon.

An Indian reprint of the 5th edition? You are so lucky. I don't even have one of those. I don't even have a copy of that! I will have to ask my friends at O'Reilly Media for a copy! :-) 

Now to answer your question, and it is a common one:

Perhaps the way I phrased this is not as clear as could be. So let's take it step by step and hopefully all will become clear. Let's start with a simplified FORALL statement:

FORALL iterator IN low_value .. high_value
   dml_statement

FORALL is itself a PL/SQL statement, and within it you find a single DML statement - and no more than one, as you so correctly point out.

When the PL/SQL engine encounters a FORALL statement, it uses the IN clause to determine how many individual DML statements will be generated (with bind variables drawn from the bind array(s)) and then passed to the SQL engine. 

In the sentence you quoted above, I use the term "DML operations" to refer to each of these generated  statements.

So, for example, in this little example, a total of 5 DML statements might be executed by the SQL engine. 

DECLARE
   TYPE ids_t IS TABLE OF INTEGER;
   l_department_ids ids_t := ids_t (1, 2, 3, 4, 5);
BEGIN
   FORALL indx IN low_value .. high_value
      UPDATE employees SET salary = salary * 2
        WHERE employee_id = l_department_ids (indx);
EXCEPTION
   WHEN OTHERS THEN
      dbms_output.put_line (SQL%ROWCOUNT);
      RAISE;
END;

Why only "might be"? Suppose that the salary column is defined as NUMBER(3) and the salary for an employee in department 2 is 600. When I double that, I now need 4 digits to store the value. So the SQL engine will reject the update and throw an error back to the PL/SQL engine.

So in that case, the following takes place (or not as the case may be):
  • one DML statement completed (for employee ID 1)
  • one failed (for ID 2)
  • three of them (for IDs 3-5) never even executed
And the PL/SQL block in which the FORALL was executed traps the error from the second statement executed and displays 1 because it reflects the total number of rows modified by the FORALL statement, not the last individual DML statement generated by the FORALL statement.

And this tells us quite unambiguously that the effect of the first UPDATE is not rolled back by the second UPDATE's failure.  

Oh and by the way: this behavior has nothing whatsoever to do with FORALL. You could execute three separate DML statements in a block and it works the same: if the first two finish successfully and the third fails, the effects of those two statements are not rolled back.

Of course, if your exception propagates all the way out of the top-most PL/SQL block unhanded, then all the outstanding changes in your session will be rolled back.

Hope that helps! Comments, questions, anyone?

Comments

  1. Steven,
    Thanks for the prompt explanation. It's crystal clear now.
    I got confused because I did not assume that the exception is getting caught. May be because exceptions were covered later in the chapter.

    About your books, what can I say, they are bread and butter !

    ReplyDelete
  2. PL/SQL Programming: indeed the bible! Taught myself PL/SQL 10 years ago on the 3rd Edition and been keeping the latest version on my library shelves for reference ever since!
    A great accompaniment to Forall is the 'Save Exceptions' clause which has two great advantages: (1) it does not stop DML statements subsequent the statement that caused the exception from being executed; (2) it allows one to capture each individual DML error and decide how to handle it. I sometimes 'abuse it' a little bit when I want to make bulk inserts/updates that I know *may* cause errors. For example in a multi-user environment where the possibility exists that a parent record may be deleted by another user, but this is a valid business scenario in which case the individual insert/update can be safely ignored. So, with 'Save Exceptions', I can analyse each DML error, and if it's in the list of 'acceptable errors' I can just ignore it and commit the rest. A bit of a hack, but the alternative (iteratively issuing individual DMLs and watching paint dry) is unacceptably slow. We've noticed performance improvement of at least one order of magnitude (sometimes several orders of magnitude) with Bulk DMLs!

    ReplyDelete

Post a Comment

Popular posts from this blog

Table Functions, Part 1: Introduction and Exploration

Recommendations for unit testing PL/SQL programs

The future of Oracle PL/SQL: some thoughts on Sten Vesterli's thoughts