ora 00060 deadlock error

[Former Member]

Hi All,

am facing deadlock error and i have given the trace file.I have reade the sap note 84348 but unfortunately am unable to troubleshoot this error.

prdadm@psapr3ngn:/oracle/PRD/saptrace/usertrace> more  prd_ora_9512.trc

/oracle/PRD/saptrace/usertrace/prd_ora_9512.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

ORACLE_HOME = /oracle/PRD/102_64

System name:    SunOS

Node name:      psapr3ngn

Release:        5.9

Version:        Generic_122300-46

Machine:        sun4u

Instance name: PRD

Redo thread mounted by this instance: 1

Oracle process number: 94

Unix process pid: 9512, image: oracle@psapr3ngn

*** 2012-08-15 11:11:12.051

*** ACTION NAME:(6695) 2012-08-15 11:11:12.025

*** MODULE NAME:(SAPLARFC) 2012-08-15 11:11:12.025

*** SERVICE NAME:(PRD) 2012-08-15 11:11:12.025

*** SESSION ID:(257.55460) 2012-08-15 11:11:12.025

DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a

deadlock due to user error in the design of an application

or from issuing incorrect ad-hoc SQL. The following

information may aid in determining the deadlock:

Deadlock graph:

                       ---------Blocker(s)--------  ---------Waiter(s)---------

Resource Name          process session holds waits  process session holds waits

TX-00140001-0003ea85        94     257     X             23     263           X

TX-00040011-001c3cc1        23     263     X             94     257           X

session 257: DID 0001-005E-00000135     session 263: DID 0001-0017-000002DB

session 263: DID 0001-0017-000002DB     session 257: DID 0001-005E-00000135

Rows waited on:

Session 263: obj - rowid = 0004EA6E - AABOp0ACUAAAFMAAAB

  (dictionary objn - 322158, file - 148, block - 21248, slot - 1)

Session 257: obj - rowid = 0004EA66 - AABOpsACTAAAGZEAAB

  (dictionary objn - 322150, file - 147, block - 26180, slot - 1)

Information on the OTHER waiting sessions:

Session 263:

  pid=23 serial=25397 audsid=63204740 user: 8/SAPR3

  O/S info: user: prdadm, term: , ospid: 255, machine: psapr3ngn

            program: dw.sapPRD_DVEBMGS00@psapr3ngn (TNS V1-V3)

  client info: 0

  application name: CL_ABAP_LIST_PARSER===========CP, hash value=4022195676

  action name: 125, hash value=441625863

  Current SQL Statement:

  DELETE FROM "ARFCSSTATE" WHERE "ARFCIPID" = :A0 AND "ARFCPID" = :A1 AND "ARFCTIME" = :A2 AND "ARFCTIDCNT" = :A3 AND "ARFCDEST" = :A4 AND "ARFCLUWCNT" = :A5

End of information on OTHER waiting sessions.

Current SQL statement for this session:

DELETE FROM "ARFCSDATA" WHERE "ARFCIPID" = :A0 AND "ARFCPID" = :A1 AND "ARFCTIME" = :A2 AND "ARFCTIDCNT" = :A3

Regards

Ram

[ashish_vikas · Accepted solution]

Dear Ramakrishnan,

Your Deadlock is coming for ARFCSSTATE & ARFCSDATA tables.

Current SQL Statement:

  DELETE FROM "ARFCSSTATE" WHERE "ARFCIPID" = :A0 AND "ARFCPID" = :A1 AND "ARFCTIME" = :A2 AND "ARFCTIDCNT" = :A3 AND "ARFCDEST" = :A4 AND "ARFCLUWCNT" = :A5

End of information on OTHER waiting sessions.

Current SQL statement for this session:

DELETE FROM "ARFCSDATA" WHERE "ARFCIPID" = :A0 AND "ARFCPID" = :A1 AND "ARFCTIME" = :A2 AND "ARFCTIDCNT" = :A3

Please check if Note 1445107 can be implemented for your system.

Note 1445107 - Deadlocks occur in the DB tables ARFCSSTATE and ARFCSDATA

Regards

Ashish

[Former Member · Accepted solution]

Hi Ashish,

Thanks for your urgent help and additionally i have taken note that i mentioned in the first reply and found the decimal value.Below is the output too.

hexa value  0004EA6E ----->decimal value is 322158

hexa value  0004EA66------>decimal value is 322150

SQL> SELECT owner, object_name, object_id FROM DBA_OBJECTS

WHERE object_id=322158;

SQL> SELECT owner, object_name, object_id FROM DBA_OBJECTS

WHERE object_id=322150;

SQL> SELECT owner, object_name, object_id FROM DBA_OBJECTS

WHERE object_id=322158  2  ;

OWNER

--------------------------------------------------------------------------------

OBJECT_NAME

--------------------------------------------------------------------------------

OBJECT_ID

----------

SAPR3

ARFCSSTATE

    322158

SQL> SELECT owner, object_name, object_id FROM DBA_OBJECTS

WHERE object_id=322150  2  ;

OWNER

--------------------------------------------------------------------------------

OBJECT_NAME

--------------------------------------------------------------------------------

OBJECT_ID

----------

SAPR3

ARFCSDATA

    322150

Let me know anything further has to be done from my end and i will go through the note suggested by you and will provide you the update later.

Many thanks ashish

Regards

Ram

[ashish_vikas · Accepted solution]

Here also you found same tables : ARFCSSTATE & ARFCSDATA.

I will suggest you first check if this note is applicable for your System Release or not and if this can be implemented. If not, may be we can find another relevant SAP Note for this problem. Let us know your SAP Release in that case.

Regards

Ashish

[Former Member · Accepted solution]

Dude,

i have SAPKB64021 which is far below the SAP note suggested,Anyhow we are going for support pack activity on coming friday and i will implement the SAPKB64029

Thanks for your valuable solution and pinpointed one answer

[Former Member · Accepted solution]

If you basis SP is far below the Basis SP in which the corrections of OSS note have been included then what is the issue in applying the OSS note. You can't apply the note if Basis SP was equal to hgher than the one specified in the note.

Regards.

Ruchit

[Former Member · Accepted solution]

Hi Ruchit,

Yes you are absolutly right,,,,Anyhow issue is resolved

Regards

Ram