12 Replies Latest reply: Jun 18, 2014 6:45 PM by rp0428 RSS

    Action After Transaction-Rollback

    d4e7e6bf-2d91-44e4-90f3-e6fbcd3be813

      Hi there,

       

      Is there a way to perform an action after a transaction rollback , etc. via a system trigger ?

       

      My problem:

       

      in my program i need to change a field value in a table XY, which is visible to all other users outside of a transaction. If, within the transaction, the process is killed , all changes within the transaction + the changed field value of the XY table must be rolled back.

      For that I need to intercept the rollback and perform a further action.

       

       

      Thx and Greetz, cagiv

        • 1. Re: Action After Transaction-Rollback
          sb92075

          d4e7e6bf-2d91-44e4-90f3-e6fbcd3be813 wrote:

           

          Hi there,

           

          Is there a way to perform an action after a transaction rollback , etc. via a system trigger ?

           

          My problem:

           

          in my program i need to change a field value in a table XY, which is visible to all other users outside of a transaction. If, within the transaction, the process is killed , all changes within the transaction + the changed field value of the XY table must be rolled back.

          For that I need to intercept the rollback and perform a further action.

           

           

          Thx and Greetz, cagiv

          not possible

          • 2. Re: Action After Transaction-Rollback
            rp0428
            Is there a way to perform an action after a transaction rollback , etc. via a system trigger ?

             

            You don't need a 'system trigger'. Just perform the action.

            1. do some DML

            2. do a ROLLBACK

            3. do some 'action after a transaction rollback

            Seems pretty simple.

             

            What PROBLEM are you trying to solve?

            My problem:

             

            in my program i need to change a field value in a table XY, which is visible to all other users outside of a transaction. If, within the transaction, the process is killed , all changes within the transaction + the changed field value of the XY table must be rolled back.

            For that I need to intercept the rollback and perform a further action.

            You say 'My problem' but you don't say what the problem really is.

             

            Other users will NOT see any changed values that have not been committed. So what they see is the same after the rollback as it was before.

             

            There is no such thing as 'intercept the rollback'. A rollback is an indivisible action. You can do something BEFORE it happens. Or you can do something AFTER it happens. But you can't interact with it WHILE it is happening.

            • 3. Re: Action After Transaction-Rollback
              sybrand_b

              Relational databases don't have 'fields'.

              Also, as a transaction ought to be atomic, there is no such thing as the hobby procedure you describe.

              Maybe you should use flat files. They have 'fields'.

               

              -------------

              Sybrand Bakker

              Senior Oracle DBA

              • 4. Re: Action After Transaction-Rollback
                chris_c

                Can you clarify what you are trying to achieve, from the description you are doing something like the following.

                 

                update table1 set column1='XY' where ....;

                commit;

                Begin long complicated transaction;

                ...

                ...

                commit;

                update table1 set column1='ZZ' where ....;

                commit;

                 

                too make the first update visible to other sessions requires a commit so the main transaction will not roll it back if it fails, there is no way to make this robust but if you describe what it is you are trying to do/avoid then there may be a better solution

                • 5. Re: Action After Transaction-Rollback
                  jgarry

                  That's called an autonomous transaction, and you seem to be giving an example of why some respected people wish that capability didn't exist, so it isn't appropriate to give a solution without further specific requirements.

                  • 6. Re: Action After Transaction-Rollback
                    d4e7e6bf-2d91-44e4-90f3-e6fbcd3be813

                    Thanks for all answers, I will describe it in more detail :

                     

                    This is a process, which books payments, completely or nothing. It can takes hours.

                     

                    I want to set a status prior to the transaction.

                    TableXY:Status = 'New' to TableXY:Status = 'Running'

                     

                    There is an overview with all runnig Processes.

                    So every other user can see that there is currently running a process and can not be restarted / needs to restartet.

                     

                    Then I start the transaction. If this aborted due to power failure , Taskkill etc. everything must be rollback, the status too.

                    How can I in addition to rollback perform another action to enforce the status back.

                    • 7. Re: Action After Transaction-Rollback
                      sb92075

                      d4e7e6bf-2d91-44e4-90f3-e6fbcd3be813 wrote:

                       

                      Thanks for answer, I will describe it in more detail :

                       

                      This is a process, which books payments, completely or nothing. It can takes hours.

                       

                      I want to set a status prior to the transaction.

                      TableXY:Status = 'New' to TableXY:Status = 'Running'

                       

                      There is an overview with all runnig Processes.

                      So every other user can see that there is currently running a process and can not be restarted / needs to restartet.

                       

                      Then I start the transaction. If this aborted due to power failure , Taskkill etc. everything must be rollback, the status too.

                      How can I in addition to rollback perform another action to enforce the status back.

                       

                      With Oracle database, no other session can ever see uncommitted DML.

                       

                      >I want to set a status prior to the transaction.

                      what does above mean?

                      what exactly is "status"?

                       

                      With Oracle database, no other session can ever see uncommitted DML.

                       

                      if session does not explicitly issue COMMIT, the changed data will never bee seen by any other session & eventually rolled back (at next DB start)

                      • 8. Re: Action After Transaction-Rollback
                        d4e7e6bf-2d91-44e4-90f3-e6fbcd3be813

                        ok, i didnt describe, i use a programming language with source like that:

                         

                        UPDATE TableXY SET Status = 'Running' WHERE SysID = 123

                        (autocommit and before transaction, other session can see it)

                         

                         

                        StartTransaction 'PMT_BOOK'

                         

                        ...many source

                         

                        IF NOT Error THEN Commit ELSE Rollback

                         

                        EndTransaction 'PMT_BOOK'

                        • 9. Re: Action After Transaction-Rollback
                          sb92075

                          >IF NOT Error THEN Commit ELSE Rollback

                           

                          IF NOT Error THEN Commit ELSE  BEGIN Rollback; ADDITIONAL_PROCESSING; END;

                          • 10. Re: Action After Transaction-Rollback
                            Brian Bontrager

                            If you want others to see the "running" status, that status change must be committed, either by an autonomous transaction, or a single step with its own commit at the beginning of your process.  If something fails and that status needs to be updated you need a separate monitor process (since you want to detect stoppage due to power failure or kill) to check if the main process is still running, and if not change your status from "running" to whatever status you use to indicate the process failure. 

                             

                            Think about the power failure situation: If power fails on the machine doing the work, there is nothing running anymore to do any kind of cleanup logic.

                            • 11. Re: Action After Transaction-Rollback
                              jgarry

                              The essential issue is the design is from batch processing era, and the database is from the OLTP era.  So if you want a monitor program, you'll have to write all that, perhaps as the autonomous transaction or previous to the real transaction, as Brian suggested, and have a cleanup program for the monitor, since people are going to have to get involved in case of a crash.  The additional_processing code example sb gave will be fine for non-crash, successful or non-successful runs, provided you really have the proper transactional handling - the transaction starts at the beginning of the process, doesn't have any commits or DDL (which does commits) in the middle, and there is sufficient undo to always handle it along with everything going on in the system.  The one thing autonomous transactions would add would be the ability to spit out processing information in the middle of it all for everyone to be able to see.  You could conceivably also do that by using os commands or oracle views (by the other programs) to imply status, though that can be tough to get exactly right for all circumstances.

                               

                              Even though a system crash will roll everything back on the next startup, if you start the processing again before the rollback is finished, you may have performance issues.  There are some things you can tweak about this, but an hours long process is an hours long process (perhaps lessened if it is a cpu bound process originally).

                              • 12. Re: Action After Transaction-Rollback
                                rp0428
                                This is a process, which books payments, completely or nothing. It can takes hours.

                                 

                                I want to set a status prior to the transaction.

                                TableXY:Status = 'New' to TableXY:Status = 'Running'

                                 

                                You would set and COMMIT that status as part of the start of your process

                                There is an overview with all runnig Processes.

                                So every other user can see that there is currently running a process and can not be restarted / needs to restartet.

                                Which is WHY the change of status MUST BE committed in its own transaction.

                                Then I start the transaction. If this aborted due to power failure , Taskkill etc. everything must be rollback, the status too.

                                How can I in addition to rollback perform another action to enforce the status back.

                                 

                                Any such abortion can leave things in an undertermined state. Some (e.g. power failure with no backup system) will leave things in the middle and YOU will have to have a recovery process to determine what needs to be 'cleaned up' and at what step to restart the process.

                                 

                                Processes such as you describe are often implemented as multiple-step processes. Each step logs information about start time, end time (and other info) and commits its work appropriately. Then if a failure occurs your log table will show what the last successful step was so that you can recover/restart from their.

                                 

                                A 'status' value may still show 'running'. Your recovery process will possibly need to change that status to 'ready to run', 'executing restart' or some other value depending on how you design it.