oracle 在只有选择的事务中提交和回滚有区别吗？

Is there a difference between commit and rollback in a transaction only having selects?

提问by Tamas Czinege

The in-house application framework we use at my company makes it necessary to put every SQL query into transactions, even though if I know that none of the commands will make changes in the database. At the end of the session, before closing the connection, I commit the transaction to close it properly. I wonder if there were any particular difference if I rolled it back, especially in terms of speed.

我们在我公司使用的内部应用程序框架使得必须将每个 SQL 查询放入事务中，即使我知道没有任何命令会在数据库中进行更改。在会话结束时，在关闭连接之前，我提交事务以正确关闭它。我想知道如果我回滚它是否有什么特别的区别，尤其是在速度方面。

Please note that I am using Oracle, but I guess other databases have similar behaviour. Also, I can't do anything about the requirement to begin the transaction, that part of the codebase is out of my hands.

请注意，我使用的是 Oracle，但我猜其他数据库也有类似的行为。此外，我无法对开始交易的要求做任何事情，代码库的那部分不在我的掌握之中。

采纳答案by S.Lott

Databases often preserve either a before-image journal (what it was before the transaction) or an after-image journal (what it will be when the transaction completes.) If it keeps a before-image, that has to be restored on a rollback. If it keeps an after-image, that has to replace data in the event of a commit.

数据库通常保留前映像日志（事务之前的内容）或后映像日志（事务完成后的内容。）如果保留前映像，则必须在回滚时恢复. 如果它保留了后映像，则必须在提交时替换数据。

Oracle has both a journal and rollback space. The transaction journal accumulates blocks which are later written by DB writers. Since these are asychronous, almost nothing DB writer related has any impact on your transaction (if the queue fills up, then you might have to wait.)

Oracle 既有日志空间又有回滚空间。事务日志累积块，然后由 DB 编写器写入。由于这些是异步的，几乎没有任何与数据库编写器相关的内容对您的事务有任何影响（如果队列已满，那么您可能需要等待。）

Even for a query-only transaction, I'd be willing to bet that there's some little bit of transactional record-keeping in Oracle's rollback areas. I suspect that a rollback requires some work on Oracle's part before it determines there's nothing to actually roll back. And I think this is synchronous with your transaction. You can't really release any locks until the rollback is completed. [Yes, I know you aren't using any in your transaction, but the locking issue is why I think a rollback has to be fully released then all the locks can be released, then your rollback is finished.]

即使对于仅查询的事务，我也愿意打赌在 Oracle 的回滚区域中会有一些事务性记录保存。我怀疑在 Oracle 确定没有任何实际回滚之前，回滚需要对其进行一些工作。我认为这与您的交易同步。在回滚完成之前，您无法真正释放任何锁。[是的，我知道您没有在您的事务中使用任何，但锁定问题是为什么我认为必须完全释放回滚然后才能释放所有锁，然后您的回滚完成。]

On the other hand, the commit is more-or-less the expected outcome, and I suspect that discarding the rollback area might be slightly faster. You created no transaction entries, so the db writer will never even wake up to check and discover that there was nothing to do.

另一方面，提交或多或少是预期的结果，我怀疑丢弃回滚区域可能会稍微快一点。您没有创建任何事务条目，因此数据库编写器甚至永远不会醒来检查并发现无事可做。

I also expect that while commit may be faster, the differences will be minor. So minor, that you might not be able to even measure them in a side-by-side comparison.

我也希望虽然提交可能会更快，但差异会很小。如此小，您甚至可能无法在并排比较中衡量它们。

回答by Walter Mitty

I agree with the previous answers that there's no difference between COMMIT and ROLLBACK in this case. There might be a negligible difference in the CPU time needed to determine that there's nothing to COMMIT versus the CPU time needed to determine that there's nothing to ROLLBACK. But, if it's a negligible difference, we can safely forget about about it.

我同意之前的答案，即在这种情况下 COMMIT 和 ROLLBACK 之间没有区别。确定没有任何内容需要提交所需的 CPU 时间与确定没有任何内容需要回滚所需的 CPU 时间之间的差异可能可以忽略不计。但是，如果差异可以忽略不计，我们可以放心地忘记它。

However, it's worth pointing out that there's a difference between a session that does a bunch of queries in the context of a single transaction and a session that does the same queries in the context of a series of transactions.

但是，值得指出的是，在单个事务的上下文中执行一组查询的会话与在一系列事务的上下文中执行相同查询的会话之间存在差异。

If a client starts a transaction, performs a query, performs a COMMITor ROLLBACK, then starts a second transaction and performs a second query, there's no guarantee that the second query will observe the same database state as the first query. Sometimes, maintaining a single consistent view of the data is of the essence. Sometimes, getting a more current view of the data is of the essence. It depends on what you are doing.

如果客户端启动事务、执行查询、执行 COMMITor ROLLBACK，然后启动第二个事务并执行第二个查询，则不能保证第二个查询将观察到与第一个查询相同的数据库状态。有时，保持数据的单一一致视图至关重要。有时，获得更当前的数据视图至关重要。这取决于你在做什么。

I know, I know, the OP didn't ask this question. But some readers may be asking it in the back of their minds.

我知道，我知道，OP 没有问这个问题。但是有些读者可能会在心里问这个问题。

回答by Tony Andrews

In general a COMMIT is much faster than a ROLLBACK, but in the case where you have done nothing they are effectively the same.

一般来说，COMMIT 比 ROLLBACK 快得多，但在您什么都不做的情况下，它们实际上是相同的。

回答by David Aldridge

The documentation states that:

该文件指出：

• Oracle recommends that you explicitly end every transaction in your application programs with a COMMIT or ROLLBACK statement, including the last transaction, before disconnecting from Oracle Database. If you do not explicitly commit the transaction and the program terminates abnormally, then the last uncommitted transaction is automatically rolled back. A normal exit from most Oracle utilities and tools causes the current transaction to be committed. A normal exit from an Oracle precompiler program does not commit the transaction and relies on Oracle Database to roll back the current transaction.

• Oracle 建议您在与 Oracle 数据库断开连接之前，使用 COMMIT 或 ROLLBACK 语句明确结束应用程序中的每个事务，包括最后一个事务。如果没有明确提交事务，程序异常终止，那么最后一个未提交的事务会自动回滚。大多数 Oracle 实用程序和工具的正常退出会导致提交当前事务。Oracle 预编译器程序的正常退出不会提交事务，而是依赖 Oracle 数据库回滚当前事务。

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_4010.htm#SQLRF01110

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_4010.htm#SQLRF01110

If you want o choose to do one or the other then you might as well do the one that is the same as doing nothing, and just commit it.

如果你想选择做一个或另一个，那么你不妨做一个和什么都不做一样的，然后就去做。

回答by pero

Well, we must take into account what an SELECT returns in Oracle. There are two modes. By default an SELECT returns data as that data looked in the very moment the SELECT statement started executing (this is default behavior in READ COMMITTED isolation mode, the default transactional mode). So if an UPDATE/INSERT was executed after SELECT was issued that won't be visible in result set.

好吧，我们必须考虑 SELECT 在 Oracle 中返回的内容。有两种模式。默认情况下，SELECT 返回的数据与 SELECT 语句开始执行的那一刻的数据一样（这是 READ COMMITTED 隔离模式下的默认行为，默认事务模式）。因此，如果在 SELECT 发出后执行了 UPDATE/INSERT，则结果集中将不可见。

This can be a problem if you need to compare two result sets (for example debta and credit sides of an general ledger app). For that we have a second mode. In that mode SELECT returns data as it looked at the moment the current transaction began (default behavior in READ ONLY and SERIALIZABLE isolation levels).

如果您需要比较两个结果集（例如总帐应用程序的债务方和信用方），这可能是一个问题。为此，我们有第二种模式。在该模式下，SELECT 返回当前事务开始时的数据（READ ONLY 和 SERIALIZABLE 隔离级别中的默认行为）。

So, at least sometimes it is necessary to execute SELECTs in transaction.

因此，至少有时需要在事务中执行 SELECT。

回答by Andrew

I'd think a Commit would be more efficient; since generally you'd expect most DB transactions to be committed; so you would think the DB optimizes for this case (as opposed to trying to be more efficient for a rollback).

我认为 Commit 会更有效率；因为通常您希望提交大多数数据库事务；所以你会认为数据库针对这种情况进行了优化（而不是试图提高回滚效率）。

回答by cagcowboy

Since you've not done any DML, I suspect there'd be no difference between a COMMIT and ROLLBACK in Oracle. Either way there's nothing to do.

由于您没有做过任何 DML，我怀疑 Oracle 中的 COMMIT 和 ROLLBACK 之间没有区别。无论哪种方式，都无事可做。