-
deadlock on the delete
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
Last edited by hannah00; 01-19-2005 at 11:22 PM.
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|