sql - Oracle 12c - 我们可以在将数据插入表而不是整个表时锁定分区吗？

Question

需要从 Informatica 并行运行 2 个作业，以将数据并行插入 Oracle 中的同一个表。

一个进程将插入到一个分区，另一个进程将插入到另一个分区。

oracle默认是怎么做的？它是否锁定整个 Oracle 表，以便没有 2 个并行进程可以从 Informatica 插入数据，或者它是否可以仅锁定一个表的分区，而另一个进程可以并行运行并插入到另一个分区？

谢谢。

Answer 1

使用普通的旧插入，两个语句永远不会互相阻塞。

1. Oracle 使用行级锁定并且不会随机将它们升级为表锁。由于根据定义 INSERT 会创建新行，因此它们不可能相互阻塞。

该规则有一些奇怪的异常(exception)情况。例如，如果表有一个更改其他行的触发器，或者如果两个 INSERT 尝试使用相同的主键值。但这两种情况都极不可能，并且可能是您无论如何都不想运行的意外错误。位图索引不是为并发 DML 设计的，也可能导致常规插入阻塞。

对于直接路径写入或并行插入，答案更复杂。

并行处理和直接路径写入使事情变得更加复杂。

1. 如果 INSERT 操作使用并行或直接路径写入，即使添加一行也会锁定整个表并防止其他 session 更改任何内容。例如，如果它生成类似 insert/*+ parallel(8) */... 的语句，它们将阻止任何其他 DML 运行。这是因为直接路径写入会直接修改数据文件以获得最佳性能，而典型的并发控制不起作用。
2. 但 Informatica 可能不会使用 Oracle 的并行处理，而是会创建多个并发线程来进行自主开发的并行处理。如果是这样，那么这两个进程就不会互相阻塞。
3. 但也有可能 Informatica 不使用 Oracle 并行处理，但确实使用直接路径写入。例如，如果它生成类似 insert/*+ append */... 的语句，它们将阻止任何其他 DML 运行，即使该 DML 正在修改不同的分区。这可能是因为 Oracle 无法轻易地提前预测哪个分区将被修改，而将它们全部锁定会更简单。
4. 但是，如果明确指定了分区，则两个并行或直接路径写入可以并发运行，只要它们修改不同的分区即可。

下面是一个快速演示。首先，使用分区表创建一个简单的测试模式。

create table test1(a number)
partition by list(a)
(
    partition p1 values (1),
    partition p2 values (2)
) nologging;

证明在一个 session 中的直接路径写入将阻止在另一个 session 中的任何类型的插入:

--Session 1: Run this statement but don't commit.  It should finish in less than a second.
insert /*+ append */ into test1 select 1 from dual;

--Session 2: Run this statement but it will never finish, even though it
--           inserts into a different partition.
insert into test1 select 2 from dual;

证明只要分区明确命名，一个 session 中的直接路径写入不会阻塞另一个 session :

--Session 1: Run this statement but don't commit.  It should run in less than a second.
insert /*+ append */ into test1 partition (p1) select 1 from dual;

--Session 2: This statement will run immediately.
insert into test1 partition (p2) select 2 from dual;

现在呢？

根据 Informatica 的配置方式，上述任何一种情况都是可能的。首先检查 Informatica 设置。

查看生成的 SQL 以了解 Informatica 正在运行什么。使用类似 select * from gv$sql where sql_fulltext like '... 的查询。

查看这些 SQL 语句的解释计划，看看查询是否也按照您希望的方式运行。使用类似select * from table(dbms_xplan.display_cursor(sql_id => '...) 的查询来查找计划。查看操作列；LOAD AS SELECT 表示直接-path is being used and LOAD TABLE CONVENTIONAL 表示未使用直接路径。您还可以检查 PARALLEL 或 PX 以查看 Oracle使用并行处理。

不幸的是，有很多原因可能会要求直接路径或并行性但不使用。请参阅我令人沮丧的长列表，列出缺乏并行性的可能原因 here ，并查看 this list由于缺乏直接路径写入的原因。