RE: How do I relate local_trans_id in dba_2pc_pending to a partic

Never mind. I finally got some time to troll through metalink and came up with:

select e.sql_text, d.osuser,d.username
from v$transaction c, v$session d, v$sqlarea e where d.taddr = c.addr

and e.address = d.prev_sql_addr 
and c.xidusn = <1st Part of Transaction -ID> 
and c.xidslot =<2nd Part of Transaction -ID> 
and c.xidsqn = <3rd Part of Transaction -ID>; 

Thanks anyway!
Jay Miller

-----Original Message-----
Sent: Friday, April 25, 2003 10:28 AM
To: 'ORACLE-L_at_fatcity.com'
particular session?

I'm having in-doubt distributed transaction problems.

How can I use the information in dba_2pc_pending to associate the in-doubt transaction with a specific session? Local_trans_id doesn't seem to relate directly to anything in v$transaction.

Also it would help if I could identify exactly which lock is causing the problem. There are usually several in place at any given point in time. Is there a way to relate the in-doubt transaction to a specific lock?

Thanks in advance.

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).