Deadlock Problem

[Nagesh]

Hi All,

we are getting following error message on our tracle log. Please help
on this.

*** 2005-07-20 12:17:00.974
*** SESSION ID:(166.5758) 2005-07-20 12:17:00.973
DEADLOCK DETECTED
Current SQL statement for this session:
DELETE FROM ASSET_CONTENTS AC WHERE AC.ASSETCONTENTUID IN ( SELECT
A.ASSETCONTENTUID FROM ASSET_CONTENTS A WHERE A.ASSETUID = :1 AND
A.ASSETCONTENTUID NOT IN (SELECT AVC.ASSETCONTENTUID FROM
ASSET_VERSION_CONTENTS AVC WHERE AVC.ASSETUID= :2 ) AND
A.ASSETCONTENTTYPEUID = (SELECT ACT.ASSETCONTENTTYPEUID FROM
ASSET_CONTENT_TYPES ACT WHERE ACT.CODE = 'CONVERTED_PDF') )
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
TM-0000d468-00000000 24 166 SX SSX 27 85
SX SSX
TM-0000d468-00000000 27 85 SX SSX 24 166
SX SSX
session 166: DID 0001-0018-0006E99E session 85: DID 0001-001B-0002ABC7
session 85: DID 0001-001B-0002ABC7 session 166: DID 0001-0018-0006E99E
Rows waited on:
Session 85: obj - rowid = 0000D46F - AAANRvAAGAAAAAAAAA
(dictionary objn - 54383, file - 6, block - 0, slot - 0)
Session 166: obj - rowid = 0000D468 - AAANRoAAGAAAAAAAAA
(dictionary objn - 54376, file - 6, block - 0, slot - 0)
Information on the OTHER waiting sessions:
Session 85:
pid=27 serial=9197 audsid=47833 user: 65/<none>
O/S info: user: root, term: unknown, ospid: , machine:
oxpaps16b.elsevier.co.uk
program: JDBC Thin Client
application name: JDBC Thin Client, hash value=0
Current SQL Statement:
DELETE FROM ASSET_CONTENTS WHERE ASSOCIATEDASSETCONTENTUID = :B1
End of information on OTHER waiting sessions.
===================================================
PROCESS STATE
-------------
Process global information:
process: 372be330, call: 37345dbc, xact: 37b38ce4, curses:
37340f44, usrses: 37340f44
----------------------------------------
SO: 372be330, type: 2, owner: 0, flag: INIT/-/-/0x00
(process) Oracle pid=24, calls cur/top: 37345dbc/37345dbc, flag: (0)
-
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 21
last post received-location: ksqrcl
last process to post me: 372bbe50 84 0
last post sent: 34537 0 16
last post sent-location: ksasnd
last process posted by me: 372b9970 1 6
(latch info) wait_event=0 bits=10
holding 20006540 Parent+children enqueue hash chains level=4
Location from where latch is held: ksqcmi: kslgpl:
Context saved from call: 0
state=busy
recovery area:
Dump of memory from 0x372B8A00 to 0x372B8A08
372B8A00 00000000 00000000 [........]
Process Group: DEFAULT, pseudo proc: 372db58c
O/S info: user: oracle, term: UNKNOWN, ospid: 5408
OSD pid info: Unix process pid: 5408, image: oracle@oxpora16 (TNS
V1-V3)

[IANAL_VISTA]

"Nagesh" <nageshnalluru@gmail.com> wrote in
news:1127474488.615583.118560@g49g2000cwa.googlegroups.com:
Hi All, we are getting following error message on our tracle log. Please help on this. *** 2005-07-20 12:17:00.974 *** SESSION ID:(166.5758) 2005-07-20 12:17:00.973 DEADLOCK DETECTED Current SQL statement for this session: DELETE FROM ASSET_CONTENTS AC WHERE AC.ASSETCONTENTUID IN ( SELECT A.ASSETCONTENTUID FROM ASSET_CONTENTS A WHERE A.ASSETUID = :1 AND A.ASSETCONTENTUID NOT IN (SELECT AVC.ASSETCONTENTUID FROM ASSET_VERSION_CONTENTS AVC WHERE AVC.ASSETUID= :2 ) AND A.ASSETCONTENTTYPEUID = (SELECT ACT.ASSETCONTENTTYPEUID FROM ASSET_CONTENT_TYPES ACT WHERE ACT.CODE = 'CONVERTED_PDF') ) 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 TM-0000d468-00000000 24 166 SX SSX 27 85 SX SSX TM-0000d468-00000000 27 85 SX SSX 24 166 SX SSX session 166: DID 0001-0018-0006E99E session 85: DID 0001-001B-0002ABC7 session 85: DID 0001-001B-0002ABC7 session 166: DID 0001-0018-0006E99E Rows waited on: Session 85: obj - rowid = 0000D46F - AAANRvAAGAAAAAAAAA (dictionary objn - 54383, file - 6, block - 0, slot - 0) Session 166: obj - rowid = 0000D468 - AAANRoAAGAAAAAAAAA (dictionary objn - 54376, file - 6, block - 0, slot - 0) Information on the OTHER waiting sessions: Session 85: pid=27 serial=9197 audsid=47833 user: 65/<none> O/S info: user: root, term: unknown, ospid: , machine: oxpaps16b.elsevier.co.uk program: JDBC Thin Client application name: JDBC Thin Client, hash value=0 Current SQL Statement: DELETE FROM ASSET_CONTENTS WHERE ASSOCIATEDASSETCONTENTUID = :B1 End of information on OTHER waiting sessions.=================================================== PROCESS STATE ------------- Process global information: process: 372be330, call: 37345dbc, xact: 37b38ce4, curses: 37340f44, usrses: 37340f44 ---------------------------------------- SO: 372be330, type: 2, owner: 0, flag: INIT/-/-/0x00 (process) Oracle pid=24, calls cur/top: 37345dbc/37345dbc, flag: (0) - int error: 0, call error: 0, sess error: 0, txn error 0 (post info) last post received: 0 0 21 last post received-location: ksqrcl last process to post me: 372bbe50 84 0 last post sent: 34537 0 16 last post sent-location: ksasnd last process posted by me: 372b9970 1 6 (latch info) wait_event=0 bits=10 holding 20006540 Parent+children enqueue hash chains level=4 Location from where latch is held: ksqcmi: kslgpl: Context saved from call: 0 state=busy recovery area: Dump of memory from 0x372B8A00 to 0x372B8A08 372B8A00 00000000 00000000 [........] Process Group: DEFAULT, pseudo proc: 372db58c O/S info: user: oracle, term: UNKNOWN, ospid: 5408 OSD pid info: Unix process pid: 5408, image: oracle@oxpora16 (TNS V1-V3)

You need to fix/modify the application code

[Nagesh]

Thx Vista.