It's undesirable, but does make sense.
It's because when you modify data in a child (FK) table the parent table is
locked in mode 3 (share row exclusive). This effect was introduced in 11.1,
it used to be mode 2 (which was introduced in 9.2 to handle a deadlocking
problem with parallel DML, if I recall correctly).
The trouble is, if you're holding a mode 3 lock that means you MIGHT have
an active update or delete, which means I can't valildate a constraint in
case when you commit your transaction it makes my validation
retrospectively incorrect so the DDL is immediately cancelled.
You could make the session resumable, or set ddl_lock_timeout if this is a
batch like job that "knows" it may have to wait for a little while
sometimes.
Regards
Jonathan Lewis
On Tue, 11 Oct 2022 at 08:57, Pap <oracle.developer35@xxxxxxxxx> wrote:
The last post got bounced back. So reposting...
Never thought of this scenario. But if a reference data table or parent
table is referenced by two child transaction tables. And if the table-1 is
doing conventional Insert , it will not let other child table-2 to disable
and enable it's foreign key constraints until the commit happens. Still
wanted to understand if it's legitimate lock or not? This happens even we
have indexes created on the foreign keys.
Below is the details of the test case to reproduce the error.
https://gist.github.com/oracle9999/f1761cdef4850522b1dd276fa4a79bfe
