Re: wait event name for deferred constraints
Date: Mon, 3 Feb 2020 13:23:41 +0000
Message-ID: <BN7PR04MB5266C6A1B5EFD092770B5A1FDD000_at_BN7PR04MB5266.namprd04.prod.outlook.com>
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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> Sent: Sunday, February 2, 2020 1:26 PM
To: ORACLE-L <oracle-l_at_freelists.org> 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_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
--
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
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Feb 03 2020 - 14:23:41 CET