Re: Deadlock inserting into same rowid (different block)

In case of deadlock occurring on Transaction (TX) enqueue held on mode X and waited on mode S I can think of 3 possibilities

1. 1) Presence of Bitmap Indexes à That is not your case
2. 2) ITL slot contention à That is not your case because statements are insert and your table is not an IOT (I believe)
3. 3) Primary key or unique Key overlapping à that’s your case

And yes developers should in this case review their code to not allow unique/primary key overlapping

Best regards

Mohamed Houri

2013/3/20 Patterson, Joel <jpatterson_at_entint.com>

> I probably shouldn't ask, but how does one session know that another
> session has uncommitted values about to be committed that would conflict
> with what itself?
>
> But aside from that, I wish to report back to the developers that are
> asking about this that --... essentially it is an application issue... and
> provide some suggestions such as the order of tables and/or rows... but
> more specifically in this case, from what I am reading, it is counter
> productive to report back that the trace file is incorrect... Does it imply
> that this could be an anomaly and just wait and see if it is a reoccurring
> issue? This is probably new code; the errors are in a UAT DB.
>
>
> Joel Patterson
> Database Administrator
> 904 928-2790
>
>
>
> --
> Joel Patterson
> Sr. Database Administrator | Enterprise Integration
> Phone: 904-928-2790 | Fax: 904-733-4916
> http://www.entint.com/
>
> http://www.entint.com/
>
> http://www.facebook.com/pages/Enterprise-Integration/212351215444231
> http://twitter.com/#!/entint
> http://www.linkedin.com/company/18276?trk=tyah
> 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: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk]
> Sent: Wednesday, March 20, 2013 11:09 AM
> To: Patterson, Joel
> Cc: oracle-l_at_freelists.org
> Subject: Re: Deadlock inserting into same rowid (different block)
>
>
> The deadlock pattern is indicative of collisions on a unique constraint.
> They're both trying to insert primary / unique key values already inserted
> but not yet committed by the other session.
>
> Given that the slot numbers are both zero, it's likely that they're being
> reported incorrectly - Oracle doesn't clean out the wait information for
> every single wait, so these could be the file and block (with zero row) for
> early buffer busy waits, or read by other session or some such.
>
> 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>
>
> |
> | Deadlock graph:
> | ---------Blocker(s)--------
> ---------Waiter(s)---------
> | Resource Name process session holds waits process session holds
> waits
> | TX-001a0015-00014787 34 90 X 32 3
> S
> | TX-00190008-0000601b 32 3 X 34 90
> S
> |
> | session 90: DID 0001-0022-00000327 session 3: DID 0001-0020-000009E9
> | session 3: DID 0001-0020-000009E9 session 90: DID
> 0001-0022-00000327
> |
> | Rows waited on:
> | Session 90: obj - rowid = 00030CE5 - AAAwzlAAGAABDiuAAA (dictionary
> | objn - 199909, file - 6, block - 276654, slot - 0) Session 3: obj -
> | rowid = 00030CE5 - AAAwzlAAGAABp8gAAA (dictionary objn - 199909, file
> | - 6, block - 433952, slot - 0)
> |
> |
> |
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Bien Respectueusement
Mohamed Houri

--
http://www.freelists.org/webpage/oracle-l