From: Jonathan Lewis <jonathan@jlcomp.demon.co.uk>
To: ORACLE-L <oracle-l@freelists.org>
Subject: Re: wait event name for deferred constraints
Thread-Topic: wait event name for deferred constraints
Date: Sun, 2 Feb 2020 18:26:40 +0000
Message-ID: 
 <LNXP265MB15626E7073F643E153F0CB21A5010@LNXP265MB1562.GBRP265.PROD.OUTLOOK.COM>
References: 
 <BN8PR04MB5569E846C3D81A7CDAC45ED0DD070@BN8PR04MB5569.namprd04.prod.outlook.com>
In-Reply-To: 
 <BN8PR04MB5569E846C3D81A7CDAC45ED0DD070@BN8PR04MB5569.namprd04.prod.outlook.com>
Accept-Language: en-GB, en-US
Content-Language: en-GB
received-spf: None (protection.outlook.com: jlcomp.demon.co.uk does not
 designate permitted sender hosts)
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-MS-Exchange-CrossTenant-Network-Message-Id: bf9848c7-7dbd-4ba5-6d8f-08d7a80d727c
X-MS-Exchange-CrossTenant-originalarrivaltime: 02 Feb 2020 18:26:40.4373
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: c5ec3dea-08bd-405e-942c-f61dff9a4831
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: 6g5GoR5gpOn/QBVZjnaF7efv0c6Fc6TuTF5agpO0IH9mKKVT4GP6uz1vT2OrJmCdA469GPA1OulOWfSHIWoVXtW5KtgQElhrebT8dnl/R8g=
X-MS-Exchange-Transport-CrossTenantHeadersStamped: LNXP265MB0107
X-archive-position: 76135
X-ecartis-version: Ecartis v1.0.0
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
X-original-sender: jonathan@jlcomp.demon.co.uk
Precedence: normal
Reply-To: jonathan@jlcomp.demon.co.uk
List-Help: <mailto:ecartis@freelists.org?Subject=help>
List-Unsubscribe: <mailto:oracle-l-request@freelists.org?Subject=unsubscribe>
List-software: Ecartis version 1.0.0
List-Id: oracle-l <oracle-l.freelists.org>
X-List-ID: oracle-l <oracle-l.freelists.org>
List-Subscribe: <mailto:oracle-l-request@freelists.org?Subject=subscribe>
List-Owner: <mailto:>
List-post: <mailto:oracle-l@freelists.org>
List-Archive: <http://www.freelists.org/archives/oracle-l>
X-list: oracle-l

I don't think you'll see any special wait event relating to deferreable con=
straints and concurrent activity. I think the last time I took a (casual) l=
ook 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 th=
e value was already present as a committed row  - the first session would b=
e 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 fi=
rst transaction Oracle rolls back the first session with the duplicate key =
error and the second session succeeds in its attempt to insert the duplicat=
e .... 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, t=
hough.

Regards
Jonathan Lewis


________________________________________
From: oracle-l-bounce@freelists.org <oracle-l-bounce@freelists.org> on beha=
lf of McPeak, Matt (Consultant) <vxsmimmcp@subaru.com>
Sent: 31 January 2020 18:30
To: ORACLE-L
Subject: wait event name for deferred constraints

If I have =93FAST REFRESH ON COMMIT=94 materialized view, multiple sessions=
 causing refreshes may experience an =93enq: JI =96 contention=94 wait even=
t.

I assume a similar serialization has to happen to enforce deferred constrai=
nts?  If that is true, does anyone know what the wait event would be?

Thanks!
Matt

--
http://www.freelists.org/webpage/oracle-l