INSERT ALL caveat
May 26, 2016 3 Comments
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.
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… 🙂
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) …
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.