RE: Deadlock inserting into same rowid (different block)
Date: Thu, 21 Mar 2013 11:19:42 -0400
Message-ID: <C1117B1AA0340645894671E09A7891F714F8833AE9_at_EIHQEXVM2.ei.local>
This was enough to dig into the application further. We know that last night's process had to be one of a few - since it is acceptance and no 'user' was logged in and working. We hypothesize that there are two application layer 'nodes' executing inserts into the child table - at the same time. The Cache on the sequence used for the primary key is set to 500 - clue.
However, as to the suspect reporting of the deadlock graph - it appears that Jonathan was spot on, (I have a little English in me).
The column used by the child table sequence is the primary key for the child table, and is not related to the FK index mentioned in the Deadlock graph, object 199909, which references the parent table on a totally different column.
So we theorize that the primary key overlap is actually on the child table - and had nothing to do with the FK index to the parent table as reported in the deadlock graph; 'mystery'.
Still looking into getting the values for the bind variable in the trace file. I haven't done an insert in so long I have opened an SR - this may confirm the theory.
Joel Patterson
Database Administrator
904 928-2790
From: Patterson, Joel
Sent: Thursday, March 21, 2013 10:38 AM
To: 'jonathan_at_jlcomp.demon.co.uk'; oracle-l_at_freelists.org
Subject: RE: Deadlock inserting into same rowid (different block)
Yes the parent table PK is single column uses a sequence. This is the column referenced by the FK constraint, (or the FK index, objn 199909).
The exact same thing happened last night with two other sessions. The application is using JDBC Thin Client
Rows waited on:
Session 663: obj - rowed = 00030C35 - AAAwz1AAGAAAwqgAAA
(dictionary objn - 199909, file - 6, block - 199328, slot -0)
Session 663: obj - rowed = 00030C35 - AAAwz1AAGAAAwqgAAA
(dictionary objn - 199909, file - 6, block - 319359, slot -0)
As of now: v$segstat: db block changes is the highest value - here is the two objects (parent table followed by FK index).
PARENT TABLE
OBJ# DATAOBJ# STATISTIC_NAME STATISTIC# VALUE
------- ---------- ---------------------------------------------------------------- ---------- ----------------
187490 187490 logical reads 0 112 187490 187490 buffer busy waits 1 0 187490 187490 gc buffer busy 2 0 187490 187490 db block changes 3 16000 187490 187490 physical reads 4 70 187490 187490 physical writes 5 187 187490 187490 physical read requests 6 53 187490 187490 physical write requests 7 29 187490 187490 physical reads direct 8 0 187490 187490 physical writes direct 9 0 187490 187490 optimized physical reads 11 0 187490 187490 gc cr blocks received 12 0 187490 187490 gc current blocks received 13 0 187490 187490 ITL waits 14 0 187490 187490 row lock waits 15 0 187490 187490 space used 17 0 187490 187490 space allocated 18 0 187490 187490 segment scans 20 0
FOREIGN KEY OBJECT
OBJ# DATAOBJ# STATISTIC_NAME STATISTIC# VALUE
------- ---------- ---------------------------------------------------------------- ---------- ----------------
199909 199909 logical reads 0 4224 199909 199909 buffer busy waits 1 0 199909 199909 gc buffer busy 2 0 199909 199909 db block changes 3 115120 199909 199909 physical reads 4 3502 199909 199909 physical writes 5 5200 199909 199909 physical read requests 6 3502 199909 199909 physical write requests 7 2398 199909 199909 physical reads direct 8 0 199909 199909 physical writes direct 9 0 199909 199909 optimized physical reads 11 0 199909 199909 gc cr blocks received 12 0 199909 199909 gc current blocks received 13 0 199909 199909 ITL waits 14 0 199909 199909 row lock waits 15 4 199909 199909 space used 17 2000694 199909 199909 space allocated 18 3145728 199909 199909 segment scans 20 0
Joel Patterson
Database Administrator
904 928-2790
--
Joel Patterson
Sr. Database Administrator | Enterprise Integration
Phone: 904-928-2790 | Fax: 904-733-4916
www.entint.com<http://www.entint.com/>
[http://i1202.photobucket.com/albums/bb367/Entint/signaturev61.jpg]<http://www.entint.com/>
[http://i1202.photobucket.com/albums/bb367/Entint/th_FaceBook1.jpg]<http://www.facebook.com/pages/Enterprise-Integration/212351215444231> [http://i1202.photobucket.com/albums/bb367/Entint/th_Twitter1.jpg] <http://twitter.com/#!/entint> [http://i1202.photobucket.com/albums/bb367/Entint/th_LinkedIn1.jpg] <http://www.linkedin.com/company/18276?trk=tyah> [http://i1202.photobucket.com/albums/bb367/Entint/th_YouTube1.jpg] <http://www.youtube.com/user/ValueofIT>
This message (and any associated files) is intended only for the use of the addressee and may contain information that is confidential, subject to copyright or constitutes a trade secret. If you are not the intended recipient, you are hereby notified that any dissemination, copying or distribution of this message, or files associated with this message, is strictly prohibited. If you have received this message in error, please notify us immediately by replying to the message and deleting it from your computer. Messages sent to and from us may be monitored. Any views or opinions presented are solely those of the author and do not necessarily represent those of the company. [v.1.1]
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Wednesday, March 20, 2013 2:45 PM
To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>
Subject: Re: Deadlock inserting into same rowid (different block)
The "graph" isn't pointing to any object.
The rowids waited on reference an object - which I think you're saying is the index supporting the foreign key - but I've said the unlikely occurrence of the zeros as both slots numbers is, by itself, sufficient to make you suspicious that the reporting is false. (The fact that the object is an index, even more so).
I'd hazard a guess that you'll find that that index is a popular candidate for "buffer busy wait" waits (check v$segstat) and that the inserts are
(roughly) sequential on the parent id.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
- Original Message -----
From: "Patterson, Joel" <jpatterson_at_entint.com<mailto:jpatterson_at_entint.com>>
To: "Bobak, Mark" <Mark.Bobak_at_proquest.com<mailto:Mark.Bobak_at_proquest.com>>; "mohamed houri"
<mohamed.houri_at_gmail.com<mailto:mohamed.houri_at_gmail.com>>
Cc: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>>; <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>>
Sent: Wednesday, March 20, 2013 6:05 PM
Subject: RE: Deadlock inserting into same rowid (different block)
Ok, I get the deadlock scenario. What has thrown me off is that the graph
is pointing to the object FK Index... as opposed to the PK index or parent
row. Since there can be several child entries for the same parent.
But what to say now as a suggestion. Therefore just say to stand by and see if it becomes a recurring theme?
Can it be an anomaly seeing how there was a suggestion "... Given that the slot numbers are both zero, it's likely that they're being reported incorrectly... "
It is the same app for both sessions so it is inserting the tables in the same order...
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 21 2013 - 16:19:42 CET