Solved

ORA-00164: autonomous transaction disallowed within distributed transaction

Posted on 2004-08-11
19
3,009 Views
Last Modified: 2012-05-05
Hi all,
I have a problem when I work with 2 bases: local base and remoted base

I have a procedure store qui get data from table A of remoted base (via dblink) and insert into a table B of local base:

PROCEDURE UPDATE_A_B ...
...
BEGIN
INSERT INTO LOCAL.B(B.COL1, B.COL2, B.COL3, ... B.COL_N)
select A.COL1, A.COL2, A.COL3, ... A.COL_N from A@REMOTED_BASE;

END;

Key of table LOCAL.B are (B.COL1, B.COL2)

on table LOCAL.B, I have a trigger:

CREATE OR REPLACE TRIGGER TRG_B BEFORE INSERT
ON B
FOR EACH ROW
DECLARE
id_line NUMBER;
PRAGMA autonomous_transaction;
BEGIN
      SELECT MAX(B.COL2)+1 INTO id_line FROM B WHERE B.COL2 = :NEW.COL2;
      id_line := NVL(id_line, 1);
      :NEW.ACALT_LIGNE := id_line;
      COMMIT;
END;

when I execute procedure UPDATE_A_B I get an error:
ORA-00164: autonomous transaction disallowed within distributed transaction
ORA-04088: error when execute trigger LOCAL.TRG_B

I have found this problem and I know this problme exites only on Oracle 8i
but I couldn't upgrade to Oracle 9i

How could I resovle this problem ???
0
Comment
Question by:ndhai
  • 8
  • 3
  • 2
  • +3
19 Comments
 
LVL 1

Expert Comment

by:JanMah
Comment Utility
Hi,

From the sound of it, you want the trigger transaction to get rolled back when the PROCEDURE UPDATE_A_B is failed for some reason?

Is that what you want to achieve?

JanMah

0
 

Author Comment

by:ndhai
Comment Utility
My trigger is executed when I insert into this table from my application WEB
in fact, there are 2 cases following :
case 1: the data are inserted from my application WEB
case 2: the data are inserted from the remoted data

You can imagine the data in table B :
table B:
CommandOfProduction
User ID (COL1)   LINE_ID(COL2)  PROD_ID (COL3 = foreign key)
USER 1              1                     <=== first command of User 1

If User 1 command new production, then
CommandOfProduction
User ID (COL1)   LINE_ID(COL2)  PROD_ID (COL3 = foreign key)
USER 1              1                     <=== first command of User 1
USER 1              2                     <=== 2nd command of User 1
...


Orther part, you can see this problem here  (in this, it propose to upgrade to Oracle 9i ??? )http://www.databasejournal.com/features/oracle/article.php/1550951
0
 

Author Comment

by:ndhai
Comment Utility
adding, I can disable this trigger TR_B by EXECUTE IMMEDIATE 'ALTER TRIGGER TRG_ACHAT_ALIMENT DISABLE'; and enable it by EXECUTE IMMEDIATE 'ALTER TRIGGER TRG_ACHAT_ALIMENT ENABLE'; but I dont want to do this ... :(
0
 
LVL 1

Expert Comment

by:JanMah
Comment Utility
Still I am not clear what you want to achieve and why you are using autonomous transaction in this case.

There is a known limitation of autonomous transaction in oracle 8i.

If you want to use the trigger for populating the Id then you can go for sequences. Even you can modify the trigger to achieve this functioanlity without autonomous transaction.

One will use autonomous transaction when there has to be a seperate transaction irrespective of whether the calling process commits or roll back the transaction. (Eg. error logging).

JanMah


0
 

Accepted Solution

by:
gupta_nag earned 34 total points
Comment Utility
hello ndhai,

Generally Autonomous treansactions will help to maintain transactions independently with main transactions and Oracle8i does not support autonomous transactions within a distributed query.

In Oracle 8.1.7, one of the ways to work around the limitation is to use the control statement SET TRANSACTION READY ONLY in your programs. However, the use of the SET TRANSACTION READ ONLY control statement means only a SELECT statement can follow the autonomous transaction and no other DML is permitted. Using SET TRANSACTION READ ONLY just after the BEGIN statement will not give any errors as the transaction is made READ ONLY and, as a result, no changes are made to the remote database.

But in your case you are not firing any ddl statements in trigger then what is the use of PRAGMA autonomous_transaction and commit statements what i feel from your logic that your given.


Naegendra Gupta
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
Gupta is correct.  Oracle does not support the combination of autonomous transactions with distributed transactions.  You will have to remove the autonomous transactions from any transactions that can be distributed.

No, this combination is not supported in Oracle9 either so an upgrade will not get you past this.
0
 

Expert Comment

by:gupta_nag
Comment Utility
If the trigger you are given is the only logic your are applying then you can comment the one what iam commented below.

CREATE OR REPLACE TRIGGER TRG_B BEFORE INSERT
ON B
FOR EACH ROW
DECLARE
id_line NUMBER;
-- PRAGMA autonomous_transaction;
BEGIN
     SELECT MAX(B.COL2)+1 INTO id_line FROM B WHERE B.COL2 = :NEW.COL2;
     id_line := NVL(id_line, 1);
     :NEW.ACALT_LIGNE := id_line;
   --  COMMIT;
END;

as you are not firing any dml statements.
0
 

Author Comment

by:ndhai
Comment Utility
@JanMah:
I understand what you mean:
TABLE B
ID(by sequence)     USER_ID (foreign key) .... others COLs ...

and I know use the sequence for ID but in this case I have to archive like that ( :( , I counldn't change the structure of table B for some raisons)


@gupta_nag + markgeer : I knowed and tried with SET TRANSACTION READ ONLY but in this case, the ID of line isn't correct :(

thanks alot

Now, If we have no solution, so I have to disable the TR_B and generate the ID = max(ID) of User + 1, put it into table B. When I finish, I set enable for TR_B but It seems not well for security because of the others transactions could arrive (ADD, DEL, or UPDATE) my table B

Hai NGUYEN
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:ndhai
Comment Utility
@gupta_nag: for new your remark

this trigger doesn't work if the transactions (INSERT) is arrived by Web Application
0
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 33 total points
Comment Utility
Also, this statement:

SELECT MAX(B.COL2)+1 INTO id_line FROM B WHERE B.COL2 = :NEW.COL2;

Will cause MUTATING ERROR! because trigger on table B and select on table B.


0
 

Author Comment

by:ndhai
Comment Utility
voilà, then we have to use autonomous_transaction in trigger TR_B  :)
sans doute :)
0
 

Expert Comment

by:gupta_nag
Comment Utility
That's exactly right Mike

but in the trigger you are just assigning next sequence no as you cant select the table in the trigger on the same table you have to change the code every where in the DDL statements some thing like the below

n_count number(3);
 SELECT MAX(B.COL2)+1 INTO n_count FROM B WHERE B.COL2 = @COL2;
and use n_count in the insert statment.



0
 

Author Comment

by:ndhai
Comment Utility
@gupta_nag : comme I said above, but It is very dangerous if there are 2 or more transactions who arrive in same time....
0
 
LVL 5

Assisted Solution

by:gmyers
gmyers earned 33 total points
Comment Utility
I think the autonomous transaction was there as a dodgy workaround to the mutating table issue highlighted by MikeOM_DBA.

Another (dodgy) workaround would be to set :NEW.ACALT_LIGNE to some non-real value (such as -1) and then have an after update trigger for the statement (ie not FOR EACH ROW) along the lines of
update B alias_1
set ACALT_LIGNE =
(select nvl(max(...)+1,1)
from b alias_2
where alias_2.col2=alias_1.col2)
where acalt_ligne = -1

Both are dodgy in that they don't cater for concurrent inserts to the table by different database sessions for the same 'col2', and probably not even inserting multiple rows for the same 'col2' within the same session.

Suggest you throw away this trigger, and tell us exactly what/how/when this ACALT_LIGNE is supposed to work, and see if there's a proper solution.
0
 

Author Comment

by:ndhai
Comment Utility
Sorry but I made a mistake in TR_B
It must be:

CREATE OR REPLACE TRIGGER TRG_B BEFORE INSERT
ON B
FOR EACH ROW
DECLARE
id_line NUMBER;
PRAGMA autonomous_transaction;
BEGIN
     SELECT MAX(B.COL2)+1 INTO id_line FROM B WHERE B.COL2 = :NEW.COL2;
     id_line := NVL(id_line, 1);
     :NEW.COL2 := id_line;
     COMMIT;
END;

I dont want to change structure of table B because It exists and for some raisons I coundn't explain to you :(
0
 

Author Comment

by:ndhai
Comment Utility
Sorry but I made a mistake in TR_B (huhu, one more time)
It must be:

CREATE OR REPLACE TRIGGER TRG_B BEFORE INSERT
ON B
FOR EACH ROW
DECLARE
id_line NUMBER;
PRAGMA autonomous_transaction;
BEGIN
     SELECT MAX(B.COL2)+1 INTO id_line FROM B WHERE B.COL1 = :NEW.COL1;
     -- COL1 is USER_ID
     id_line := NVL(id_line, 1);
     :NEW.COL2 := id_line;
     -- COL2 is LINE OF COMMAND
     COMMIT;
END;
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now