oracle 批量插入分区表和表级锁
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16477377/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Bulk insert into partitioned table and table level lock
提问by Florin Ghita
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:
第 1 节：
insert into table fg_test select * from fg_test where col >=1000;
--1 rows inserted;
Session 2:
第 2 节：
alter table fg_test truncate partition p1;
--table truncated
Session 1:
第 1 节：
rollback;
insert /*+append */ into table fg_test select * from fg_test where col >=1000;
--1 rows inserted;
Session 2:
第 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 Insertis 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 Worksdoes 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)
该如何直接路径INSERT作品并不能解释为什么需要对所有分区的锁。为什么常规插入不锁定未受影响的分区？（我的直觉是锁定是在块级别完成的）
采纳答案by Jon Heller
Your premise is slightly wrong. A direct-path insert does not lock the entire table if you use the partition extension clause.
你的前提有点错误。如果使用分区扩展子句，直接路径插入不会锁定整个表。
Session 1:
第 1 节：
insert /*+append */ into fg_test partition (p2)
select * from fg_test where col >=1000;
Session 2:
第 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% 的情况下它已经足够好了。
回答by HAL 9000
I found the follwing answer on asktom.oracle.com:
我在 asktom.oracle.com 上找到了以下答案：
Ask Tom: Inserts with APPEND Hint
Tom explains many of the inner workings, but the reason whyOracle 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 ...)
也许这只是一个设计决定（例如，不希望庞大的直接负载可能被一个小的未提交事务阻塞并因此锁定所有分区......）
