SQL oracle - 需要提交哪些语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9541013/
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
oracle - what statements need to be committed?
提问by toop
What are the list of statements that need to be committed before further action on the table in order to avoid a lock? I am not talking about full transactions with multiple statements and transaction integrity; instead I am referring to single statements.
在对表进行进一步操作之前需要提交哪些语句列表以避免锁定？我不是在谈论具有多个语句和事务完整性的完整事务；相反，我指的是单个语句。
I know insert should be committed but truncate has an autocommit. What is the full list of statements that need to be committed?
我知道应该提交插入但截断有自动提交。需要提交的完整语句列表是什么？
Need to be committed (starter list):
需要提交（入门列表）：
UPDATE
INSERT
DELETE
回答by rics
DML (Data Manipulation Language) commands need to becommited/rolled back. Hereis a list of those commands.
DML（数据操作语言）命令需要提交/回滚。这是这些命令的列表。
Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:
数据操作语言 (DML) 语句用于管理模式对象内的数据。一些例子：
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain
MERGE - UPSERT operation (insert or update)
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency
回答by APC
In mechanical terms a COMMIT makes a transaction. That is, a transaction is all the activity (one or more DML statements) which occurs between two COMMIT statements (or ROLLBACK).
在机械方面，COMMIT 进行交易。也就是说，事务是发生在两个 COMMIT 语句（或 ROLLBACK）之间的所有活动（一个或多个 DML 语句）。
In Oracle a DDL statement is a transaction in its own right simply because an implicit COMMIT is issued before the statement is executed and again afterwards. TRUNCATE is a DDL command so it doesn't need an explicit commit because calling it executes an implicit commit.
在 Oracle 中，DDL 语句本身就是一个事务，因为在执行语句之前和之后再次发出隐式 COMMIT。TRUNCATE 是一个 DDL 命令，因此它不需要显式提交，因为调用它会执行隐式提交。
From a system design perspective a transaction is a business unit of work. It might consist of a single DML statement or several of them. It doesn't matter: only full transactions require COMMIT. It literally does not make sense to issue a COMMIT unless or until we have completed a whole business unit of work.
从系统设计的角度来看，事务是一个业务工作单元。它可能包含一个 DML 语句或其中的几个。没关系：只有完整的事务需要 COMMIT。除非或直到我们完成了整个业务单元的工作，否则发出 COMMIT 确实没有意义。
This is a key concept. COMMITs don't just release locks. In Oracle they also release latches, such as the Interested Transaction List. This has an impact because of Oracle's read consistency model. Exceptions such as ORA-01555: SNAPSHOT TOO OLDor ORA-01002: FETCH OUT OF SEQUENCEoccur because of inappropriate commits. Consequently, it is crucial for our transactions to hang onto locks for as long as they need them.
这是一个关键概念。COMMIT 不只是释放锁。在 Oracle 中，它们还释放锁存器，例如感兴趣的事务列表。由于 Oracle 的读取一致性模型，这会产生影响。由于不适当的提交而发生ORA-01555: SNAPSHOT TOO OLD或ORA-01002: FETCH OUT OF SEQUENCE发生的异常。因此，对于我们的事务来说，只要需要它们就挂在锁上是至关重要的。
回答by Fabian Barney
DML have to be committed or rollbacked. DDL cannot.
必须提交或回滚 DML。DDL 不能。
http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commands
http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commands
You can switch auto-commit on and that's again only for DML. DDL are never part of transactions and therefore there is nothing like an explicit commit/rollback.
您可以打开自动提交，这也仅适用于 DML。DDL 从来都不是事务的一部分，因此没有什么比显式提交/回滚更好的了。
truncateis DDL and therefore commited implicitly.
truncate是 DDL，因此隐式提交。
Edit
I've to say sorry. Like @DCookie and @APC stated in the comments there exist sth like implicit commitsfor DDL. See here for a question about that on Ask Tom.
This is in contrast to what I've learned and I am still a bit curious about.
编辑
我不得不说对不起。就像评论中声明的@DCookie 和@APC 一样，存在诸如DDL 的隐式提交之类的东西。请参阅此处了解有关Ask Tom 的问题。这与我所学到的相反，我仍然有点好奇。
回答by John Flack
And a key point - although TRUNCATE TABLE seems like a DELETE with no WHERE clause, TRUNCATE is not DML, it is DDL. DELETE requires a COMMIT, but TRUNCATE does not.
还有一个关键点——虽然 TRUNCATE TABLE 看起来像一个没有 WHERE 子句的 DELETE，但 TRUNCATE 不是 DML，它是 DDL。DELETE 需要 COMMIT，但 TRUNCATE 不需要。
