OraFAQ Forum: Server Administration » Application DEADLOCKS

Home » RDBMS Server » Server Administration » Application DEADLOCKS (RH ES4 Oracle V10.2.01)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Application DEADLOCKS [message #312799]

Wed, 09 April 2008 15:40

BlackSwan
Messages: 26766
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!

Report message to a moderator

Re: Application DEADLOCKS [message #312802 is a reply to message #312799]

Wed, 09 April 2008 16:13

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Do they have any autonomous transactions taking place ?

Report message to a moderator

Re: Application DEADLOCKS [message #312803 is a reply to message #312799]

Wed, 09 April 2008 16:23

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Application DEADLOCKS [message #312808 is a reply to message #312803]

Wed, 09 April 2008 16:38

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?

Report message to a moderator

Re: Application DEADLOCKS [message #312810 is a reply to message #312799]

Wed, 09 April 2008 17:18

BlackSwan
Messages: 26766
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!

Report message to a moderator

Re: Application DEADLOCKS [message #312811 is a reply to message #312810]

Wed, 09 April 2008 17:42

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.

Report message to a moderator

Previous Topic:	Insert duration
Next Topic:	Regarding accidently shutdown or accidently window close

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 02 04:29:08 CST 2024