RE: ORA-01591 after client upgrade to Windows XP - weird

Oh, and there is an entry in dba_2pc_pending on the local database but not on the remote database. It has a state of 'prepared' while it's holding the lock that causes the problem and 'forced rollback' after I force the rollback.

Jay Miller

-----Original Message-----
Sent: Thursday, April 24, 2003 5:22 PM
To: Multiple recipients of list ORACLE-L

Not sure if this is relevant... are there entries in dba_2pc_pending on either of the Oracle databases?

If there are, somehow a set transaction read only should be done at the beginning the remote transaction.

You didn't have this problem before though, so probably that is not the cause.

Pat.

-----Original Message-----
Sent: Thursday, April 24, 2003 4:22 PM
To: Multiple recipients of list ORACLE-L

This is very strange. This week we started having multiple "ORA-01591: lock held by in-doubt distributed transaction" errors on the database. This prevented one of our major customer web functions from working (very bad :). It was easy enough to force a rollback but the problem recurred.

We've tracked down the source of the in-doubt transaction to an old Powerbuilder application which queries from this database across a database link to another database. It only does a select, no insert, update, delete. The web application also queries across that same link.

We see an exclusive lock present from the user but it is not on a table, it is holding the lock on a rollback segment.

Further investigation has shown that users of this Powerbuilder app had started receiving other errors at app. the same time that the web application started malfunctioning (the user who causes it doesn't see any errors from the in-doubt transaction). These occurred after their workstations were upgraded to XP.

I can't think of why an upgrade to XP would cause a query that has worked perfectly for the last 4 years to suddenly start causing in-doubt transactions. Has anyone experienced anything similar? The code for the web app hasn't changed since January, the code for the Powerbuilder app hasn't changed in at least 2 years.

In case it helps, this is the query being issued. It causes no errors if run from SQL Plus.

SELECT "TLS"."AS_OF_BUSINESS_DATE"
, min("ACT"."CURRENT_TOTAL_COMMISSION")
"CURRENT_TOTAL_COMMISSION"
, min("ACT"."CURRENT_YEAR") "CURRENT_YEAR"
, min("ACT"."PREVIOUS_YEAR") "PREVIOUS_YEAR"
, min("ACT"."PREV_2_YEAR") "PREV_2_YEAR"
, min("ACT"."PREV_3_YEAR") "PREV_3_YEAR"
, min("ACT"."PREV_4_YEAR") "PREV_4_YEAR"
, min("ACT"."PREV_5_YEAR") "PREV_5_YEAR"
 FROM "ANNUAL_COMMISSION_TOTALS"@NJCCP "ACT"                  , "TABLE_LOAD_STATUS"@NJCCP "TLS"
 WHERE ( "TLS"."TABLE_NAME" = 'ANNUAL_COMMISSION_TOTALS' ) and

                ( "ACT"."ACCOUNT_ID" = 3353149 )
GROUP BY "TLS"."AS_OF_BUSINESS_DATE"; Primary database is 8.1.7.2
Remote database is 8.1.6.2
OS is Solaris 2.6

Our temporary solution has been to tell the user to stop working. The problem has not recurred since then.

Any other suggestions for tracing the problem? For a similar problem I saw in Metalink Oracle recommended setting
event = "1591 trace name errorstack level 10" but that will require restarting the database which is not a trivial thing to get permission for. I think my next step will be to run SQL Trace for the user and try to duplicate the problem but I'll welcome other ideas.

Thanks,
Jay Miller

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: JayMiller_at_TDWaterhouse.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Boivin, Patrice J
  INET: BoivinP_at_mar.dfo-mpo.gc.ca

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: JayMiller_at_TDWaterhouse.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).