在 Oracle 中发出 COMMIT 之前 INSERT 如何工作
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9147131/
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
how INSERT works before issuing a COMMIT in Oracle
提问by user547453
My question is how oracle treats an INSERT transaction before issuing a COMMIT.
我的问题是 oracle 在发出 COMMIT 之前如何处理 INSERT 事务。
While I am doing an INSERT transaction, will oracle wait until I have inserted all my records within that procedure and then when I issue a COMMIT statement will the records be saved in a sequence for this transaction?
当我在做一个 INSERT 事务时，oracle 会等到我在那个过程中插入了我的所有记录，然后当我发出 COMMIT 语句时，这些记录是否会按顺序保存在这个事务中？
In the following code, the first insert that is made is the number of rows (metadata) and then the cursor loops and starts inserting the actual data.
在下面的代码中，第一个插入的是行数（元数据），然后游标循环并开始插入实际数据。
Is there a possibility, in one transaction when I call this procedure, first my metadata record is inserted and then some other data (not related to this transaction) be inserted and then rest of my data. So that, the first record and the rest of the records from the loop are not inserted in a Sequence.
是否有可能，在一个事务中，当我调用此过程时，首先插入我的元数据记录，然后插入一些其他数据（与此事务无关），然后插入其余数据。因此，循环中的第一条记录和其余记录不会插入到序列中。
-- 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;
回答by Branko Dimitrijevic
Another transaction can perform inserts concurrently to your transaction, but your transaction won't see them:
另一个事务可以同时对您的事务执行插入，但您的事务不会看到它们：
- until the other transaction commits (if your transaction is using READ COMMITTED isolation), or
- ever (when using SERIALIZABLE isolation) - you'll need to start another transaction to see them.
- 直到另一个事务提交（如果您的事务使用 READ COMMITTED 隔离），或
- 曾经（使用 SERIALIZABLE 隔离时）-您需要启动另一个事务才能看到它们。
Whether this will yield a correct behavior, is for you to decide.
这是否会产生正确的行为，由您决定。
Just be careful about SELECT COUNT(*) ...- it may not return what you expect. Consider the following scenario:
请注意SELECT COUNT(*) ...- 它可能不会返回您的期望。考虑以下场景：
- The EMP table is initially empty.
- Transaction A starts and inserts a row in EMP, but does not commit.
- Transaction B starts and inserts a row in EMP, but does not commit.
- Transaction A executes
SELECT COUNT(*) FROM EMPand gets 1 (because it sees its own newly inserted row, but does not see B's newly inserted row since B did not commit yet). - Transaction B executes
SELECT COUNT(*) FROM EMPand also gets 1 (for the same reason but in reverse). - Transaction A inserts 1 into EMP_OUTPUT and commits.
- Transaction B inserts 1 into EMP_OUTPUT and commits (assuming there is no key violation).
- 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 并提交（假设没有密钥冲突）。
So, 1 is inserted despite table actually having 2 rows!
因此，尽管表实际上有 2 行，但还是插入了 1！
Unfortunately not even Oracle's SERIALIZABLE isolation will save you from this kind of anomaly. Pretty much the only way to guarantee the "correct" result if to lock the entire table, so no concurrent inserts (or deletes) can occur.
不幸的是，即使是 Oracle 的 SERIALIZABLE 隔离也不能使您免于这种异常。如果锁定整个表，几乎是保证“正确”结果的唯一方法，因此不会发生并发插入（或删除）。
回答by Jon Heller
Use a single SQL statement if possible. It will have statement-level read consistency, and will be much faster.
如果可能，请使用单个 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;
回答by paulsm4
The term you want to google for is "read consistency":
你想用谷歌搜索的术语是“读取一致性”：
http://docs.oracle.com/cd/B12037_01/server.101/b10743/consist.htm
http://docs.oracle.com/cd/B12037_01/server.101/b10743/consist.htm
Bottom line:
底线：
As you know, if you rollback, it's as though the inserts "never happened"
However, other stuff can (and probably did) "happen" in the meantime.
如您所知，如果回滚，就好像插入“从未发生过”
但是，与此同时，其他事情可能（并且可能确实）“发生”。
回答by Cade Roux
You need to run in the Serializable Isolation Level:
您需要在 Serializable Isolation Level 中运行：
http://docs.oracle.com/cd/E11882_01/server.112/e16508/consist.htm#BABCJIDI
http://docs.oracle.com/cd/E11882_01/server.112/e16508/consist.htm#BABCJIDI
"Serializable transactions see only those changes that were committed at the time the transaction began, plus those changes made by the transaction itself through INSERT, UPDATE, and DELETE statements. Serializable transactions do not experience nonrepeatable reads or phantoms."
“可序列化事务只看到在事务开始时提交的那些更改，以及事务本身通过 INSERT、UPDATE 和 DELETE 语句所做的那些更改。可序列化事务不会遇到不可重复读取或幻像。”
