Re: Deadlock inserting into same rowid (different block)

  • From: mohamed houri <mohamed.houri@xxxxxxxxx>
  • To: jpatterson@xxxxxxxxxx
  • Date: Wed, 20 Mar 2013 16:48:55 +0100

Joel,

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@xxxxxxxxxx>

> 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@xxxxxxxxxxxxxxxxxx]
> Sent: Wednesday, March 20, 2013 11:09 AM
> To: Patterson, Joel
> Cc: oracle-l@xxxxxxxxxxxxx
> 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@xxxxxxxxxx>
>
> |
> | 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)
> |
> |
> |
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>


-- 
Bien Respectueusement
Mohamed Houri

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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 03-2013