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

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