Re: How to debug resource busy error

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_at_gmail.com> 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
>
>
>

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