sporadic ORA-01591 error in AFTER INSERT trigger

From: Adric Norris <spikey.mcmarbles_at_gmail.com>
Date: Tue, 12 May 2009 11:25:28 -0500
Message-ID: <d4beff360905120925h30049a6ak8f2b63e9b9ce1736_at_mail.gmail.com>

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 11.1.0.7 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
declare

   l_action char(1);

begin

   if inserting then

      l_action := 'I';
   elsif updating then

      l_action := 'U';
   else

      l_action := 'D';
   end if;

   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;

end;
/

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.

Thanx!

-- 
"I'm too sexy for my code." - Awk Sed Fred.

--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 12 2009 - 11:25:28 CDT