Re: wait event name for deferred constraints
Date: Sun, 2 Feb 2020 18:26:40 +0000
Message-ID: <LNXP265MB15626E7073F643E153F0CB21A5010_at_LNXP265MB1562.GBRP265.PROD.OUTLOOK.COM>
I don't think you'll see any special wait event relating to deferreable constraints and concurrent activity. I think the last time I took a (casual) look if you had two sessions which had set their constraints to deferred and then tried to execute SQL that produced collisions the waits that appeared where essentially the same as they would have been if the constraints had not been deferrable.
e.g. inserting the same PK value from TWO sessions at the same time when the value was already present as a committed row - the first session would be allowed to insert the row but the second would wait for the first on the (normal) "enq: TX - row lock contention". If you then try to commit the first transaction Oracle rolls back the first session with the duplicate key error and the second session succeeds in its attempt to insert the duplicate .... until it tries to commit and gets rolled back.
There may be combinations of actions and deferrability that could result in something different happening, but I would be a little surprised if there were. I didn't try and catalogue and test all the possible combinations, though.
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of McPeak, Matt (Consultant) <vxsmimmcp_at_subaru.com> Sent: 31 January 2020 18:30
To: ORACLE-L
Subject: wait event name for deferred constraints
If I have “FAST REFRESH ON COMMIT” materialized view, multiple sessions causing refreshes may experience an “enq: JI – contention” wait event.
I assume a similar serialization has to happen to enforce deferred constraints? If that is true, does anyone know what the wait event would be?
Thanks!
Matt
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Feb 02 2020 - 19:26:40 CET