deadlock on the delete

[hannah00]

HI all,

I have a deadlock issues on one of my application, below is the error:

DEADLOCK DETECTED
Current SQL statement for this session:
DELETE FROM PF_TRAN WHERE ID = 1964 AND CODE = 'C'
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-000047e0-00000000 22 28 SX 15 25 SX SSX
TM-00004739-00000000 15 25 SX 22 28 SX SSX
session 28: DID 0001-0016-00000004 session 25: DID 0001-000F-00000006
session 25: DID 0001-000F-00000006 session 28: DID 0001-0016-00000004
Rows waited on:
Session 25: obj - rowid = 000047E5 - AAAEflAADAAAAAAAAA
(dictionary objn - 18405, file - 3, block - 0, slot - 0)
Session 28: obj - rowid = 00004739 - AAAEc5AADAAAAAAAAA
(dictionary objn - 18233, file - 3, block - 0, slot - 0)
Information on the OTHER waiting sessions:
Session 25:
pid=15 serial=957 audsid=12599 user: 34/QRMTIPS
O/S info: user: ISO\USERS, term: PROD01, ospid: 3688:3484, machine: ISO\PROD01
program: PB.EXE
application name: PB.EXE, hash value=0
Current SQL Statement:
DELETE FROM PF_SUMWHERE ID = 1965
End of information on OTHER waiting sessions.
===================================================
PROCESS STATE
-------------
Process global information:
process: 67EAD07C, call: 67F61B9C, xact: 688755A4, curses: 67EEB144, usrses: 67EEB144
----------------------------------------
SO: 67EAD07C, type: 2, owner: 00000000, flag: INIT/-/-/0x00
(process) Oracle pid=22, calls cur/top: 67F61B9C/67F61B9C, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 4
last post received-location: kslpsr
last process to post me: 67ea8cfc 1 6
last post sent: 0 0 16
last post sent-location: ksasnd
last process posted by me: 67ea893c 1 6
(latch info) wait_event=0 bits=10
holding 213f1e0 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 0x67EA7D3C to 0x67EA7D44



here is couple question;

1. deadlock happened on prod and it's application related. I am trying to find out what happened to the appl, knowing that I don't have issues with unindexes on FK (since I already ran the query).
2. what I am not sure is why deadlock is happened on the above delete, I have talked to couple appl guys and the two things I suspect: a. session A ran and got disconnected and connection got corrupted, so the session never get commit and it should rollback everything so why do we have problem when session B kid off the process again???
3. Lastly, could the statistics of the above tables/indexes is out of whack since the table is frequently delete/insert data. so the delete statement take forever which would have the lock and another processs is runnning

thanks all

[jmodic]

And why are you so sure you are not hit by the most clasical deadlock example, without any session being killed/disconnected/corrupted, ie like this:

Time T1: session A deletes record R1 and does not commit

Time T2: session B deletes record R2 and does not commit

Time T3: session A tries to delete record R2 (which is still there as far as his session is concerned), but this record is locked by session B, so he has to wait

Time T4: session B tries to delete record R1 (which is still there as far as his session is concerned), but as this record is locked by session A he has to wait. And wait. And wait. And wait.

This is clasical example of deadlock and is not limited on UPDATEs only, it's the same with DELETEs.

[abhaysk]

Originally posted by jmodic
This is clasical example of deadlock and is not limited on UPDATEs only, it's the same with DELETEs.

With inserts as well..

(ofcourse if unique key/pk enabled)


Abhay.