Re: Q: Remote stored procedure call

iliber1_at_umbc.edu (Ilya Liberman) wrote:
>We are having a problem with Commits/Rollbacks in
>a distributed database system (ORA-2074).
>The system is made up of two Oracle 7 database instances
>running on two separate Unix boxes on a network and these
>two instances are connected by a public database link.
>A stored procedure is located on instance #2. It contains
>savepoints, commits and rollbacks. When we attempt to call
>this procedure from instance #1 - we get the above error.
>However, removing commits and rollbacks eliminates the error.

Ilya,

 In a distributed transaction the instance to which the application connects (your instance #1) coordinates all commit, rollback, and recovery processing. Commits must be initiated at the coordinating instance and are propagated to the other instances using a 2-phase commit protocol. The coordinating instance keeps track of this process and knows how to recover if there is an interruption of any kind. This design ensures that a distributed transaction either completes (commits) at all nodes or is rolled back at all nodes.

 The decision to commit a transaction is up to the originating application and instance. You cannot turn the process around and have the commit initiated at a secondary instance.  

 You might want to take a look at your application design and reconsider why you want a secondary node to commit changes independent of the application.

 I found this in Chapter 7 of the Oracle7 Server Application Developer's Guide":

  "A procedure that includes the transaction control statements  COMMIT, ROLLBACK, and SAVEPOINT cannot be executed by a trigger  or a procedure participating in a distributed transaction; a  runtime error is returned in such cases."

/b

-- 
Bill Manry  -  Mainframe and Integration Technologies  -  Oracle Corporation
Standard disclaimer applies.