?
Solved

Insert code in Trigger causes ORA01591 lock held by in doubt transaction

Posted on 2005-04-07
9
Medium Priority
?
2,437 Views
Last Modified: 2008-01-09
Hi there,
We use the Oracle transparent gateway for informix to access an informix db from oracle.
This is generally working.

When I create a trigger on the oracle db and it is fired, it causes the ORA-01591 error message. It looks like there is a problem with the 2point commit phase.

Here the trigger code:
CREATE OR REPLACE TRIGGER BTO_QMAP.AI_CELLS
AFTER INSERT
ON BTO_QMAP.QMAP_GROUPS_CELLS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (
new.CELLID like '%G'
      )
BEGIN
   insert into "qmap_groups_cells"@IFMX_METRICA ("fk_subgroup_name","fk_user_id","cellid")
   VALUES (:new.fk_subgroup_name, :new.fk_user_id, :new.cellid);
END ;

The values are inserted correctly into the informix database. But in oracle a lock is created for this dirstributed transaction. After accessing the involved table, the ORA-01591 "lock held by in-doubt transaction" is raised. After a COMMIT force 'transaction id', the lock is released.

When I execute a similar insert statement directly (not via trigger), then everything works fine.

Any ideas would be appreciated....

br
Kenny
0
Comment
Question by:Kenny2k4
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 13724549
It is normal in a distributed transaction for the table to be locked.
Oracle will normally and automatically rollback the transaction when all the databases involved are available again (after the network is restored).
There is a discussion of this in the
Oracle8i Distributed Database Systems , chapter 4
If your system includes a transaction processing monitor (TPM), then check for logs files on the TPM side to see what happened.


Also this might be caused by the Bug 2740481
This issue is fixed in       9.2.0.5 (Server Patch Set) and 10g Production Base Release.
Versions confirmed as being affected 8.1.7.4 .
0
 

Author Comment

by:Kenny2k4
ID: 13724618
It cannot be caused by network problems.
It occurs everytime when the trigger is fired but NEVER when the same sql is sent directly from SQLpus or TOAD.

I read the chapter 4. There is written, that it automatically should recover, when the connection can be established. But there is no network problem. Without a commit force, the lock would be indefinitely.

BTW: We have 9.2.0.0 Version.

Where can I get a description of the bug 2740481 ?

br
Kenny


0
 
LVL 48

Expert Comment

by:schwertner
ID: 13724872
You have to see for timeouts on both Oracle and Informix sites. As you read from my posting the Administrator or Coordinator
of 2 Phase Commit Protocol looses control over the process. When you use TOAD or SQL*Plus you of course work in
homogenious environment and 2PC Protoco is not involved.

You have to look which version of Transparent Gateway are you using.
Possibly you will need a relevant and newer version.


Bug 2740481 is property of Oracle :-))) !!!!!
You can read about it on the Metalink (also property of Oracle, but with restricted access).

Please as SYS run

select * from v$version

Because 9.2.0.0 doesn't exists. This product line begins from 9.2.0.2 and ends on 9.2.0.6 (Windows)

I will strongly and friendly recommend ASAP to upgrade to 9.2.0.6 (9.2.0.5 on Linux).

I did this two month ago in attempt to avoid bugs.

Also be aware that every version of Oracle comes with his own versions of connection tools like JDC, ODBC.
Always use the newest set of connection software.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Kenny2k4
ID: 13725602
ok. Our version is 9.2.0.4.

I am just the developer and not the admin of this db. I will recommend the upgrade to 9.2.0.6 version to the dba.

The transparent gateway should be the latest version. It was installed about a month ago....

Did I understand you correctly ? This means, that if the same query is executed from TOAD it does not use the same commit protocol as when the same query is run by the database trigger ?

br
Kenny

0
 
LVL 5

Expert Comment

by:helpneed
ID: 13725690
hi

An attempt was made to access resource that is locked by a dead two-phase commit transaction that is in prepared state

The database administrator should query the PENDING_TRANS$ and related tables, and attempt to repair network connection(s) to coordinator and commit point. If timely repair is not possible, the database administrator should contact the database administrator at the commit point if known or the end user for correct outcome, or use heuristic default if given to issue a heuristic COMMIT or ABORT command to finalize the local portion of the distributed transaction.

try this also

dbms_transaction.purge_lost_db_entry(number)

Match the transaction number in the message with the GLOBAL_TRAN_ID
column of the DBA_2PC_PENDING table to determine the database link and          
the state of the transaction.        
Attempt to repair network connections to the coordinator and commit point, if necessary.
If timely repair is not possible, contact the database administrator at the commit point, if known, to resolve the pending transaction

regards
0
 
LVL 4

Accepted Solution

by:
plamen73 earned 1000 total points
ID: 13725848
I am not able to reproduce this on my Oracle 9.2.0.4.
However I would suggest to enclose the remote access in an autonomous transaction:
-------------------------------------------
CREATE OR REPLACE TRIGGER BTO_QMAP.AI_CELLS
AFTER INSERT
ON BTO_QMAP.QMAP_GROUPS_CELLS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (
new.CELLID like '%G'
     )
declare
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  insert into "qmap_groups_cells"@IFMX_METRICA ("fk_subgroup_name","fk_user_id","cellid")
  VALUES (:new.fk_subgroup_name, :new.fk_user_id, :new.cellid);
  commit;
exception
  when others then rollback; raise;
END ;
------------------------------------------------

0
 

Author Comment

by:Kenny2k4
ID: 13725941
@plamen73

Your suggestion solved the problem :-)

Thanks a lot.

Is there any negative influence that has to be considered when it is run as an autonomous transaction ?

Thanks an br
Kenny
0
 
LVL 4

Expert Comment

by:plamen73
ID: 13726071
There is one major:
imagine the remote insert is successful. the commit statement will make it permanent.
if the trigger fails after this commit you will end with inconsistent data. Remotely you will have the record and locally - not.
So, keep this trigger as simple as possible

In general, it is a good practice to move the entire trigger code (when it is more complicated that just getting :new.value from sequence) in a stored procedure and call it from the trigger.
The reason is that oracle reads, parses the statements etc... from the trigger every time it is executed, while the procedure will be parsed and cached once.
Also, trigger are not my favorite objects...:(
0
 

Author Comment

by:Kenny2k4
ID: 13726172
Thanks for the inputs. I already did a procedure to call from the trigger. I just put it here directly in the PL/SQL block because of easier understanding.

I am also not a big fan of triggers. However, this trigger is used to keep one table synchronised between the ORACLE and the INFORMIX db's. That means, it needs at the end 3 triggers (insert, update, delete) that are very simple.

Is there any better way to keep the table synchronised in both db's (Oracle and Informix)

Thanks for any info and br
Kenny
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

839 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