Oracle - 事务、回滚段和 undo_retention 参数如何工作？

Oracle - How does transaction, rollback segment and the undo_retention parameter work?

提问by Manur

I'm no DBA, and I'm having a bit of a hard time understanding Oracle's transaction management process.

我不是 DBA，我很难理解 Oracle 的事务管理过程。

From what I understood by reading some reliable-looking pages on the Internet (most notably this AskTom note-- but don't bother with the comments), when a transaction is commited, the new data is notreported on the actual data block yet, but stays logged on the rollback segment. When someone issues a SELECT on the data, or when UNDO_RETENTION seconds have passed -- whichever of these two events happens first --, the new data is then (and only then) written on the data blocks.

根据我在互联网上阅读一些看起来可靠的页面（最值得注意的是这个AskTom 注释——但不要理会评论）的理解，当一个事务被提交时，新数据还没有报告在实际数据块上，但在回滚段保持登录状态。当有人对数据发出 SELECT 或当 UNDO_RETENTION 秒过去时——无论这两个事件中的哪一个先发生——，新数据然后（并且仅在那时）写入数据块。

But someone in our company, supposedly in the know, recently told me the opposite : according to him, when a transaction is commited, the new data isimmediately written on the data blocks, and the rollback segment/undo tablespace keeps the old data for a duration of UNDO_RETENTION seconds. This old data stays available during this time for access by queries launched on SCNs prior to the transaction.

但是我们公司的一个应该是知情人士最近跟我说相反的：据他说，当一个事务被提交时，新的数据会立即写入数据块，回滚段/undo表空间保留旧的数据UNDO_RETENTION 秒的持续时间。在此期间，这些旧数据保持可用，以供在交易之前在 SCN 上启动的查询访问。

So, what really happens inside Oracle, and can you provide references to back up your reply ?

那么，Oracle 内部究竟发生了什么，您能否提供参考以支持您的回复？

We're using Oracle 9.2.0.8.

我们使用的是 Oracle 9.2.0.8。

Thanks in advance.

提前致谢。

回答by Dave Costa

Lots to cover here! The person in your company is essentially right, except that the changes are written to the data block in memoryas they are made, even before the commit; and they are written out to disk entirely independently of when you commit (possibly before, possibly after, never as part of the commit operation).

这里有很多内容！贵公司的人基本上是对的，除了更改会在进行时写入内存中的数据块，甚至在提交之前；并且它们完全独立于您提交的时间写出到磁盘（可能在提交之前，也可能在提交之后，从不作为提交操作的一部分）。

1) UNDO_RETENTION has nothing to do with when your changes are written to the data block, either in memory or on disk. UNDO_RETENTION controls how long the data necessary to undo your change persists AFTER you commit the change. The purpose being that other queries or serializable transactions that started before your commit may still want that data. Reference: http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/undo.htm#sthref1477

1) UNDO_RETENTION 与何时将更改写入数据块（内存或磁盘）无关。UNDO_RETENTION 控制在您提交更改后撤消更改所需的数据保留多长时间。目的是在您提交之前启动的其他查询或可序列化事务可能仍然需要该数据。参考：http: //download.oracle.com/docs/cd/B19306_01/server.102/b14231/undo.htm#sthref1477

2) When you do an update, the data blocks in memory are modified. They may or may not be written out to disk (even before you commit, I believe); this is done by a background process. Also, redo information is written to the redo log buffer. Undo is generated and stored in an undo segment.

2) 进行更新时，内存中的数据块被修改。它们可能会或可能不会写出到磁盘（甚至在你提交之前，我相信）；这是由后台进程完成的。此外，重做信息被写入重做日志缓冲区。撤消在撤消段中生成并存储。

3) When you commit, Oracle makes sure that your redo information is written to disk, and marks the undo data as committed. But it does not write the changed data blocks in memory to disk, nor does it go back and mark each block as committed. This is to make the commit as quick as possible. Reference: http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/transact.htm#sthref628

3) 当您提交时，Oracle 确保您的重做信息写入磁盘，并将撤消数据标记为已提交。但它不会将内存中更改的数据块写入磁盘，也不会返回并将每个块标记为已提交。这是为了使提交尽可能快。参考：http: //download.oracle.com/docs/cd/B19306_01/server.102/b14220/transact.htm#sthref628

4) The data blocks in memory will be marked as committed either when they are written out to disk by the background process, or the next time they are used (by a SELECT or any other operation). That's what the AskTom note is discussing. This is not about whether your changes to data are written to the block; it is about whether they are marked as committed in the block itself.

4) 内存中的数据块将在后台进程将它们写出到磁盘时或下次使用时（通过 SELECT 或任何其他操作）标记为已提交。这就是 AskTom 笔记正在讨论的内容。这与您对数据的更改是否写入块无关；这是关于它们是否在块本身中被标记为已提交。

回答by Matthew Watson

My understanding is (basically) the later, This linkhas the details.

我的理解（基本上）是后者，此链接有详细信息。

The data blocks don't have to be written, just updated in the buffer, they may or may not be written out to disk. The redo must be written to disk before the commit can proceed though.

数据块不必写入，只需在缓冲区中更新，它们可能会或可能不会写出到磁盘。但是，必须先将重做写入磁盘，然后才能继续提交。

回答by rics

I also vote for the second version based on
this link(which is Oracle 10.2, but I think it still applies to 9.2 as well).

我也根据
这个链接投票给第二个版本（这是 Oracle 10.2，但我认为它仍然适用于 9.2）。

It says: "After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. However, for consistent read purposes, long-running queries may require this old undo information for producing older images of data blocks."

它说：“在提交事务后，回滚或事务恢复目的不再需要撤消数据。但是，为了一致的读取目的，长时间运行的查询可能需要这些旧的撤消信息来生成旧的数据块映像。”

and

和

"When automatic undo management is enabled, there is always a current undo retention period, which is the minimum amount of time that Oracle Database attempts to retain old undo information before overwriting it."

“启用自动撤消管理后，始终存在当前撤消保留期，这是 Oracle 数据库在覆盖旧撤消信息之前尝试保留旧撤消信息的最短时间。”