This discussion is archived
7 Replies Latest reply: Dec 6, 2012 3:04 PM by 946279 RSS

transaction over a few instances?

946279 Newbie
Currently Being Moderated
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 Guru Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    thanks! could you give some high level code template for this situation? this would be very helpful.
  • 3. Re: transaction over a few instances?
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    thanks for all the answers!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points