ORA-00060: Deadlock detected from 2 INSERT INTO 2 tables [message #275384] |
Fri, 19 October 2007 13:08  |
 |
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 #275426 is a reply to message #275384] |
Sat, 20 October 2007 00:05   |
 |
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   |
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  |
 |
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.
|
|
|