SQL DDL 语句是否总是给你一个隐式提交，或者你能得到一个隐式回滚？

Do DDL statements always give you an implicit commit, or can you get an implicit rollback?

提问by Nick Pierpoint

If you're halfway through a transaction and perform a DDL statement, such as truncating a table, then the transaction commits.

如果您在事务进行到一半并执行 DDL 语句（例如截断表），则事务将提交。

I was wondering whether this was always the case and by definition, or is there a setting hidden somewhere that would rollbackthe transaction instead of committing.

我想知道这是否总是如此并且根据定义，或者是否有隐藏在某处的设置可以回滚事务而不是提交。

Thanks.

谢谢。

Edit to clarify...

编辑以澄清...

I'm not looking to rollback after a truncate. I just want to confirm that statements already carried out are absolutely alwaysgoing to be committed before a DDL. Just want to make sure there isn't a system property somewhere that someone could set to wreck my code.

我不打算在截断后回滚。我只想确认已经执行的语句绝对总是在 DDL 之前提交。只是想确保在某处没有系统属性，有人可以设置来破坏我的代码。

I understand the need to commit before and after a DDL, but conceptually I'd have thought the same consistency requirement couldbe achieved with a rollback before the DDL and a commit after.

据我所知，之前需要和DDL之后提交，但在概念上我还以为同样的一致性要求可以与DDL之前回滚实现后提交。

回答by JosephStyons

No, it will always commit.

不，它会一直提交。

If you want to rollback, you'll have to do it before the DDL.

如果要回滚，则必须在 DDL 之前执行。

If you want to isolate the DDL from your existing transaction, then you will have to execute it in its' own, separate transaction.

如果要将 DDL 与现有事务隔离，则必须在其自己的单独事务中执行它。

回答by Nick Pierpoint

Technically DDLdoes a commit BEFORE it executes and AFTER it executes.

从技术上讲，DDL在执行之前和执行之后都会进行提交。

Yes same link from Cookie but this is a different aspect of the same issue. It's crucial to understand it's not just one commit, there are two and they happen just before and just after.

是来自 Cookie 的相同链接，但这是同一问题的不同方面。理解这不仅仅是一次提交是至关重要的，有两次，它们发生在之前和之后。

回答by Gary Myers

Actually it will commit IF IT CAN. If it can't successfully commit, the DDL will fail. One way to stop it committing is have a deferred constraint violated.

实际上，如果可以，它会提交。如果它不能成功提交，DDL 将失败。阻止它提交的一种方法是违反延迟约束。

create table fred (id number);
alter table fred add constraint id_ck check (id >0) initially deferred;
insert into fred values (-1);
SQL> create table junk(val number);
create table junk(val number)
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (GC_REF.ID_CK) violated
SQL> desc junk
ERROR:
ORA-04043: object junk does not exist

So if you want to prevent an implicit commit, have a dummy table with a deferred constraint. Insert a violating row in it, and you can make sure the transaction can't be committed until that violation is resolved (eg row deleted).

因此，如果您想防止隐式提交，请使用带有延迟约束的虚拟表。在其中插入违规行，您可以确保在解决该违规（例如删除行）之前无法提交事务。

回答by tuinstoel

A truncate tableor an alter tableor a create tabledo always cause a commit.

一个截断表或ALTER TABLE或创建表常做的原因提交。

Why do you want to rollback when you do a truncate table?

为什么在做truncate table的时候要回滚？

回答by DCookie

Hereis an AskTom article that may help. From the article:

这是一篇可能有帮助的 AskTom 文章。从文章：

"I was wondering why DDL statements are not executed inside an autonomous transaction (like sequences do), so they wouldn′t affect any pending user transaction...

“我想知道为什么 DDL 语句不在自治事务中执行（就像序列那样），所以它们不会影响任何待处理的用户事务......

Can you clarify?

你能澄清一下吗？

Followup June 24, 2003 - 7am US/Eastern:

跟进 2003 年 6 月 24 日 - 美国/东部上午 7 点：

that would be as "confusing" as not doing it that way. anyway, you have atrans so if you want to, you can. "

这将与不这样做一样“令人困惑”。无论如何，你有一个trans，所以如果你愿意，你可以。”

So, if you really need to, you can stick your DDL inside an Autonomous Transaction and do what you want.

所以，如果你真的需要，你可以将你的 DDL 放在一个自治事务中并做你想做的。

EDIT: Bottom line is that unless you go to explicit lengths to "subvert" Oracle, DDL is going to perform a commit. That said, if you absolutely require that a commit is performed at a certain point, why not just perform it explicitly?

编辑：最重要的是，除非您使用明确的长度来“颠覆”Oracle，否则 DDL 将执行提交。也就是说，如果您绝对需要在某个时间点执行提交，为什么不直接执行呢？

回答by Touchstone

DDL statements always performs auto commit,after the execution.

DDL 语句在执行后总是执行自动提交。

If you want it to rollback in case of failure(in server side), then you can set certain flags to indicate the failure and take the appropriate action.

如果您希望它在发生故障时回滚（在服务器端），那么您可以设置某些标志来指示故障并采取适当的措施。

eg: if you have created a table table1. and at the same time you are inserting a record in other table.

例如：如果你已经创建了一个表 table1。同时您在其他表中插入一条记录。

but insertion has failed due to certain reason(set flag=true).Then in that case you can't rollback as create statement is a ddl statement,so you can undo the changes in the database by dropping the table(table1) depending on the value of flag,by Drop statement.

但是由于某种原因插入失败了（设置标志=真）。那么在这种情况下你不能回滚，因为 create 语句是一个 ddl 语句，所以你可以通过删除表（table1）来撤消数据库中的更改，具体取决于标志的值，通过 Drop 语句。

回答by SystemAltered

I agree with DCookie and Tom about the autonomous transaction. I was going to state this also.

我同意 DCookie 和 Tom 关于自主交易的看法。我也打算说明这一点。

Example pseudocode:

示例伪代码：

Do some DML
Call autonomous function, that performs DDL
Do some more DML
rollback or commit all the DML - your choice

I don't see this as being very useful though. If the initial DML and the DDL touch the same table/object, it's not going to work. You'll get contention when you try to perform the DDL. Just like any two transactions blocking each other. And if they're independent objects, I guess I don't see why the execution order matters.

我不认为这很有用。如果初始 DML 和 DDL 接触同一个表/对象，它将无法工作。当您尝试执行 DDL 时，您会遇到争用。就像任何两个相互阻塞的事务一样。如果它们是独立的对象，我想我不明白为什么执行顺序很重要。

回答by Lukasz Szozda

"Always/never" is too strong. For example DDL like CREATE PRIVATE TEMPORARY TABLEfrom Oracle 18c won't COMMITyour transaction.

“总是/从不”太强了。例如，CREATE PRIVATE TEMPORARY TABLE来自 Oracle 18c 的DDL不会是COMMIT您的交易。

Normal scenario:

正常场景：

CREATE TABLE t(i INT);
INSERT INTO t(i) VALUES(21);

CREATE TABLE x(i INT);   -- same for CREATE GLOBAL TEMPORARY TABLE y(i INT); 
ROLLBACK;

SELECT * FROM t;
-- Output:
-- 21

But if you create private table:

但是如果你创建私有表：

CREATE TABLE t(i INT);
INSERT INTO t(i) VALUES(21);

CREATE PRIVATE TEMPORARY TABLE ORA$PTT_temp(i INT);  
-- or
CREATE PRIVATE TEMPORARY TABLE ORA$PTT_tab
AS
SELECT 1 AS c FROM dual;

ROLLBACK;
SELECT * FROM t;
-- Output:
-- no data found

db<>fiddle demo

db<>小提琴演示