Bulk insert into partitioned table and table level lock

Question

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)

Solution 1

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.

Solution 2

I found the follwing answer on asktom.oracle.com:

Ask Tom: Inserts with APPEND Hint

Tom explains many of the inner workings, but the reason why Oracle locks the whole table and not only affected partitions is still not clear.

Maybe it's just a design decision (e.g. not wanting the big bulky direct load to be potentially blocked by one smallish uncommited transaction and therefore locking all partitions ...)

Comments

[AnBisw]

@JonHeller So, If I use a direct path insert into a table using partition extension, that partition would still be locked in that session until a COMMIT is issued. Is that correct? I am not even sure if "partition locking" is even a thing, pardon my ignorance.