INSERT ALL caveat

Why you might want to think twice about using INSERT ALL.

One of those things I knew and then forgot.

So, let’s say you’ve got three tables or a partitioned table or something like that.

Let’s use regional tables for simplicity.

drop table t1_r1;
drop table t1_r2;
drop table t1_r3;

create table t1_r1
(col1 varchar2(2) not null
,col2 number not null
,check( col1 in ('R1')));

create table t1_r2
(col1 varchar2(2) not null
,col2 number not null
,check( col1 in ('R2')));

create table t1_r3
(col1 varchar2(2) not null
,col2 number not null
,check( col1 in ('R3')));

insert into t1_r1 values ('R1',1);
insert into t1_r2 values ('R2',1);
insert into t1_r3 values ('R3',1);

commit;

And you want a routine that will insert into one of those tables depending on region.

And you’re a simple fellow, so you go with an IF statement:

create or replace procedure p1 (
  col1 in varchar2, 
  col2 in number
)
as
begin
  if col1 = 'R1'
  then
      insert into t1_r1 values(col1,col2);
  elsif col1 = 'R2'
  then
      insert into t1_r3 values(col1,col2);
  else 
      insert into t1_r3 values(col1,col2);
  end if;
end p1;
/

Procedure P1 compiled

And then in the same session you run this uncommitted:

exec p1('R1',2);

PL/SQL procedure successfully completed.

And then in another session you decide to truncate table T1_R3:

truncate table t1_r3;

Table T1_R3 truncated.

No problem.
None was expected.

However…

Let’s say that we decide to tidy up that procedure and get rid of some of the repetition by using an INSERT ALL statement.
I will use a standalone sql statement just to demonstrate a further minor aspect rather than using a procedure with a bound parameter.

insert all
when col1 = 'R1' then into t1_r1
when col1 = 'R2' then into t1_r2
when col1 = 'R3' then into t1_r3
select 'R1' col1,2 col2
from dual;

1 row inserted.

Let’s revisit the truncate:

truncate table t1_r3;

SQL Error: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
00054. 00000 -  "resource busy and acquire with NOWAIT specified or timeout expired"
*Cause:    Interested resource is busy.
*Action:   Retry if necessary or increase timeout.

TM share locks from the INSERT ALL on all three possible targets prevent the TRUNCATE.

So, a simple/simplisitic illustration of why you might want to think twice about whether INSERT ALL is the best feature for your use case, based on a real life problem.

Advertisements

3 Responses to INSERT ALL caveat

  1. I magnificently missed the point of this on my first read through; insert all slapping down TM locks irrespective of which table is being used by the WHEN clause, so I had to have a look in v$lock…

    14:42:39 NEIL @ ORCL01 > select object_name,object_id
    from dba_objects where object_name in (‘T1_R1′,’T1_R2′,’T1_R3’);

    OBJECT_NAM OBJECT_ID
    ———- ———-
    T1_R1 96452
    T1_R2 96453
    T1_R3 96454

    14:42:39 NEIL @ ORCL01 > exec p1(‘R1’,2);

    PL/SQL procedure successfully completed.

    14:42:39 NEIL @ ORCL01 > select (some cols) from v$lock where id1 in (‘96452′,’96453′,’96454’);
    ADDR KADDR SID TY ID1
    —————- —————- ———- — ———-
    00007F9DCEA70590 00007F9DCEA705F8 277 TM 96452

    14:42:39 NEIL @ ORCL01 > commit;

    Commit complete.

    14:42:39 NEIL @ ORCL01 > insert all
    14:42:39 2 when col1 = ‘R1’ then into t1_r1
    14:42:39 3 when col1 = ‘R2’ then into t1_r2
    14:42:39 4 when col1 = ‘R3’ then into t1_r3
    14:42:39 5 select ‘R1’ col1,2 col2
    14:42:39 6 from dual;

    1 row created.

    14:42:39 NEIL @ ORCL01 >
    14:42:39 NEIL @ ORCL01 > select (some cols) from v$lock where id1 in (‘96452′,’96453′,’96454’);

    ADDR KADDR SID TY ID1
    —————- —————- ———- — ———-
    00007F9DCEA78B38 00007F9DCEA78BA0 277 TM 96454
    00007F9DCEA78B38 00007F9DCEA78BA0 277 TM 96453
    00007F9DCEA78B38 00007F9DCEA78BA0 277 TM 96452

    14:42:39 NEIL @ ORCL01 >
    14:42:39 NEIL @ ORCL01 > commit;

    Commit complete.

    Lovely. TM locks everywhere. I would ask why you’re doing truncate’s in your app though… 🙂

  2. Jan says:

    The point of the INSERT ALL is not to do an one-row inserts in multiple sessions (OLTP). Imagine INSERT ALL … FROM BIG_TABLE with all the values (R1, R2, R3) …

    • Dom Brooks says:

      That’s not what I’m trying to illustrate… although it’s a fair point.
      This is just a simple example to underline the issue with TM locks.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: