Mauro Pagano's Blog

Concurrent INSERT APPEND into the same table

5 Comments

The main reason I decided to have a blog was to share all the small things I learn every day while experimenting but I’ve to admit I fell short on my initial intention, hopefully I’ll do better in the coming months, starting with this super-quick post.

I always knew that concurrent (aka multiple sessions at the same time) INSERT /*+ APPEND */ into the same target table was not possible even if the target table was partitioned. Here is an example of a SQL

create table test_target (n1 number, n2 number)
partition by range(n1)
(partition p1 values less than (5),
 partition p2 values less than (10));

insert /*+ append */ into test_target select mod(rownum,5), rownum from dual connect by rownum <= 10;

Reason for the concurrency being not feasible is the first session acquires an enqueue TM in X mode on the whole table (so every other session will have to wait) even if the inserted data only goes into a specific partition.
In the following extract SID 306 (my session) is holding the enq TM in X mode on object id 138542, which is the table even though only partition P1 has been populated.

SQL> select * from v$lock where sid = 306;

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK     CON_ID
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ----------
       306 TX     131102      11102          6          0         29          0          0
       306 TM     138542          0          6          0         29          0          3
       306 AE        133          0          4          0         86          0          3

SQL> select object_id, object_name, object_type from user_objects where object_id = 138542;

 OBJECT_ID OBJECT_NAME     OBJECT_TYPE
---------- --------------- -----------------------
    138542 TEST_TARGET     TABLE

If in another session (SID 186) I try to run an INSERT /*+ APPEND */ to populated only P2 then the execution will sit and wait for SID 306 to release the enqueue on object id 138542, as shown in the first row from V$LOCK where 186 wants to grab an enq TM in X mode for object id 138542

SQL> insert /*+ append */ into test_target select mod(rownum,5)+5, rownum from dual connect by rownum  select * from v$lock where sid in (306,186) order by sid;

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK     CON_ID
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ----------
       186 TM     138542          0          0          6         94          0          3
       186 AE        133          0          4          0        104          0          3
       306 AE        133          0          4          0        640          0          3
       306 TM     138542          0          6          0        583          1          3
       306 TX     131102      11102          6          0        583          0          0 

Nothing new so far, it confirms what I expected.
What I didn’t know is that if “extended partition syntax” is used to specify the target partition (and if you specify the wrong one you get a correct ORA-14401) then the X lock is held on the partition and not the table (it’s held in SX on the table).
Here is the example

SID 306
insert /*+ append */ into test_target partition (p1) select mod(rownum,5), rownum from dual connect by rownum <= 10;

SID 186
insert /*+ append */ into test_target partition (p2) select mod(rownum,5)+5, rownum from dual connect by rownum  select * from v$lock where sid in (306,186) and type = 'TM' order by sid;

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK     CON_ID
---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ----------
       186 TM     138542          0          3          0         11          0          3
       186 TM     138544          0          6          0         11          0          3
       306 TM     138543          0          6          0         35          0          3
       306 TM     138542          0          3          0         35          0          3

SQL> select object_id, object_name, object_type from user_objects where object_id in (138542, 138543, 138544);

 OBJECT_ID OBJECT_NAME    OBJECT_TYPE
---------- -------------- -----------------------
    138544 TEST_TARGET    TABLE PARTITION
    138543 TEST_TARGET    TABLE PARTITION
    138542 TEST_TARGET    TABLE

So in case you know in advance where the data is going (which is generally true specially for range/interval partition) then you can have multiple concurrent loads into the same target table.
I’m not recommending it, just saying it’s possible 😀

5 thoughts on “Concurrent INSERT APPEND into the same table

  1. Very good article! I have tried your solution but it seems that I continue to have locks.
    If the insert into append with partition start from a select on the same table is the lock related to the table of the select?

    Thanks

    Like

  2. Does the select part access a single partition too? It would be easier if you can just provide a few lines long testcase (you can use the one in the blog post as starting point, just use a physical table instead of dual)

    Like

    • Hi Mauro, below an example.

      I have a target table T_TGT created with NOLOGGING option and with a partition by LIST on field PARTITION_ID.

      I have in all queries the same source table T_SRC in join with a table that contains a list of condition to be applied (T_COND_1, T_COND_2, T_COND_3).

      So I insert in multithread in tgt table always starting from T_SRC (that is not partitioned because if a temp table that contain only data in scope) in join with a different table with different conditions (T_COND_1, T_COND_2, T_COND_3).

      First query:

      INSERT INTO /*+ APPEND */ T_TGT PARTITION (P1)
      (
      PARTITION_ID,
      COL1,
      COL2,
      COL3
      )
      SELECT /*+ LEADING(T1 T2) USE_NL(T1) */
      ‘1’
      ,T1.COL1
      ,T1.COL2
      ,T1.COL3
      FROM
      T_SRC T1
      , T_COND_1 T2
      WHERE
      ( (1=0) OR T2.PK = 1 AND (COL1 IN (‘VAL1’, ‘VAL2’)) AND (COL2 IN (‘FA’))
      OR T2.PK = 2 AND (COL1 IN (‘VAL1’, ‘VAL2’)) AND (COL2 IN (‘FB’)))

      Second query:
      INSERT INTO /*+ APPEND */ T_TGT PARTITION (P2)
      (
      PARTITION_ID,
      COL1,
      COL2,
      COL3
      )
      SELECT /*+ LEADING(T1 T2) USE_NL(T1) */
      ‘2’
      ,T1.COL1
      ,T1.COL2
      ,T1.COL3
      FROM
      T_SRC T1
      , T_COND_2 T2
      WHERE
      ( (1=0) OR T2.PK = 1 AND (COL1 IN (‘VAL1’, ‘VAL2’)) AND (COL2 IN (‘FA’))
      OR T2.PK = 2 AND (COL1 IN (‘VAL1’, ‘VAL2’)) AND (COL2 IN (‘FB’)))

      Third query:
      INSERT INTO /*+ APPEND */ T_TGT PARTITION (P3)
      (
      PARTITION_ID,
      COL1,
      COL2,
      COL3
      )
      SELECT /*+ LEADING(T1 T2) USE_NL(T1) */
      ‘3’
      ,T1.COL1
      ,T1.COL2
      ,T1.COL3
      FROM
      T_SRC T1
      , T_COND_3 T2
      WHERE
      ( (1=0) OR T2.PK = 1 AND (COL1 IN (‘VAL1’, ‘VAL2’)) AND (COL2 IN (‘FA’))
      OR T2.PK = 2 AND (COL1 IN (‘VAL1’, ‘VAL2’)) AND (COL2 IN (‘FB’)))

      From my analysis (unfortunately i have not DBA grants so i not see $V_LOCK table) it seems that one query wait the other one before executed.

      I have also tried to add PARALLEL hint near append in the INSERT INTO CLAUSE and also in SELECT CLAUSE and enabled DML PARALLEL. In this case performance are a bit better, but i want to understand your suggestion and why in this case your suggestion seems to not work correctly.

      Many thanks

      Like

      • Ciao Andrea, sorry for the delay.

        I cannot reproduce your problem, below is my TC (a simplified version of yours)

        DDL:
        create table t_tgt (partition_id varchar2(1), col1 number, col2 number, col3 number) partition by list (partition_id) (partition p1 values (‘1’), partition p2 values (‘2’), partition p3 values (‘3’));

        I’ll then lock exclusively a single partition (p1)
        MPAGANO@orcl> lock table t_tgt partition (p1) in exclusive mode;
        MPAGANO@orcl> select * from v$lock where sid = 153;

        ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
        —————- —————- ———- — ———- ———- ———- ———- ———- ———- ———-
        0000000074BE9B58 0000000074BE9BD8 153 AE 134 0 4 0 12751 0 0
        00007F914289ABE8 00007F914289AC58 153 TM 112013 0 6 0 9 0 0 @show_obj 112012,112013

        OWNER OBJECT_ID DATA_OBJECT_ID OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE
        —————————— ———- ————– —————————— —————————— ——————————
        MPAGANO 112012 T_TGT TABLE
        MPAGANO 112013 112013 T_TGT P1 TABLE PARTITION

        From another session (20) the insert into P2 works just fine (P1 is locked)

        MPAGANO@orcl> INSERT INTO /*+ APPEND */ T_TGT PARTITION (P2) (PARTITION_ID,COL1,COL2,COL3) SELECT ‘2’ ,1 COL1 ,2 COL2 ,3 COL3 FROM dual;

        1 row created.

        Elapsed: 00:00:01.65

        while the insert into P1 waits forever waiting on the enq TM on the partition

        MPAGANO@orcl> INSERT INTO /*+ APPEND */ T_TGT PARTITION (P1) (PARTITION_ID,COL1,COL2,COL3) SELECT ‘1’ ,1 COL1 ,2 COL2 ,3 COL3 FROM dual;
        … waiting …

        MPAGANO@orcl> select * from v$lock where sid in (153,20) order by sid, type;

        ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
        —————- —————- ———- — ———- ———- ———- ———- ———- ———- ———-
        0000000074BE9D88 0000000074BE9E08 20 AE 134 0 4 0 273 0 0
        00007F9142752250 00007F91427522C0 20 TM 112013 0 0 3 234 0 0 @show_sess mpagano

        INST_ID SID SERIAL# STATE SEQ# EVENT P1 P2 P3 SECONDS_IN_WAIT SQL_ID SQL_EXEC_ID
        ———- ———- ———- ——————- ———- —————————————- ———- ———- ———- ————— ————- ———–
        1 20 31225 WAITING 857 enq: TM – contention 1414332419 112013 0 255 54y6094ksq801 16777216 <—–
        1 153 15094 WAITED SHORT TIME 487 SQL*Net message to client 1413697536 1 0 0 2j461wqch7tb8 16777216

        So if you can't get this to work it sounds like there might be something else in the real-world case that is preventing it, unrelated from what just shown above

        Cheers,
        Mauro

        Like

      • Just realized my previous update is completely unreadable, copy&paste into a text editor with fixed width chars and it should be readable

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s