I guess that is because deadlocks are possible with multiple sessions and a
"FAST REFRESH ON COMMIT" materialized view (requiring special serialization),
but deadlocks are not possible with multiple sessions and deferred constraints?
That seems to make sense to me. I'll have to think about it some more. I'll
read the other link you sent. Thank you!
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
Sent: Sunday, February 2, 2020 1:26 PM
To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
Subject: Re: wait event name for deferred constraints
CAUTION: This email has originated from outside of SOA. Do not click on links
or open attachments unless you recognize the sender and know the content is
safe.
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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of McPeak, Matt (Consultant) <vxsmimmcp@xxxxxxxxxx>
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
--
https://nam03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l&data=02%7C01%7Cvxsmimmcp%40subaru.com%7Cb66b7d99c85c491bc1cb08d7a80d953c%7C80f0f13322e9459c97469507eb1e8595%7C0%7C0%7C637162648619971511&sdata=GJg4gZWq0Mcyc4Q9a9FY%2BacItTH8jT2zWE%2Bw0dylY2M%3D&reserved=0
