Pages

Thursday, June 11, 2020

ORA-00060: deadlock detected while waiting for resource

Error :

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-0013000e-00183dd3 302        888       X     121           51       X
TX-0028000e-000b3549 121        51         X     302      888 X

session 888: DID 0001-012E-00002340 session 51: DID 0001-0079-0000040C
session 51: DID 0001-0079-0000040C session 888: DID 0001-012E-00002340

Rows waited on:
Session 888: obj - rowid = 001B352D - AAGzUtADgAABXGaAAC
(dictionary objn - 1783085, file - 224, block - 356762, slot - 2)
Session 51: obj - rowid = 001B6BF2 - AAHDULACaAAEIR7AAH
(dictionary objn - 1797106, file - 154, block - 1082491, slot - 7)

----- Information for the OTHER waiting sessions -----
Session 51:

sid: 51 ser: 15593 audsid: 907952455 user: 34/SAPSR3
flags: (0x1000041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 121 O/S info: user: oraBHP, term: UNKNOWN, ospid: 36985
image: oracle@oraclehost
client details:
O/S info: user: BHPadm, term: , ospid: 9188
machine: oraclehost program: dw.sapBHP_DVEBMGS01@oraclehost (TNS V1-V3)
client info: 0:ML81N:SAPLMLSR
application name: SAPLXMLU, hash value=1026532593
action name: 979, hash value=3010390085
current SQL:
UPDATE "REPOLOAD" SET "UNAM"=:A0,"UDAT"=:A1,"UTIME"=:A2,"L_DATALG"=:A3,"MINOR_VERS"=:A4,"MAJOR_VERS"=:A5 WHERE "PROGNAME"=:A6 AND "R3STATE"=:A7 AND "MACH"=:A8 AND "MAJOR_VERS"=:A9
----- End of information for the OTHER waiting sessions -----

Information for THIS session:
----- Current SQL Statement for this session (sql_id=c36b6kkw57c3t) -----
UPDATE "REPOSRC" SET "SDATE"=:A0,"STIME"=:A1 WHERE "PROGNAME"=:A2 AND "R3STATE"=:A3 AND ("SDATE"<:A4 OR ("SDATE"=:A5 AND "STIME"<:A6) )
*** 2020-06-09 20:52:47.893
Attempting to break deadlock by signaling ORA-00060


Solution :

Find out if it is an APPLICATION or DATABASE deadlock as shown below

Application deadlocks :

                                                 ---------Blocker(s)--------            ---------Waiter(s)---------
Resource Name  process session holds waits         process session holds waits
TX-0013000e-00183dd3  302        888       X               121           51       X
TX-0028000e-000b3549  121        51         X                 302       888  X

Shutdown the SAP Instance and Database, Clean all the application running process,make sure no process
running and then restart the system.

Oracle deadlocks :

                                                ---------Blocker(s)--------            ---------Waiter(s)---------
Resource Name  process session holds waits         process session holds waits
TX-0013000e-00183dd3  302        888       X               121           51       S
TX-0028000e-000b3549  121        51         X                 302       888  S

Follow SAP Note : 84348

No comments:

Post a Comment