sporadic ORA-01591 error in AFTER INSERT trigger
Date: Tue, 12 May 2009 11:25:28 -0500
We recently setup an AFTER INSERT trigger on a table containing XMLType columns, which captures insert/update/delete operations into a shadow table for processing by Change Data Capture (CDC). Basically it just adds an action column to distinguish insert/update/delete operations, and inserts * all* columns with the XMLType beasties converted to CLOB. Absolutely nothing queries the shadow table directly... it's there simply to support CDC processing by logminer.
The database in question is a 3-instance RAC beastie, running version 188.8.131.52 under Sun. Here's a (very slightly slightly) simplified example of the trigger:
create or replace trigger shadow.tr_TRANSACTIONINFO
after insert or update or delete on app.TRANSACTIONINFO
for each row
if inserting then
l_action := 'I';
elsif updating then
l_action := 'U';
l_action := 'D';
if l_action in ('I ','U') then
- insert/update insert into shadow.st_TRANSACTIONINFO ( action, col1, col2, col3 ) values ( l_action, :new.col1, :new.col2, XMLType.GetClobVal(:new.col3) ); else
- delete insert into shadow.st_TRANSACTIONINFO ( action, col1, col2, col3 ) values ( l_action, :old.col1, :old.col2, XMLType.GetClobVal(:old.col3) ); end if;
Unfortunately, the vendor is reporting that the trigger sometimes fails with an ORA-01591 error (lock held by in-doubt distributed transaction).
30 Apr 2009 09:20:25,210 - ERROR populateTransactionInfo
(TransactionInfoDAO.java:780) - WIGDBException exception occured while
fetching dependanat transaction
30 Apr 2009 09:20:25,233 - ERROR saveWithAllXML
(TransactionBMPHelper.java:636) - :: Error while .. java.sql.SQLException:
ORA-01591: lock held by in-doubt distributed transaction 48.15.38461 ORA-06512: at "SHADOW.TR_TRANSACTIONINFO", line 16 ORA-04088: error during execution of trigger 'SHADOW.TR_TRANSACTIONINFO' at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
This doesn't make any sense to me, because the trigger accesses only the :OLD/:NEW column values and the shadow table (which shouldn't be involved in any distributed transactions). The vendor does have a BEFORE INSERT/UPDATE trigger, however, which sometimes performs a simple SELECT across a dblink. It doesn't seem like it should impact my piece, however.
For the time being I've enabled event '1591 trace name errorstack level 10', to hopefully capture more information next time the problem occurs. I would, of course, welcome any troubleshooting suggestions and advice from the oracle-l crowd.
-- "I'm too sexy for my code." - Awk Sed Fred. -- http://www.freelists.org/webpage/oracle-lReceived on Tue May 12 2009 - 11:25:28 CDT