批量插入分区表和表级锁

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:

关于Diret-Path Insert的Doc在这个主题上是非常突然的,只是说:

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.

在直接路径INSERT期间,数据库获取表(或分区表的所有分区)上的独占锁。因此,用户无法对表执行任何并发插入,更新或删除操作,并且不允许并发索引创建和构建操作。

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)

Direct-Path INSERT如何工作并不能解释为什么所有分区都需要锁定。为什么传统插件不能锁定不受影响的分区? (我的直觉是锁是在块级完成的)

2 个解决方案

#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.

通常不需要更细粒度的锁。大多数使用直接路径写入的系统或进程一次只更新一个大表。如果确实需要更细粒度的锁,则可以使用分区扩展子句。它不太方便,因为一次只能引用一个分区。但它有99.9%的时间足够好。

#2

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

我在asktom.oracle.com上找到了以下答案:

Ask Tom: Inserts with APPEND Hint

问汤姆:插入APPEND提示

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.

Tom解释了许多内部工作,但Oracle锁定整个表而不仅影响分区的原因仍然不明确。

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 ...)

也许这仅仅是一个设计决策(例如,不希望大型的大型直接负载可能被一个小的未通信事务阻塞,因此锁定所有分区......)