RE: Can a deferred FK constraint cause "enq: TX - row lock contention" in Share (4) mode?
From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 4 Jul 2014 20:11:27 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901E0061D_at_exmbx05.thus.corp>
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 Received on Fri Jul 04 2014 - 22:11:27 CEST
Date: Fri, 4 Jul 2014 20:11:27 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901E0061D_at_exmbx05.thus.corp>
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 Received on Fri Jul 04 2014 - 22:11:27 CEST