RE: Deadlock inserting into same rowid (different block)
- From: "Patterson, Joel" <jpatterson@xxxxxxxxxx>
- To: "jonathan@xxxxxxxxxxxxxxxxxx" <jonathan@xxxxxxxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 21 Mar 2013 10:38:14 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Jonathan Lewis
Sent: Wednesday, March 20, 2013 2:45 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxx<mailto:jpatterson@xxxxxxxxxx>>
To: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx<mailto:Mark.Bobak@xxxxxxxxxxxx>>;
"mohamed houri"
<mohamed.houri@xxxxxxxxx<mailto:mohamed.houri@xxxxxxxxx>>
Cc: "Jonathan Lewis"
<jonathan@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>>;
<oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
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...
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Deadlock inserting into same rowid (different block)- Patterson, Joel
- » Re: Deadlock inserting into same rowid (different block)- Jonathan Lewis
- » Re: Deadlock inserting into same rowid (different block)- mohamed houri
- » RE: Deadlock inserting into same rowid (different block)- Patterson, Joel
- » RE: Deadlock inserting into same rowid (different block)- Patterson, Joel
- » Re: Deadlock inserting into same rowid (different block)- mohamed houri
- » RE: Deadlock inserting into same rowid (different block)- Patterson, Joel
- » RE: Deadlock inserting into same rowid (different block)- Bobak, Mark
- » RE: Deadlock inserting into same rowid (different block)- Patterson, Joel
- » Re: Deadlock inserting into same rowid (different block)- Jonathan Lewis
- » RE: Deadlock inserting into same rowid (different block) - Patterson, Joel
- » RE: Deadlock inserting into same rowid (different block)- Patterson, Joel
- » Re: Deadlock inserting into same rowid (different block)- D'Hooge Freek
- » RE: Deadlock inserting into same rowid (different block)- Patterson, Joel
- » RE: Deadlock inserting into same rowid (different block)- Patterson, Joel
