Re: Can a deferred FK constraint cause "enq: TX - row lock contention" in Share (4) mode?

From: Rich <richa03_at_gmail.com>
Date: Sat, 5 Jul 2014 20:11:05 -0700
Message-ID: <CALgGkeDtqtVphSNCs0eybfUdT4SxAdHY4QxRhWdZ2CgbgMwNMw_at_mail.gmail.com>

I wouldn't necessarily expect the enqueue for a deferred constraint - the enqueue infers to me that constraint checking is being done. I wonder if that's the intent?
BTW, I'd expect that the constraint would be checked [each transaction] after it was set to "not deferrable".
Sounds like a perfect MOS/SR question...

On Sat, Jul 5, 2014 at 7:17 PM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

>
> Generally, by the way, TX/4 row lock waits can be produced from any type
> of RI conflict relating to different sessions colliding at opposite ends of
> the constraint - it doesn't require deferrable constraints.
>
> e.g: Session 1 inserts new parent, session 2 inserts child for that parent.
> Session 2 has to wait for session 1 to commit or rollback.
>
> e.g.2: Session 1 deletes parent, session 2 inserts child for that parent
> session 2 has to wait for session 1 to commit or rollback
>
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> _at_jloracle
>
> ________________________________________
> From: Jonathan Lewis
> Sent: 04 July 2014 21:11
> To: oracle-l_at_freelists.org
> Subject: RE: Can a deferred FK constraint cause "enq: TX - row lock
> contention" in Share (4) mode?
>
> One scenario I've created which matches one set of your symptoms:
>
> Foreign key is deferrable initially deferred.
> Session 1 inserts a new parent key value - without commiting.
> Session 2 updates a child row changing a value that exists in the parent
> table to the new, uncommitted, parent value.
>
> Session 2 goes into TX mode 4 waiting for session 1 to commit; showing -1
> as the current_obj#
>
>
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> _at_jloracle
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Sun Jul 06 2014 - 05:11:05 CEST