19 May, 2021

Does a Conventional INSERT block Parallel or Direct Path INSERTs ?


Note : This test is in a 19c database

 In my previous post, I have demonstrated how a Parallel or Direct Path INSERT blocks a Conventional INSERT.

Does a Conventional INSERT block a Parallel or Direct Path INSERT ?  OR is there a different effect on the session attempting the Parallel or Direct Path ?

Taking the same target table,  this is the first session, with a Conventional INSERT :



22:30:18 SQL> insert into target_objects_list
22:30:20   2  select * from dba_objects
22:30:32   3  where rownum lessthan 2  -- changed the "lessthansign" to "lessthan" to preserve formatting in HTML 
22:30:43   4  /

1 row created.

22:30:44 SQL>


With the Conventional INSERT not having issued a COMMIT yet, the second session attempts a Parallel INSERT

22:32:14 SQL> alter session enable parallel dml;

Session altered.

22:32:20 SQL> insert /*+ PARALLEL (t 2) */ into target_objects_list t
22:32:30   2  select /*+ PARALLEL (s 2) */ * from objects_list s
22:32:37   3  /



With the second session waiting, let's check what the wait is

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

 w*82
DML               Exclusive  Row-X (SX) 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=82)
  4  /

SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
insert /*+ PARALLEL (t 2) */ into target_objects_list t select /*+ PARALLEL (s 2) */ * from objects_list s

1 row selected.

SQL>

Note how the MODE_REQUESTED and MODE_HELD are different from the previous blog post.

What happens when the Conventional INSERT issues a COMMIT ?

22:30:18 SQL> insert into target_objects_list
22:30:20   2  select * from dba_objects
22:30:32   3  where rownum lessthan 2  -- changed the "lessthansign" to "lessthan" to preserve formatting in HTML 
22:30:43   4  /

1 row created.

22:30:44 SQL>
22:35:54 SQL>
22:35:55 SQL> commit;

Commit complete.

22:35:56 SQL>


And here is the second session now :

22:32:20 SQL> insert /*+ PARALLEL (t 2) */ into target_objects_list t
22:32:30   2  select /*+ PARALLEL (s 2) */ * from objects_list s
22:32:37   3  /

289641 rows created.

22:36:00 SQL>
22:36:50 SQL> select count(*) from target_objects_list;
select count(*) from target_objects_list
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


22:36:58 SQL> commit;

Commit complete.

22:37:13 SQL> select count(*) from target_objects_list;

  COUNT(*)
----------
    289642

22:37:20 SQL>


The Parallel INSERT succeeded after the COMMIT by the Conventional INSERT.

If you compare the MODE_REQUESTED and MODE_HELD (from dba_locks) in the two blog posts, the Parallel, Direct Path INSERT requests an Exclusive Lock.

FYI, LOCK_ID1=78449 is :
SQL> l
  1  select object_id,  object_name
  2  from dba_objects
  3* where object_id = 78449
SQL> /

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     78449 TARGET_OBJECTS_LIST

1 row selected.

SQL>


Here's an experiment you can try :
What if the target is not a table but a Partition of a Table ?
What if the target is an explicitly named Partition ?


No comments: