Oracle 是否会在发生错误时回滚事务？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1468116/
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
Does Oracle roll back the transaction on an error?
提问by Jason Baker
This feels like a dumb question, but I see the following in the Oracle concepts guide on transaction management:
这感觉像是一个愚蠢的问题，但我在 Oracle 事务管理概念指南中看到以下内容：
A transaction ends when any of the following occurs:
A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.
A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER. If the current transaction contains any DML statements, Oracle first commits the transaction, and then runs and commits the DDL statement as a new, single statement transaction.
A user disconnects from Oracle. The current transaction is committed.
A user process terminates abnormally. The current transaction is rolled back.
当发生以下任何一种情况时，事务结束：
用户发出没有 SAVEPOINT 子句的 COMMIT 或 ROLLBACK 语句。
用户运行 DDL 语句，例如 CREATE、DROP、RENAME 或 ALTER。如果当前事务包含任何 DML 语句，Oracle 首先提交该事务，然后将 DDL 语句作为新的单语句事务运行并提交。
用户与 Oracle 断开连接。当前事务已提交。
用户进程异常终止。当前事务被回滚。
Am I to interpret the last point to mean that if I issue a query that has an error, the transaction will get rolled back?
我是否将最后一点解释为如果我发出一个有错误的查询，事务将被回滚？
采纳答案by Justin Cave
"User process" in this context is referring to the process running on the client machine that creates the connection to Oracle. In other words, if you are using Application A (SQL*Plus, TOAD, etc.) to connect to Oracle, the user process is SQL*Plus, TOAD, etc. If that user process dies while you were in the middle of a transaction, that transaction will be rolled back. This will happen as soon as PMON discovers that the client has died which may take a bit of time-- it isn't always trivial for Oracle to distinguish the failure of a user process from a user process that just isn't issuing commands at the moment.
此上下文中的“用户进程”是指在客户端计算机上运行的创建与 Oracle 的连接的进程。换句话说，如果您使用应用程序 A（SQL*Plus、TOAD 等）连接到 Oracle，则用户进程是SQL*Plus、TOAD 等。如果该用户进程在您处于事务中间时死亡，则该事务将是回滚。一旦 PMON 发现客户端已经死亡，这可能会发生这种情况，这可能需要一些时间——Oracle 区分用户进程的失败和只是不发出命令的用户进程并不总是微不足道的此时此刻。
回答by Vincent Malgrat
this is an interesting question !
这是个有趣的问题 ！
When Oracle encounters an error, it will rollback the current statement, not the transaction. A statement is any top-level instruction, it can be a SQL statement (INSERT, UPDATE...) or a PL/SQL block.
当 Oracle 遇到错误时，它会回滚当前语句，而不是事务。语句是任何顶级指令，它可以是 SQL 语句（INSERT、UPDATE...）或 PL/SQL 块。
This means that when a statement (for example a pl/sql procedure called from java) returns an error, Oracle will put the transaction in the same logical state as before the call. This is immensely helpful, you don't have to worry about half-executed procedures (**).
这意味着当语句（例如从 java 调用的 pl/sql 过程）返回错误时，Oracle 会将事务置于与调用之前相同的逻辑状态。这非常有用，您不必担心半执行程序 (**)。
This thread on AskTom covers the same topic:
[the statement] either ENTIRELY happens or it ENTIRELY DOES NOT happen and the way that works is the database does the logical equivalent of:
[语句] 要么完全发生，要么完全不发生，工作方式是数据库在逻辑上等效于：
begin
savepoint foo;
<<your statement>>
exception
when others then rollback to foo;
RAISE;
end;
This feature, in my opinion, is why it is a lot easier to write database code (*) in pl/sql than in any other language.
在我看来，这个特性就是为什么在 pl/sql 中编写数据库代码 (*) 比在任何其他语言中都容易得多。
(*) code that interacts with an Oracle DB of course, I suppose the native procedural languages of the other DBMS have similar features.
(*) 与 Oracle DB 交互的代码当然，我想其他 DBMS 的本机过程语言具有类似的功能。
(**) This only concerns DML since DDL are not transactionalin Oracle. Be also careful with some DBMS packages that update the data dictionary (such as DBMS_STATS), they often do DDL-like changes and issue commits. Refer to the documentationin case of doubts.
(**) 这仅涉及 DML，因为DDL在 Oracle中不是事务性的。还要小心一些更新数据字典的 DBMS 包（例如DBMS_STATS），它们经常进行类似 DDL 的更改并发出提交。如有疑问，请参阅文档。
Update:this behaviour is one of the most important concept in PL/SQL, I will provide a small example to demonstrate the atomicity of the pl/sql statements:
更新：此行为是 PL/SQL 中最重要的概念之一，我将提供一个小示例来演示pl/sql 语句的原子性：
SQL> CREATE TABLE T (a NUMBER);
Table created
SQL> CREATE OR REPLACE PROCEDURE p1 AS
2 BEGIN
3 -- this statement is successful
4 INSERT INTO t VALUES (2);
5 -- this statement will raise an error
6 raise_application_error(-20001, 'foo');
7 END p1;
8 /
Procedure created
SQL> INSERT INTO t VALUES (1);
1 row inserted
SQL> EXEC p1;
begin p1; end;
ORA-20001: foo
ORA-06512: at "VNZ.P1", line 5
ORA-06512: at line 2
SQL> SELECT * FROM t;
A
----------
1
Oracle has rolled back the transaction to the point just before calling p1. There is no half-work done. It is as if the procedure p1 had never been called.
Oracle 已经将事务回滚到调用 p1 之前的点。没有完成一半的工作。就好像从未调用过过程 p1 一样。
回答by Jay
I agree with Justin, his insight is correct. Adding additional information: As the application developer, you should explicitly call a rollback command if errors happen. This means, you should also consider grouping statements into transactional blocks as appropriate. Transactional blocks and rollbacks are handled differently by different technologies, it's worth some research to make sure you understand it well.
我同意贾斯汀的观点，他的见解是正确的。添加附加信息：作为应用程序开发人员，如果发生错误，您应该显式调用回滚命令。这意味着，您还应该考虑将语句分组到适当的事务块中。不同的技术对事务块和回滚的处理方式不同，值得进行一些研究以确保您很好地理解它。
