1 2 Previous Next 17 Replies Latest reply: Apr 23, 2013 2:30 AM by Billy~Verreynne RSS

    commit in procedures

    user10447332
      hi,
      i have written one procedure which contains only insert and update statements which are coming from front end.
      after insert/update the records into table wether i need to write commit in my procure or commit is done from front end itself ie commiting the transaction.
        • 1. Re: commit in procedures
          Hoek
          wether i need to write commit in my procure or commit is done from front end itself ie commiting the transaction.
          The commit (or rollback) should be issued by the caller/invoker of your procedure.
          • 2. Re: commit in procedures
            jeneesh
            Best practice is to handle COMMIT from the calling program..

            ie., dont commit in procedure, but handle it in the front-end

            worth reading: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1519405621318
            • 3. Re: commit in procedures
              Frank Kulash
              Hi,
              user10447332 wrote:
              hi,
              i have written one procedure which contains only insert and update statements which are coming from front end.
              after insert/update the records into table wether i need to write commit in my procure or commit is done from front end itself ie commiting the transaction.
              You can either COMMIT in the same procedure or not. Neither one is necessarily or always better than the other; it depends on how the procedure is used.
              • 4. Re: commit in procedures
                BluShadow
                I agree with Frank, a commit should be issues when you have reached the end of a logical transaction. That logical transaction may be contained within a procedure, in which case committing in the procedure is appropriate, or it may be spread across several procedures, in which case the calling code (whether that's another procedure or a front end interface etc.) should issue the commit when the transaction has logically completed.
                • 5. Re: commit in procedures
                  sybrand_b
                  Strongly disagree and this is completely wrong advice to newbies.
                  It is actually appalling you recommend this.

                  ----------
                  Sybrand Bakker
                  Senior Oracle DBA
                  • 6. Re: commit in procedures
                    jeneesh
                    I will disagree - based on my experience..

                    For example, Just after COMMIT, if application crashes - The user will get an error window, But transcation will get saved..
                    • 7. Re: commit in procedures
                      BluShadow
                      jeneesh wrote:
                      I will disagree - based on my experience..
                      Disagree with what though?

                      Not all code is written for a "front end" so you cannot just say that one should commit from the front end. Some processes are written as back end processes and a commit should be placed in the place where a logical transaction is complete, after all that's the whole concept of database transactions and committing.... you commit when the transaction is complete.

                      http://en.wikipedia.org/wiki/Database_transaction

                      What is your experience?
                      • 8. Re: commit in procedures
                        jeneesh
                        BluShadow wrote:
                        jeneesh wrote:
                        I will disagree - based on my experience..
                        Disagree with what though?

                        Not all code is written for a "front end" so you cannot just say that one should commit from the front end. Some processes are written as back end processes and a commit should be placed in the place where a logical transaction is complete, after all that's the whole concept of database transactions and committing.... you commit when the transaction is complete.

                        http://en.wikipedia.org/wiki/Database_transaction

                        What is your experience?
                        I have updated my post....

                        And if you dont have a choice to do it in Front-end, thst is different case..

                        But, If I have choice to select from the two options, I will select to commit from front-end.
                        If there is no option to do it at front end - The question is not valid..
                        • 9. Re: commit in procedures
                          BluShadow
                          I see you edited your post...
                          jeneesh wrote:
                          For example, Just after COMMIT, if application crashes - The user will get an error window, But transcation will get saved..
                          Great, so the user managed to get their work saved before the database crashed. If the error handling is correct then they will get an error to say that the database crashed, and not an incorrect message about their work not being saved.

                          I don't actually see what your point is though... the database could crash after any commit, so it's irrelevant to where the commit is actually placed, unless of course you're referring to a procedure that does more work after the commit but before returning to the user, which means it's not really the end of the logical transaction and the commit is in the wrong place.
                          • 10. Re: commit in procedures
                            jeneesh
                            I did not say, DB is crashed..

                            I do say about APPLICATION..
                            • 11. Re: commit in procedures
                              BluShadow
                              jeneesh wrote:
                              I did not say, DB is crashed..

                              I do say about APPLICATION..
                              Still don't know what difference that makes. If the application crashes, the user will be sitting there saying "thank God my work got saved before it crashed!" ;)

                              Assuming there is a 'front-end' application, it's still a case of "it depends", because it does depend on what the application is doing. Maybe the front end application kicks off an atomic process on the back end that, for example, updates all employees with this years "cost of living increase" for their salary. They push the button, it calls the procedure to do it. There's no reason the procedure cannot commit the transaction when it's complete, and no reason that the front end has to issue a commit afterwards, especially as the application itself has not been involved in any part of the transaction apart from initiating it through the procedure call. Conversely, if the application is inputting data and sending 'records' to the database to be inserted/updated/deleted, then you would likely have a commit on the "Save" button (or whatever) after it's sent all the data to be processed by the database, so yes, in that case you'd have a commit in the front end.

                              There is no strict answer of saying it should be in the front end or should be in the procedure. The only strict answer is that the commit should happen when the transaction has logically completed.
                              • 12. Re: commit in procedures
                                jeneesh
                                BluShadow wrote:
                                jeneesh wrote:
                                I did not say, DB is crashed..

                                I do say about APPLICATION..
                                Still don't know what difference that makes. If the application crashes, the user will be sitting there saying "thank God my work got saved before it crashed!" ;)
                                Thats good - But he will never come to know that it got saved..... ;)

                                Probably he will redo the work..
                                • 13. Re: commit in procedures
                                  BluShadow
                                  jeneesh wrote:
                                  BluShadow wrote:
                                  jeneesh wrote:
                                  I did not say, DB is crashed..

                                  I do say about APPLICATION..
                                  Still don't know what difference that makes. If the application crashes, the user will be sitting there saying "thank God my work got saved before it crashed!" ;)
                                  Thats good - But he will never come to know that it got saved..... ;)

                                  Probably he will redo the work..
                                  That could happen anyway. The front end issues a commit, and then the application crashes before it displays a message to say that the work was saved... the user still doesn't know. Answer: don't write applications that crash. ;)

                                  Typically users will have the ability to retrieve or search for records, so most users (in my experience) go back into the application and search to see if the work saved ok or not, before they do anything.
                                  • 14. Re: commit in procedures
                                    jeneesh
                                    That could happen anyway. The front end issues a commit, and then the application crashes before it displays a message to say that the work was saved... the user still doesn't know.
                                    Yea, that is there.. But probability is half of that was there with a COMMIT in the DB..
                                    Answer: don't write applications that crash. ;)
                                    That is a good advice..

                                    And to add - I was not saying not to use COMMIT inside procedure.. But, if there is a choice, In my opinion.....

                                    Ok, we will stop this I feel - never ending debate...
                                    1 2 Previous Next