Re: Deadlock inserting into same rowid (different block)

  • From: mohamed houri <mohamed.houri@xxxxxxxxx>
  • To: jonathan@xxxxxxxxxxxxxxxxxx
  • Date: Wed, 20 Mar 2013 16:20:32 +0100

In addition to what Jonathan has already clarified, I want to add that for
a deadlock to be eventually due to a missing index on FK the deadlock graph
should at *least* show *one TM* enqueue(DML enqueue). In your case both
enqueues are of Transaction(TX) enqueue type. So your deadlock has a big
chance to not being related to an unindexed FK
Best regards
Mohamed Houri
www.hourim.wordpress.com


2013/3/20 Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>

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