10 Replies Latest reply: Sep 29, 2008 1:17 AM by Denes Kubicek RSS

    When commit is executed?

    bpprive
      I remarqued a strange issue in application today.

      Suppose I have 2 processes after submit and validations:

      10 - PROCESS ROW FOR VD_VIEW (process created by wizard and work properly)
      20 - Insert the version number in the version table (Process I defined to add a row each and every time the process 10 inserts a row in VD_VIEW)

      Everything work fine.

      Today, I added some validations on the version table's trigger. The version being inserted must be greater than all the others in the table. Once again, the trigger fires and let the row being inserted when the version is greater than. The problem is when the version is lower or equal to the others in the table.

      I coded a raise_application_error in the version table's trigger when the version isn't greater. The error is shown correctly in the page but it seems that the line inserted by the process 10 - PROCESS ROW FOR VD_VIEW is not rollbacked.

      I tought a raise_application_error would raise the insert in the version table and the insert done by the other process too. It seems not.

      So the question is how commit are issued? Following each process (which is in my view not what we want) or each and every time all the processes have been executed in rendering OR processing a page? Why this happen?

      Did I assumed to quickly that only on commit is issued after the execution of all the processes?
        • 1. Re: When commit is executed?
          515357
          Each process has its own BEGIN-END block, and is executed similar to how a Stored Procedure would be executed. As a result, commit will be issued at the end. This is the behavior I have noticed. Apex developer will be able to confirm for sure.

          Can you move the INSERT statement into process 10? Hopefully, it would work.

          Ravi
          • 2. Re: When commit is executed?
            60437
            Bruno-Pierre,

            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.

            About your exception handler, do you have a rollback statement in there?

            Scott
            • 3. Re: When commit is executed?
              bpprive
              @Ravi Adik
              It does not make sense to me to commit each time a process is executed. It make more sense that it commits when Apex have to save the session state, but explicitly this, like a automonous_transaction. Sspadafo, is Apex commit the entire transaction or the little session state saving transaction?

              I cannot move the insert statement into the #10 process because it's a dml process, no way to modify it. I could rewrite the process, I know, but I'm looking for a more generic way to do it, in addition to understand how apex engine works.

              @sspadafo
              I don't have any of the above listed, except, the "returning key into page item" functionnality of the dml process. Is this issuing a commit?

              About the exception handler, the process is just an insert statement, no exception handler. The table's trigger raises when the validation fails and the error is trapped by apex and shown in the message error section, in top of the page. I assumed that the raise_application_error executed in the trigger would perform a rollback like it is in regular pl/sql, by raising to the top-level caller which implicitly rollback the transaction if the error is'nt trapped and managed correctly. But your point is good, if the error is trapped by the caller (the apex procedure which encapsulates the pl/sql process with a begin-end anonymous block), if the error is managed by this proc, it is possible that the raise_application_error I assume would rollback doesn't. I must be wrong?

              I tried to add a rollback before the raise_application_error and it doesn't seems to work...

              Thank you, Bruno-Pierre

              Edited by: bpprive on Sep 22, 2008 11:07 AM
              • 4. Re: When commit is executed?
                bpprive
                sspadafo, do you have any others idea about this?

                thank, Bruno-Pierre
                • 5. Re: When commit is executed?
                  Denes Kubicek
                  Bruno-Pierre,

                  using triggers for validation is not the right way to go. Especially not then, when you already inserted some values in some other table. As mentioned before, each process is indipendant and will commit if successfull. Why don't you use the validation possibilities apex offers and do a validation there, before any of the processes start? Only if validation doesn't fire, the DML processes will take place:

                  10 - PROCESS ROW FOR VD_VIEW (process created by wizard and work properly)
                  20 - Insert the version number in the version table (Process I defined to add a row each and every time the process 10 inserts a row in VD_VIEW)

                  Some examples on how to create and use validations you can find here:

                  http://apex.oracle.com/pls/otn/f?p=31517:210

                  Simply search for validations and you will find several examples.

                  Denes Kubicek
                  -------------------------------------------------------------------
                  http://deneskubicek.blogspot.com/
                  http://www.opal-consulting.de/training
                  http://apex.oracle.com/pls/otn/f?p=31517:1
                  -------------------------------------------------------------------
                  • 6. Re: When commit is executed?
                    bpprive
                    I know about apex validations, I know how to do them but the problem is it doesn't make sense to me.

                    The process flow shouldn't be so granular. According to sspadfo, it commits only when you save session state or someting like that, not after each and every process...

                    The best example I can show to explain my point is the master-detail example : there is a process to manage the master (doing the update) and another one to manage the detail (insert/update). Does it make sense to you to commit the update on the master and because of a trigger on the detail table, rollback the operation of the detail, which means half of the transaction commited and the other half, rollbacked?

                    I always though apex was a good tool for data driver applications, I mean applications driven by the database. In my view, triggers are a good way to keep the database integrity, validations coded once, called many. many technologies have to insert-update-delete same tables, seems unproductive to me to code validations in every pages and every technologies who needs to talk to those tables.

                    And if it work like this, I know a lot of customers who will have big problems migrating from forms to apex. You opinions?
                    • 7. Re: When commit is executed?
                      60437
                      Bruno-Pierre,

                      1. My name is Scott.

                      2. The "returning into" option on a DML process will cause a commit as it is one of those session state altering actions.

                      3. The session state altering transactions are not autonomous. They should be but they aren't.

                      4. Commits are not performed at the end of each process. They happen only in the cases I listed.

                      Scott
                      • 8. Re: When commit is executed?
                        bpprive
                        Thank Scott, sorry about the nickname call.

                        This anwser my question. I didn't thought that the " returning into " was a session state saving (which now make sense).

                        Do the apex team plans to switch the behaviour of this int the next versions, I mean the point #3 about the session state altering. I think the same than you, it should be autonomous.

                        What's you opinion about the example I gave, the master-detail. If the second process fails, the one which saves the details, there isn't an integrity problem (the first process may have done changes in the database and these changes stay because they are commited)?

                        Thank again, Bruno-Pierre
                        • 9. Re: When commit is executed?
                          Denes Kubicek
                          Hm, Scott clarified my missunderstanding of the commit. Thanks Scott. I didn't know about the dependance on session state altering and was convinced each process will commit if successfull. Well, that is actually good to know. However, I still do not agree with the practice of putting validations in a trigger. What is the reason for that? Imagine you would first insert thousands of rows into a table just to roll that back short time after. Also, if someone needs to maintain that application, how will he know there is a validation hidden in a trigger? In my opinnion, there is no difference in granularity if you put a validation on your page or in a trigger. If you are concerned about coding it too often, create a package and just call that from your validations. But this way it will be transparent. Everyone who touches that application will know there is a check performed. Also, you will not need to run some processes which may, at the end, be useless. And your users will receive a more friendly error message. Maybe you would like to check this:

                          http://www.oracle.com/technology/oramag/oracle/08-sep/o58asktom.html

                          Denes Kubicek
                          -------------------------------------------------------------------
                          http://deneskubicek.blogspot.com/
                          http://www.opal-consulting.de/training
                          http://apex.oracle.com/pls/otn/f?p=31517:1
                          -------------------------------------------------------------------
                          • 10. Re: When commit is executed?
                            60437
                            Bruno-Pierre,
                            Do the apex team plans to switch the behaviour of this int the next versions, I mean the point #3 about the session state altering. I think the same than you, it should be autonomous.
                            We've discussed it but I don't see it happening anytime soon.

                            About the master-detail question, I'm afraid I don't have time to study the problem in enough depth to give an opinion but what Denes says makes sense to me.

                            Scott