7 Replies Latest reply: Dec 6, 2012 5:04 PM by 946279 RSS

    transaction over a few instances?

    946279
      Here is a situation: business proces requires modifications in 3 databases. in each database there is a procedure to do the job, lets say p1 on database d1, p2 on d2, p3 on d3.

      Is it possible to call p1, then p2, then p3 in a way that if any error occurs during one of them all changes done so far on those databases can be rollbacked? In other words, is is possible to treat all calls to those procedures as a part of one transaction (ie. all or nothing)?

      thanks
        • 1. Re: transaction over a few instances?
          BluShadow
          Using database links and exception handling, with one of the procedures being the main controlling procedure (or some wrapper procedure that calls each of them checking for appropriate exceptions/return values)
          • 2. Re: transaction over a few instances?
            946279
            thanks! could you give some high level code template for this situation? this would be very helpful.
            • 3. Re: transaction over a few instances?
              JustinCave
              BEGIN
                p1;
                p2@dblink_to_db2;
                p3@dblink_to_db3;
              EXCEPTION
                WHEN OTHERS THEN
                  rollback;
                  raise;
              END;
              Now, realistically, you'd probably want to handle the exception a bit better. You would generally need to create the two database links ahead of time as well.

              Justin
              • 4. Re: transaction over a few instances?
                Stew Ashton
                Or just:
                BEGIN
                  p1;
                  p2@dblink_to_db2;
                  p3@dblink_to_db3;
                END;
                Statement-level atomicity: the anonymous block will roll back automatically if you just let it.
                • 5. Re: transaction over a few instances?
                  Stew Ashton
                  943276 wrote:
                  Here is a situation: business proces requires modifications in 3 databases. in each database there is a procedure to do the job, lets say p1 on database d1, p2 on d2, p3 on d3.

                  Is it possible to call p1, then p2, then p3 in a way that if any error occurs during one of them all changes done so far on those databases can be rollbacked? In other words, is is possible to treat all calls to those procedures as a part of one transaction (ie. all or nothing)?
                  Yes, and I know three ways to do this:

                  1) Your client code runs in a Transaction Monitor, such as Tuxedo or Weblogic or Websphere Application Server.

                  You access each database separately, but the Transaction Monitor manages a "distributed transaction": when you COMMIT or ROLLBACK, the Transaction Monitor will make sure the changes are made "all or nothing".

                  2) You connect to only one database, and you use this database to access the two other databases using database links. You then call each procedure using a separate anonymous block.

                  The database you are connected to will manage the "distributed transaction" all by itself. You don't need a Transaction Monitor, but you do need to COMMIT or ROLLBACK yourself if there is an exception.

                  3) You connect to only one database, and you use this database to access the two other databases using database links. You then use one anonymous block that calls all three procedures.

                  This is the method Justin and I were discussing.

                  With this method, you don't have to do the rollback yourself, Oracle will roll everything back before returning from the anonymous block you submitted. Justin's variant is equivalent, except you won't get back the line number where the original exception was raised. This may make it harder to figure out where things went wrong.
                  • 6. Re: transaction over a few instances?
                    rp0428
                    >
                    Here is a situation: business proces requires modifications in 3 databases. in each database there is a procedure to do the job, lets say p1 on database d1, p2 on d2, p3 on d3.

                    Is it possible to call p1, then p2, then p3 in a way that if any error occurs during one of them all changes done so far on those databases can be rollbacked? In other words, is is possible to treat all calls to those procedures as a part of one transaction (ie. all or nothing)?
                    >
                    It's not only possible but that is just the way Oracle works. You don't have to do anything special unless there is a long-term failure on one of the databases. Then the local DBA will have to resolve any in-doubt transactions manually.

                    See 'The Two-Phase Commit Mechanishm' in the Database Concepts doc.
                    http://docs.oracle.com/cd/B28359_01/server.111/b28318/transact.htm#sthref450
                    >
                    The Oracle Database two-phase commit mechanism is completely transparent to users who issue distributed transactions. In fact, users need not even know the transaction is distributed. A COMMIT statement denoting the end of a transaction automatically triggers the two-phase commit mechanism to commit the transaction. No coding or complex statement syntax is required to include distributed transactions within the body of a database application.
                    • 7. Re: transaction over a few instances?
                      946279
                      thanks for all the answers!