Sql – Bulk insert into partitioned table and table level lock

bulkinsertlockingoraclesql

I want to know the core reason(the mechanics of segments, blocks, locks that the engine does) why bulk insert(with direct-path) locks the entire table so if I insert into a partition, I can't truncate another partition which is not affected(apparently) by insert.

A conventional insert(without append hint) permits to truncate some nonaffected partitions.(Notice that i speak about non-commited transaction.)

Below an example to ilustrate it.

Let be a table:

 CREATE TABLE FG_TEST 
   (COL NUMBER ) 
  PARTITION BY RANGE (COL) 
 (PARTITION "P1"  VALUES LESS THAN (1000), 
  PARTITION "P2"  VALUES LESS THAN (2000));

Insert into table fg_test values (1);
insert into table fg_test values (1000);
commit;

Session 1:

insert into table fg_test select * from fg_test where col >=1000;
--1 rows inserted;

Session 2:

alter table fg_test truncate partition p1;
--table truncated

Session 1:

rollback;
insert /*+append */ into table fg_test select * from fg_test where col >=1000;
--1 rows inserted;

Session 2:

alter table fg_test truncate partition p1;
--this throws ORA-00054: resource busy and acquire with NOWAIT specified 
--or timeout expired

The Doc on Diret-Path Insert is pretty abrupt on this subject and just says:

During direct-path INSERT, the database obtains exclusive locks on the
table (or on all partitions of a partitioned table). As a result,
users cannot perform any concurrent insert, update, or delete
operations on the table, and concurrent index creation and build
operations are not permitted.

The How Direct-Path INSERT Works does not explain why the lock is needed for all partitions.
And why conventional insert does not lock nonaffected partitions? (My intuition is that the lock is done at block level)

Best Answer

Your premise is slightly wrong. A direct-path insert does not lock the entire table if you use the partition extension clause.

Session 1:

insert /*+append */ into fg_test partition (p2)
select * from fg_test where col >=1000;

Session 2:

alter table fg_test truncate partition p1;
--table truncated

The new question is: When the partition extension clause is NOT used, why do conventional and direct-path inserts have different locking mechanisms? This clarification makes the question easier, but without inside knowledge the answer below is still only a guess.

It was easier to code a feature that locks the entire table. And it runs faster, since there is no need to track which partitions are updated.

There's usually no need for a more fine-grained lock. Most systems or processes that use direct-path writes only update one large table at a time. If a more fine-grained lock is really needed, the partition extension clause can be used. It's not quite as convenient, since only one partition can be referenced at a time. But it's good enough 99.9% of the time.

Related Topic