Troubleshooting Dead locks in SAP

[Saikumar]

HI All,

I observed there are multiple dumps occurring on my instance i.e Runtime error DBIF_RSQL_SQL_ERROR . After investigating st22 , sm21 and database logs understand that this was because of dead locks. And in oracle log it was clearly mentioned that " The following error is not oracle error: it is a dead lock due to user error in the design of an application of form issuing incorrect adhoc SQL "  . Now my question is how to check the history of dead locks .  And  my users are connecting from SCM system to ECC using RFC i could not even able to trace the user and transaction occurred during that point.

Any suggestions on how to go further to identify the exact cause for this dead lock..

Thank you.

Sai.

[Johan_sapbasis]

HI,

In addition to CRMRFCPAR this means you probably have a resource issue with your outbound smq1 and smq2 inbound queues on the system check your resources around this area and for failed updates possibly sm13.

check notes 565527 726148

Kind Regards,

Johan

[Saikumar]

Hi Johan,

Did not find any sm 13 update failuers .  And note 565527 is not released .  we have adiquate number of resources alligned and multiple application servers are available.

Regards,

Sai

[Johan_sapbasis]

Hi,

Please investigate also failed sm37 jobs on same time frame as st22 dump , maybe jobs conlflicting with same table access as remote call from system trying to update data

Kind Regards,

Johan

[Saikumar]

HI All,

Thank you for all your suggestions . I have tried everything and finally reached here , even i tried to take the help from our CRM team to identify which action/transaction  or Bdoc triggered  during that  time . could not get the require details . So tried self to idenfiy the transaction / user trying to access at the point of time causing this dead lock. Further will try and investigate more.

Thanks & Regards,

Sai

[former_member207186]

Hi,

After investigating st22 , sm21 and database logs understand that this was because of dead locks. And in oracle log it was clearly mentioned that " The following error is not oracle error: it is a dead lock due to user error in the design of an application of form issuing incorrect adhoc SQL "

Regarding this deadlock issue, you can refer to below SAP KBA and SAP Note for troubleshooting:

1872040 - "Handling ORA-00060 error - application deadlock issue"

84348 - "Oracle deadlocks, ORA-00060"

Regards,

Bíborka

[Saikumar]

HI All,

Thanks for your revert.

Can you all please help on "to trace the user and transaction occurred during that point " Because its through RFC in all the logs i am able to see only RFC user.

@Biborka ,

Thank you for the notes : i have gone through them already and could not found the relavant solution.

@ James , sriram

Have checked through below log information.

"

ORA-00060: Deadlock detected. More info in file /oracle/XXX/saptrace/diag/rdbms/xxx/XXX/trace/XXX_ora_39714942.trc.
Wed Apr 22 00:18:48 2015
   84348 - Oracle deadlocks, ORA-00060
----- Current SQL Statement for this session (sql_id=2m3zu9mgzxvnm) -----
DELETE FROM "NAST" WHERE "MANDT"=:A0 AND "KAPPL"=:A1 AND "OBJKY"=:A2 AND "KSCHL"=:A3 AND "SPRAS"=:A4 AND "PARNR"=:A5 AND "PARVW"=:A6 AND "ERDAT"=:A7 AND "ERU
HR"=:A8

*** 2015-04-09 11:04:14.592
Attempting to break deadlock by signaling ORA-00060

*** SESSION ID:(1.41905) 2015-04-22 00:21:42.040
*** CLIENT ID:(ECPCPIC) 2015-04-22 00:21:42.040
*** SERVICE NAME:(SYS$USERS) 2015-04-22 00:21:42.040
*** MODULE NAME:(SAPLCRM0) 2015-04-22 00:21:42.040
*** ACTION NAME:(7828) 2015-04-22 00:21:42.040

*** 2015-04-22 00:21:42.040
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-003d0006-003f6147       728       1     X            330     310           X
TX-007d0019-0016a3fa       330     310     X            728       1           X

session 1: DID 0001-02D8-00000388       session 310: DID 0001-014A-000001F4
session 310: DID 0001-014A-000001F4     session 1: DID 0001-02D8-00000388
session 1: DID 0001-02D8-00000388       session 310: DID 0001-014A-000001F4
session 310: DID 0001-014A-000001F4     session 1: DID 0001-02D8-00000388

Rows waited on:
  Session 1: obj - rowid = 00010403 - AAAQQDAAPAAAH6fAAA
  (dictionary objn - 66563, file - 15, block - 32415, slot - 0)
  Session 310: obj - rowid = 00010403 - AAAQQDAAPAAAH6dAAA
  (dictionary objn - 66563, file - 15, block - 32413, slot - 0)

----- Information for the OTHER waiting sessions -----
Session 310:
  sid: 310 ser: 24747 audsid: 36871506 user: 25/SAPXER
    flags: (0x1000041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40009) -/-/INC
  pid: 330 O/S info: user: oraerp, term: UNKNOWN, ospid: 26214842
    image: XXX
  client details:
    O/S info: user: erpadm, term: , ospid: 19306
    machine: XXXXXXXXXXXXX(TNS V1-V3)
    client info: 0::SAPLERFC
    application name: SAPLCRM0, hash value=2285381656
    action name: 7828, hash value=2134877692
  current SQL:
  UPDATE "CRMRFCPAR" SET "RFC_QUEUE"=:A0,"RFC_IN_QUE"=:A1,"BAPINAME"=:A2,"INFO"=:A3,"INACTIVE"=:A4,"DISCARDDAT"=:A5,"USE_IN_Q"=:A6,"SEND_XML"=:A7,"HOLD_DATA"
=:A8,"CRM_REL"=:A9,"LAST_UPD"=:A10,"USERNAME"=:A11,"CHANGEDATE"=:A12,"CHANGETIME"=:A13,"REM_LOGSYS"=:A14 WHERE "MANDT"=:A15 AND "CONSUMER"=:A16 AND "OBJNAME"
=:A17 AND "RFCDEST"=:A18 AND "DOWNLOAD"=:A19

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=f3h2ryat77j5d) -----
UPDATE "CRMRFCPAR" SET "RFC_QUEUE"=:A0,"RFC_IN_QUE"=:A1,"BAPINAME"=:A2,"INFO"=:A3,"INACTIVE"=:A4,"DISCARDDAT"=:A5,"USE_IN_Q"=:A6,"SEND_XML"=:A7,"HOLD_DATA"=:
A8,"CRM_REL"=:A9,"LAST_UPD"=:A10,"USERNAME"=:A11,"CHANGEDATE"=:A12,"CHANGETIME"=:A13,"REM_LOGSYS"=:A14 WHERE "MANDT"=:A15 AND "CONSUMER"=:A16 AND "OBJNAME"=:
A17 AND "RFCDEST"=:A18 AND "DOWNLOAD"=:A19

*** 2015-04-22 00:21:42.040
Attempting to break deadlock by signaling ORA-00060" "

Regards

Sai

[Sriram2009]

Hi Sai

1. Could you share the ST22 full dump as text format attachment?

2. Could you check this SAP Note and implement the corrections 2147699

BR

SS

[former_member207186]

Hi,

Thank you for your feedback. The provided deadlock graph of the trace indeed indicates an application deadlock as described in suggested SAP KBA 1872040 and affected table is CRMRFCPAR. The corresponding/affected user and application name can also be found/indetified in this graph. In this case I would like to recommend to involve responsible application area in order to solve this issue.

Regards,

Bíborka

[JamesZ]

Hi Sai,

According to the graph you pasted, this is a pure applciation deadlock, which means oracle has nothing to do with this case:

Deadlock graph:

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

Resource Name          process session holds waits  process session holds waits

TX-003d0006-003f6147       728       1     X            330     310           X

TX-007d0019-0016a3fa       330     310     X            728       1           X

For more details, you can review SAP note 84348.

So the involved object seems to be a crm object "CRMRFCPAR". So you may need to check with your CRM colleagues. From basis level, we can trace out which SAP user is causing this issue, because SAP always uses sapsr3 user to connect oracle.

Please try to discuss with your crm colleague first.

Best regards,
James

[Saikumar]

HI All,

@Sriram ,

I find the attached st22dump.  And this note is not relating to the issue on the table.

@Bíborka ,

". The corresponding/affected user and application name can also be found/indetified in this graph" . I could not found this user. As i said users are connecting to ECC system from CRM through RFC . How to begin  investigation  from application area end.

Please note right now i am not getting any dumps ... i am looking for stopping this dumps reoccurence.

Regards,

Sai

DBIF_RSQL_SQL_ERROR.TXT.zip

[former_member185954]

Hello Saikumar,

Locate the source of the query

UPDATE "CRMRFCPAR" SET "RFC_QUEUE"=:A0,"RFC_IN_QUE"=:A1,"BAPINAME"=:A2,"INFO"=:A3,"INACTIVE"=:A4,"DISCARDDAT"=:A5,"USE_IN_Q"=:A6,"SEND_XML"=:A7,"HOLD_DATA"
=:A8,"CRM_REL"=:A9,"LAST_UPD"=:A10,"USERNAME"=:A11,"CHANGEDATE"=:A12,"CHANGETIME"=:A13,"REM_LOGSYS"=:A14 WHERE "MANDT"=:A15 AND "CONSUMER"=:A16 AND "OBJNAME"
=:A17 AND "RFCDEST"=:A18 AND "DOWNLOAD"=:A19

This can be done using ST04 transaction (in your ECC), once you find the program executing that query, speak to  your CRM team ask them why do they attempt to set an entry in CRMRFCPAR table, they should be able to help you better.

Regards,

Siddhesh

[former_member207186]

Hi Sai,

The corresponding application name/program can also be found in the trace and in the runtime error log also.

If you search in the provided file you can find the following:

|    Program............. "SAPLCRM0"       

Since provided data indicates application deadlock you should involve CRM area to solve the issue.

You can also refer to Analysing Oracle Deadlocks - ORA-00060 for more information.

Regards,

Bíborka

[JamesZ]

hi Sai,

Does oracle alert log report ora-00060？Which oracle log shows the information  you mentioned below?

" The following error is not oracle error: it is a dead lock due to user error in the design of an application of form issuing incorrect adhoc SQL "

Best regards,
James

[Former Member]

Hi James,

You need to check the usertrace n the oracle logs to get the exact transaction and what caused the deadlock.

You can find under /oracle/<SID>/saptrace/usertrace for older versions.

/oracle/<SID>/saptrace/diag/rdbms/<SID>/<SID>/trace for 11g onwards.

Do verify the lock and see if its reoccuring you need to troubleshoot based on the program and update values in SM12.

Let us know if you need further help.

Regards,

Ram

[Sriram2009]

Hi Sai

Could you share the full dump DBIF_RSQL_SQL_ERROR as attachment?

BR

SS