Home » RDBMS Server » Server Administration » Application DEADLOCKS (RH ES4 Oracle V10.2.01)
Application DEADLOCKS [message #312799] Wed, 09 April 2008 15:40 Go to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
The application is throwing ORA-00060 & I need to assist with resolving this issue.

The application folks swear that there is "NO WAY" their application will have two different sessions trying to delete the same row.

If/how can I extract the value of the bind variables involved with the 2 DELETE statements below?


*** 2008-04-09 10:55:44.298
*** SERVICE NAME:(SYS$USERS) 2008-04-09 10:55:44.298
*** SESSION ID:(439.31952) 2008-04-09 10:55:44.298
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
DELETE FROM tblStatusProfile WHERE StatusProfileID=:1
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-0000c9c5-00000000 196 439 SX SSX 66 313 SX SSX
TM-0000c9c5-00000000 66 313 SX SSX 196 439 SX SSX
session 439: DID 0001-00C4-00001E29 session 313: DID 0001-0042-0000000A
session 313: DID 0001-0042-0000000A session 439: DID 0001-00C4-00001E29
Rows waited on:
Session 313: no row
Session 439: no row
Information on the OTHER waiting sessions:
Session 313:
pid=66 serial=17591 audsid=1171907 user: 55/INLOGIC
O/S info: user: , term: , ospid: 1234, machine: gs12dn
program:
Current SQL Statement:
DELETE FROM tblStatusProfile WHERE StatusProfileID=:1
End of information on OTHER waiting sessions.
===================================================


Any other ideas or suggestions on how to better understand what is or is not happening would be greatly appreciated.

TIA!
Re: Application DEADLOCKS [message #312802 is a reply to message #312799] Wed, 09 April 2008 16:13 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Do they have any autonomous transactions taking place ?
Re: Application DEADLOCKS [message #312803 is a reply to message #312799] Wed, 09 April 2008 16:23 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
The claim is no autonomous transactions.
I am still trying to get access to the source code.

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=873 siz=24 off=0
  kxsbbbfp=2a97209378  bln=22  avl=05  flg=05
  value=6390994


If I knew what I am looking at, I'd be dangerous.
The number to the right of "value=" seems to be what I was looking for; but I need to do more sanity testing.
Re: Application DEADLOCKS [message #312808 is a reply to message #312803] Wed, 09 April 2008 16:38 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
It appears that the bind value is "value=6390994".

Are they updating/deleting from a parent table? Any un-indexed foreign keys?
Re: Application DEADLOCKS [message #312810 is a reply to message #312799] Wed, 09 April 2008 17:18 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
ebrian,
Not only do I owe you a beer, I owe you a whole CASE!

YES, a missing index on a FK was causing more problems than just DEADLOCKS.

The index will be added this Saturday when we are doing a production release.

THANKS Again!
Re: Application DEADLOCKS [message #312811 is a reply to message #312810] Wed, 09 April 2008 17:42 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Great...always wanted to visit SoCal Smile

Just glad it was a simple index. Deadlocks can be a pain to track down sometimes.
Previous Topic: Insert duration
Next Topic: Regarding accidently shutdown or accidently window close
Goto Forum:
  


Current Time: Mon Dec 05 21:24:20 CST 2016

Total time taken to generate the page: 0.08831 seconds