Parallel or Direct Path INSERT blocks Conventional INSERT

 Note : This test is in a 19c database

A session executing Parallel (or Direct Path, with APPEND) INSERT will block conventional insert from another session.

(a bit of explanation : A Parallel INSERT is always a Direct Path INSERT, without requiring the APPEND Hint.  But a non-Parallel INSERT with an APPEND Hint is also a Direct Path INSERT)

23:02:31 SQL> l
  1   insert /*+ PARALLEL (t 2) */ into target_objects_list t
  2* select /*+ PARALLEL (s 2) */ * from objects_list s
23:02:31 SQL> /

289641 rows created.

23:02:39 SQL>

Session 1 ran a Parallel INSERT from 23:02:31 to 23:02:39.  What happens to another session attempting Conventional Insert from a different session -- even if the second INSERT begins after the Parallel INSERT has completed (but not COMMITed) ?

23:02:36 SQL> insert into target_objects_list
23:02:50   2  select * from dba_objects
23:02:55   3  where rownum lessthan 2  -- changed the "lessthansign" to "lessthan" to preserve formatting in HTML
23:02:58   4  /

A Blocker-Waiter Tree shows :

WAITING_SESSION
------------------------------------------------------------------------------------------------------------------------------------
LOCK_TYPE         MODE_REQUE MODE_HELD  LOCK_ID1   LOCK_ID2
----------------- ---------- ---------- ---------- ----------
17
None

 w*60
DML               Row-X (SX) Exclusive  78449      0


2 rows selected.

SQL> select sql_text from v$sql where sql_id =
  2  (select sql_id from v$session
  3   where sid=60)
  4  /

SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
insert into target_objects_list select * from dba_objects where rownum lessthan 2  -- changed the "lessthansign" to "lessthan" to preserve formatting in HTML

1 row selected.

SQL>

As soon as I issue a COMMIT in the first session :

23:11:10 SQL>
23:11:11 SQL> select count(*) from target_objects_list
23:11:20   2  /
select count(*) from target_objects_list
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


23:11:23 SQL> commit;

Commit complete.

23:11:28 SQL>

The waiting session with a single-row Conventional INSERT succeeds :

23:02:36 SQL> insert into target_objects_list
23:02:50   2  select * from dba_objects
23:02:55   3  where rownum lessthan 2  -- changed the "lessthansign" to "lessthan" to preserve formatting in HTML
23:02:58   4  /

1 row created.

23:11:27 SQL>

So, be careful when attempting Parallel or Direct Path (APPEND) INSERTs into a table where other sessions may be attempting Conventional INSERTs.