Re: Deadlock inserting into same rowid (different block)

From: mohamed houri <mohamed.houri_at_gmail.com>
Date: Wed, 20 Mar 2013 16:20:32 +0100
Message-ID: <CAJu8R6gTu9BLL8jTeSPmJGfPOK31Rkn7efuXd3y6Mqx3LCCznA_at_mail.gmail.com>

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_at_jlcomp.demon.co.uk>

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

Received on Wed Mar 20 2013 - 16:20:32 CET