Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00060: Deadlock detected from 2 INSERT INTO 2 tables
ORA-00060: Deadlock detected from 2 INSERT INTO 2 tables [message #275384] Fri, 19 October 2007 13:08 Go to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
OK, I must me suffering from senioritis & I need some (fresh) ideas.
How can INSERT by two separate sessions into two separate tables generate an ORA-00060 exception?
 more /prime/oracle/product/admin/comdb04/udump/comdb04_ora_12291.trc
/prime/oracle/product/admin/comdb04/udump/comdb04_ora_12291.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /prime/oracle/product/10.2.0
System name:    Linux
Node name:      comdb04.example.com
Release:        2.6.9-42.0.3.ELsmp
Version:        #1 SMP Mon Sep 25 17:24:31 EDT 2006
Machine:        x86_64
Instance name: comdb04
Redo thread mounted by this instance: 1
Oracle process number: 30
Unix process pid: 12291, image: oraclecomdb04@comdb04.example.com

*** 2007-10-19 00:44:56.771
*** SERVICE NAME:(SYS$USERS) 2007-10-19 00:44:56.771
*** SESSION ID:(560.20958) 2007-10-19 00:44:56.771
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
INSERT INTO PRODUCT (PRODUCT_ID,NAME) VALUES (:1, :2)
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
TX-00050017-0006c085        30     560     X             50     499           S
TX-00030022-00065655        50     499     X             30     560           S
session 560: DID 0001-001E-0007384C     session 499: DID 0001-0032-0013D857
session 499: DID 0001-0032-0013D857     session 560: DID 0001-001E-0007384C
Rows waited on:
Session 499: obj - rowid = 00552241 - AAVSJBAC/AAIEhlAAA
  (dictionary objn - 5579329, file - 191, block - 2115685, slot - 0)
Session 560: obj - rowid = 005521D2 - AAVSHSAAHAAFfPVAAA
  (dictionary objn - 5579218, file - 7, block - 1438677, slot - 0)
Information on the OTHER waiting sessions:
Session 499:
  pid=50 serial=3774 audsid=8762316 user: 861/EVENT
  O/S info: user: , term: , ospid: 1234, machine: dil02.example.com
            program: 
  Current SQL Statement:
  INSERT INTO HOUR_CHECKOUT (HOUR,STORE_ID,SHIPPING,CHECKOUTS,ORDERS,CHECKOUT_QTY,CHECKOUT_PRICE,ORDER_QTY,ORDER_PRICE) VALUES (:1, :2
, :3, :4, :5, :6, :7, :8, :9)
End of information on OTHER waiting sessions.
===================================================

What is even more bizarre to my way of thinking is that OBJECT_ID=5579329 is a 3rd table; neither of the 2 getting the INSERTs.

Free clues would be most welcomed.

[Updated on: Fri, 19 October 2007 13:53] by Moderator

Report message to a moderator

Re: ORA-00060: Deadlock detected from 2 INSERT INTO 2 tables [message #275397 is a reply to message #275384] Fri, 19 October 2007 14:36 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I hope this link might help you.

http://www.orafaq.com/usenet/comp.databases.oracle.server/2005/06/02/0176.htm

Cheers

Re: ORA-00060: Deadlock detected from 2 INSERT INTO 2 tables [message #275398 is a reply to message #275384] Fri, 19 October 2007 15:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Check your foreign keys. Are they all indexed?
Is there any bitmap index?

Regards
Michel
Re: ORA-00060: Deadlock detected from 2 INSERT INTO 2 tables [message #275401 is a reply to message #275384] Fri, 19 October 2007 15:21 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
You've probably already checked the obvious, like triggers in additional to Michel's comment.
Re: ORA-00060: Deadlock detected from 2 INSERT INTO 2 tables [message #275402 is a reply to message #275384] Fri, 19 October 2007 15:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
S.Rajaram,
THANKS!

I had been on a fruitless search all morning.

I was able to reproduce the ORA-00060 by following the steps in this URL in your response to my post.

I'm still trying to understand exactly why it happens so I can make the necessary changes to stop the ORA-00060 from plaguing my Production servers.

At least now I know where to focus my efforts.

Thanks Again to all who have responded.
Re: ORA-00060: Deadlock detected from 2 INSERT INTO 2 tables [message #275406 is a reply to message #275384] Fri, 19 October 2007 15:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
"feature" documented in Note:392816.1; but there is NO fix mentioned! Bummer.
Re: ORA-00060: Deadlock detected from 2 INSERT INTO 2 tables [message #275423 is a reply to message #275406] Fri, 19 October 2007 23:35 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Bitmap indexes? INITTRANS too small?
Re: ORA-00060: Deadlock detected from 2 INSERT INTO 2 tables [message #275426 is a reply to message #275384] Sat, 20 October 2007 00:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I did not grasp how straight forward the sample really is until I had some quiet time to think over this situation.
Say there are two sessions, S1 & S2 and two table T1 & T2 while both tables have PK.
Then following step occur in order.
1) S1 update T1
2) S2 update T2
3) S1 update T2 & "hangs" because S2 has not COMMIT its update of T2.
4) S2 update T1 & "hangs" because S1 has not COMMIT its update of T1

At this point in time we have a classic example of a deadlock.
Neither session can proceed, so Oracle intervenes & throws an ORA-00060 exception.

It has nothing to do with any of the possible reasons for an ORA-00060. It is just a deadlock; pure & simple.

My challenge is to figure out in my production environment why there are two (different) sessions concurrently updating multiple different tables in the same schema. Either additional COMMITs (which might break transactional integrity) or enforcing only 1 session per schema for the DML.

In doing additional investigation I found a different ORA-00060 scenario involving DELETEs of multiple tables.

IMO, the ORA-00060 could occur with any combination of DML statements.

The other factor I am struggling to discern is this application
has been deployed for many years & I've just started seeing these ORA-00060 errors over the last 2 - 3 months but they seem to be occurring with slightly increasing frequency.

Once again I'd like to THANK S.Rajaram for giving me the clue I suspected existed, but I had not found on my own.
Part of my problem was I was not really sure what I was actually looking for.
So I was hoping somebody else had previously seen my symptoms & sure enough somebody had documented exactly what I was seeing.

I intend to update this thread after I eliminate the ORA-00060.
My reality is that I am losing some actual Production data because of these exceptions.
Needless to say I am expected to correct this situation sooner rather than later.


HAND!

[Updated on: Sat, 20 October 2007 00:10] by Moderator

Report message to a moderator

Re: ORA-00060: Deadlock detected from 2 INSERT INTO 2 tables [message #275442 is a reply to message #275426] Sat, 20 October 2007 02:24 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Could it be that there are new developers on the team? People that don't keep up the intended locking-order?
The described case (the simple case in your last post) i a typical example of a team not following a locking strategy. It should not be possible that session A locks tables in one order, while session B locks them in another order.

So, I think you should either check for those new developers, or for some new piece of code. Since you mention that you see it more and more lately, it could be the result of some code-refactoring as well..

But of course, this view is quite obvious...

[Updated on: Sat, 20 October 2007 02:25]

Report message to a moderator

Re: ORA-00060: Deadlock detected from 2 INSERT INTO 2 tables [message #275458 is a reply to message #275384] Sat, 20 October 2007 08:59 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I did not mention it but the ORA-00060 always occurs within a utility that is in new code or a totally re-written component.
I suspect that the off shored Java developers "got fancy" implemented a multi-threaded capability to achieve maximum throughput.
Things like this tend to keep my job interesting.
Previous Topic: About Ref Cursor
Next Topic: Query Help
Goto Forum:
  


Current Time: Fri Feb 14 16:37:32 CST 2025