oracle 为什么 SQL*Plus 在退出时提交？

Why does SQL*Plus commit on exit?

提问by Chris Gill

Surely this should be the same as a termination of a session and cause a rollback? It seems to me to be the most un-Oracle thing possible. I was actually shocked when I found out that it did this

当然，这应该与终止会话并导致回滚相同吗？在我看来，这可能是最不符合 Oracle 标准的事情。当我发现它这样做时，我真的很震惊

More importantly - would anyone object if Oracle changed it to rollback on exit?

更重要的是 - 如果 Oracle 将其更改为退出时回滚，有人会反对吗？

回答by Gary Myers

Funnily enough, with the 11gR2 release this week (2009-09-03), SQL*Plus now has an option to COMMIT or ROLLBACK on EXIT. Doc here

有趣的是，随着本周 (2009-09-03) 11gR2 的发布，SQL*Plus 现在可以选择在退出时提交或回滚。文档在这里

I'd guess in the next few weeks/months, there'll be an 11gR2 Instant Client which you can use against your current database and get your desired behaviour

我猜在接下来的几周/几个月内，将会有一个 11gR2 即时客户端，您可以将其用于当前数据库并获得所需的行为

A caution to be aware of. If you DISCONNECT or CONNECTto a different session, it will still implicitly commit the transaction (according to the doc).

需要注意的警告。如果您DISCONNECT 或 CONNECT到不同的会话，它仍然会隐式提交事务（根据文档）。

回答by Jonathan Leffler

It was a design decision by Oracle, probably made more than 20 years ago. It is not the design I would have used. Note that it seems to be a property of SQL*Plus, not of the underlying OCI.

这是 Oracle 的一个设计决定，可能是在 20 多年前做出的。这不是我会使用的设计。请注意，它似乎是 SQL*Plus 的属性，而不是基础 OCI 的属性。

If the session terminates abruptly, AFAIK, the session is rolled back, as you'd expect. So, for example, if someone sends a SIGKILL to SQLPlus, the session's transaction should be rolled back. But if the SQLPlus session terminates gracefully (EOF or exit command), then SQL*Plus in its infinite wisdom decides to commit whatever you've done so far.

如果会话突然终止，AFAIK，会话将回滚，如您所料。因此，例如，如果有人向 SQL Plus发送 SIGKILL ，会话的事务应该回滚。但是，如果 SQLPlus 会话正常终止（EOF 或退出命令），那么 SQL*Plus 以其无限的智慧决定提交您迄今为止所做的任何事情。

As to why - I have a theory. In SQL standard databases, you are always in a transaction, even if the only operation you've performed is a SELECT statement. If you don't commit, then any changes you make are rolled back. It is easy to forget to add commit to the end of scripted operations, so making it the default behaviour reduced the number of times someone ran a script to change the database and then ran a second script to see whether the changes took effect correctly. Other DBMS obviate the need for this with modes like 'auto-commit', where each statement is a standalone transaction, automatically committed on completion. That's a useful mode of operation. Other systems provide a mode where you are in auto-commit until you run an explicit BEGIN WORK statement, whereupon (of course), you are in a transaction until the corresponding COMMIT or ROLLBACK. I have been caught out by 'MODE ANSI' databases not committing sufficiently often to make sure that I commit when it matters, but the software I use (not Oracle) still rolls back uncommitted work rather than silently committing it for you - and I would be unhappy if it was changed to work otherwise. (I suppose a configurable default might be OK; I still think rollback uncommitted is the better default, for all it is a nuisance to the unaware; there is less danger of accidentally corrupting a database, and that is of paramount importance to me.)

至于为什么 - 我有一个理论。在 SQL 标准数据库中，您始终处于事务中，即使您执行的唯一操作是 SELECT 语句。如果您不提交，那么您所做的任何更改都会回滚。很容易忘记将提交添加到脚本操作的末尾，因此将其设为默认行为会减少某人运行脚本更改数据库然后运行第二个脚本以查看更改是否正确生效的次数。其他 DBMS 使用“自动提交”等模式消除了这种需求，其中每个语句都是一个独立的事务，在完成时自动提交。这是一种有用的操作模式。其他系统提供了一种模式，在您运行明确的 BEGIN WORK 语句之前，您处于自动提交状态，因此（当然），你在一个事务中直到相应的 COMMIT 或 ROLLBACK。我被“MODE ANSI”数据库没有足够频繁地提交以确保我在重要时提交，但我使用的软件（不是Oracle）仍然回滚未提交的工作而不是默默地为你提交它 - 我会如果更改为其他方式工作，则不高兴。（我想一个可配置的默认值可能没问题；我仍然认为回滚未提交是更好的默认值，因为它对不知情的人来说是一种滋扰；意外损坏数据库的风险较小，这对我来说至关重要。）但是我使用的软件（不是 Oracle）仍然会回滚未提交的工作，而不是默默地为您提交 - 如果将其更改为其他工作，我会不高兴。（我想一个可配置的默认值可能没问题；我仍然认为回滚未提交是更好的默认值，因为它对不知情的人来说是一种滋扰；意外损坏数据库的风险较小，这对我来说至关重要。）但是我使用的软件（不是 Oracle）仍然会回滚未提交的工作，而不是默默地为您提交 - 如果将其更改为其他工作，我会不高兴。（我想一个可配置的默认值可能没问题；我仍然认为回滚未提交是更好的默认值，因为它对不知情的人来说是一种滋扰；意外损坏数据库的风险较小，这对我来说至关重要。）

(Due notice:This is second-hand information from someone who works for another DBMS vendor. It is, however, accurate as far as I know, and based on information accumulated over a period of more than a decade and after asking related questions in various forums.)

（注意：这是其他 DBMS 供应商的二手信息。但是，据我所知，它是准确的，基于十多年积累的信息，并在询问相关问题后各种论坛。）

回答by cagcowboy

You'd have to ask Oracle!

你得问甲骨文！

I must admit that I was surprised when I first discovered this, since you'd think it would take the more conservative approach which would be to do a ROLLBACK.

我必须承认，当我第一次发现这一点时，我感到很惊讶，因为您会认为它会采取更保守的方法，即进行 ROLLBACK。

I can only guess that COMMIT is considered to be the most likely / default action and maybe this is why SQL*Plus does it?

我只能猜测 COMMIT 被认为是最有可能的/默认操作，也许这就是 SQL*Plus 这样做的原因？

回答by msy

Consistent with how a jdbc connection using an Oracle driver implicitly commits the txn on closing the connection.

与使用 Oracle 驱动程序的 jdbc 连接在关闭连接时隐式提交 txn 的方式一致。

回答by Ian Carpenter

Good question.

好问题。

I had a look on metalink and a bug (or change request) has been raised against the default behaviour of committing on normal exit back in 1998. If you have access to metalink look for bug 633247.

我查看了 metalink 并且在 1998 年针对正常退出时提交的默认行为提出了一个错误（或更改请求）。如果您有权访问 metalink，请查找错误 633247。

回答by Waleed Mahmoud

Committing at exit seems the logical thing to me, generally ROLLBACKis the exception, we rollback when something goes wrong, when you insert, update or delete data then you mean to do this i.e. COMMIT.

在退出时提交对我来说似乎是合乎逻辑的事情，通常ROLLBACK是例外，我们在出现问题时回滚，当您插入，更新或删除数据时，您的意思是这样做，即COMMIT。

回答by Rob van Wijk

I think the commit is a good idea and I agree with what Justin and Billy wrote in this thread: http://forums.oracle.com/forums/thread.jspa?messageID=3611345&#3611345

我认为提交是一个好主意，我同意 Justin 和 Billy 在此线程中所写的内容：http: //forums.oracle.com/forums/thread.jspa?messageID=3611345᫑

Regards, Rob.

问候，罗布。