• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3202
  • Last Modified:

ORA-00164: autonomous transaction disallowed within distributed transaction

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
ndhai
Asked:
ndhai
  • 8
  • 3
  • 2
  • +3
3 Solutions
 
JanMahCommented:
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
 
ndhaiAuthor Commented:
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
 
ndhaiAuthor Commented:
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
JanMahCommented:
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
 
gupta_nagCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
gupta_nagCommented:
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
 
ndhaiAuthor Commented:
@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
 
ndhaiAuthor Commented:
@gupta_nag: for new your remark

this trigger doesn't work if the transactions (INSERT) is arrived by Web Application
0
 
MikeOM_DBACommented:
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
 
ndhaiAuthor Commented:
voilĂ , then we have to use autonomous_transaction in trigger TR_B  :)
sans doute :)
0
 
gupta_nagCommented:
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
 
ndhaiAuthor Commented:
@gupta_nag : comme I said above, but It is very dangerous if there are 2 or more transactions who arrive in same time....
0
 
gmyersCommented:
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
 
ndhaiAuthor Commented:
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
 
ndhaiAuthor Commented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now