Re: Can a deferred FK constraint cause "enq: TX - row lock contention" in Share (4) mode?
- From: Rich <richa03@xxxxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx
- Date: Sat, 5 Jul 2014 20:11:05 -0700
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@xxxxxxxxxxxxxxxxxx>
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
> @jloracle
>
> ________________________________________
> From: Jonathan Lewis
> Sent: 04 July 2014 21:11
> To: oracle-l@xxxxxxxxxxxxx
> 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
> @jloracle
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
