在 Oracle 中发出 COMMIT 之前 INSERT 是如何工作的

Question

我的问题是 oracle 在发出 COMMIT 之前如何处理 INSERT 事务。

当我执行 INSERT 事务时，oracle 是否会等到我在该过程中插入所有记录，然后当我发出 COMMIT 语句时，记录是否会按此事务的顺序保存？

在下面的代码中，首先插入的是行数（元数据），然后游标循环并开始插入实际数据。

是否有可能，在一个事务中，当我调用此过程时，首先插入我的元数据记录，然后插入一些其他数据（与此事务无关），然后插入我的其余数据。因此，循环中的第一条记录和其余记录不会插入到序列中。

-- This code belongs to proecdure when ever a user clicks on insert 
-- button from the front end form

DECLARE

    rowcnt NUMBER;

    CURSOR c_get_employ IS
    SELECT EMP.EMPLOYER_ID, EMP.EMPLOYER_NAME, EMP.EMPLOYER_LOCATION
          FROM EMP
            WHERE EMP.EMPLOYER_COUNTRY = 'USA'
    ORDER BY EMP.EMPLOYER_ID;

BEGIN

    Select count(*) 
    INTO rowcnt 
    FROM EMP
    WHERE EMP.EMPLOYER_COUNTRY = 'USA'
    ORDER BY EMP.EMPLOYER_ID;

    -- I want to insert the 'number of employee records' that will be inserted (metadata)

    INSERT INTO EMP_OUTPUT 
        (EMPID, EMPNAME, EMPLOC, ECOUNT)
    VALUES
        (,,,rowcnt);

    -- Then loop through the cursor and start inserting the data
    FOR c_post_employ IN c_get_employ LOOP

        INSERT INTO EMP_OUTPUT 
            (EMPID, EMPNAME, EMPLOC)
        VALUES
            (c_post_employ.EMPLOYER_ID,c_post_employ.EMPLOYER_NAME,c_post_employ.EMPLOYER_LOCATION);

    END LOOP;

    COMMIT;

END;

Answer 1

另一个事务可以与您的事务同时执行插入，但您的事务不会看到它们：

• 直到另一个事务提交（如果您的事务使用 READ COMMITTED 隔离），或者
• 永远（当使用 SERIALIZABLE 隔离时） - 您需要启动另一笔交易去看他们。

这是否会产生正确的行为，由您决定。

---

请注意 SELECT COUNT(*) ... - 它可能不会返回您期望的结果。考虑以下场景：

• EMP 表最初是空的。
• 事务 A 启动并在 EMP 中插入一行，但不提交。
• 事务 B 启动并在 EMP 中插入一行，但不提交。
• 事务 A 执行 SELECT COUNT(*) FROM EMP 并获取 1（因为它看到自己新插入的行，但没有看到 B 新插入的行，因为 B 尚未提交）。
• 事务B执行SELECT COUNT(*) FROM EMP并且也得到1（出于相同的原因但相反）。
• 事务 A 将 1 插入 EMP_OUTPUT 并提交。
• 事务 B 将 1 插入 EMP_OUTPUT 并提交（假设没有密钥违规）。

因此，尽管表实际上有 2 行，但还是插入了 1 行！

不幸的是，即使是 Oracle 的 SERIALIZABLE 隔离也无法避免这种异常情况。如果锁定，几乎是保证“正确”结果的唯一方法整个表，因此不会发生并发插入（或删除）。

Answer 2

如果可能，请使用单个 SQL 语句。它将具有语句级读取一致性，并且速度会快得多。

insert into emp_output(empid, empname, emploc, ecount)
with employees as
(
    select employer_id, employee_name, employer_location
    from emp
    where employer_country = 'USA'
    order by employer_id    
)
select null, null, null, count(*) from employees
union all
select employer_id, employee_name, employer_location, null from employees;

Answer 3

您要在 Google 上搜索的术语是“读取一致性”：

http: //docs.oracle.com/cd/B12037_01/server.101/b10743/consist.htm

底线：

• As你知道，如果你回滚，就好像插入“从未发生过”

• 但是，其他东西可以（并且可能确实发生过）同时“发生”。

Answer 4

您需要在可序列化隔离级别中运行：

http://docs.oracle.com/cd/E11882_01/server.112/e16508/consist.htm#BABCJIDI

“可序列化事务仅看到事务时提交的那些更改开始，加上事务本身通过 INSERT、UPDATE 和 DELETE 语句进行的更改不会经历不可重复的读取或操作。幻影。”