Oracle 事务读取一致性？

Oracle transaction read-consistency?

提问by user319280

I have a problem understanding read consistency in database (Oracle).

我在理解数据库 (Oracle) 中的读取一致性时遇到问题。

Suppose I am manager of a bank . A customer has got a lock (which I don't know) and is doing some updating. Now after he has got a lock I am viewing their account information and trying to do some thing on it. But because of read consistency I will see the data as it existed before the customer got the lock. So will not that affect inputs I am getting and the decisions that I am going to make during that period?

假设我是一家银行的经理。一位客户有一把锁（我不知道）并且正在做一些更新。现在，在他获得锁定后，我正在查看他们的帐户信息并尝试对其进行处理。但是由于读取一致性，我将看到在客户获得锁之前存在的数据。那么这不会影响我在此期间获得的输入以及我将要做出的决定吗？

回答by APC

The point about read consistency is this: suppose the customer rolls back their changes? Or suppose those changes fail because of a constraint violation or some system failure?

关于读取一致性的要点是：假设客户回滚他们的更改？或者假设这些更改由于违反约束或某些系统故障而失败？

Until the customer has successfully committed their changes those changes do not exist. Any decision you might make on the basis of a phantom read or a dirty read would have no more validity than the scenario you describe. Indeed they have less validity, because the changes are incomplete and hence inconsistent. Concrete example: if the customer's changes include making a deposit and making a withdrawal, how valid would your decision be if you had looked at the account when they had made the deposit but not yet made the withdrawal?

在客户成功提交更改之前，这些更改不存在。您可能基于幻读或脏读做出的任何决定都不会比您描述的场景更有效。事实上，它们的有效性较低，因为更改不完整，因此不一致。具体示例：如果客户的更改包括存款和取款，如果您在他们存款但尚未取款时查看了帐户，您的决定有多有效？

Another example: a long running batch process updates the salary of every employee in the organisation. If you run a query against employees' salaries do you really want a report which shows you half the employees with updated salaries and half with their old salaries?

另一个示例：长时间运行的批处理更新组织中每位员工的工资。如果您对员工的薪水进行查询，您真的想要一份报告，其中显示一半的员工有更新的薪水，另一半的员工有旧的薪水吗？

edit

编辑

Read consistency is achieved by using the information in the UNDO tablespace (rollback segments in the older implementation). When a session reads data from a table which is being changed by another session, Oracle retrieves the UNDO information which has been generated by that second session and substitutes it for the changed data in the result set presented to the first session.

读取一致性是通过使用 UNDO 表空间（旧实现中的回滚段）中的信息来实现的。当一个会话从另一个会话正在更改的表中读取数据时，Oracle 会检索由第二个会话生成的 UNDO 信息，并将其替换为呈现给第一个会话的结果集中的更改数据。

If the reading session is a long running query it might fail because due to the notorious ORA-1555: snapshot too old. This means the UNDO extent which contained the information necessary to assemble a read consistent view has been overwritten.

如果阅读会话是一个长时间运行的查询，它可能会失败，因为臭名昭著的ORA-1555: snapshot too old. 这意味着包含组装读取一致视图所需信息的 UNDO 范围已被覆盖。

Locks have nothing to do with read consistency. In Oracle writes don't block reads. The purpose of locks is to prevent other processes from attempting to change rows we are interested in.

锁与读一致性无关。在 Oracle 中，写入不会阻塞读取。锁的目的是防止其他进程尝试更改我们感兴趣的行。

回答by oluies

For systems that have large number of users, where users may "hold" the lock for a long time the Optimistic Offline Lock patternis usually used, i.e. use the version in the UPDATE ... WHERE statement.

对于拥有大量用户的系统，用户可能会长时间“持有”锁，通常使用乐观离线锁模式，即使用 UPDATE ... WHERE 语句中的版本。

You can use a date, version id or something else as the row version. Also the virtual columm ORA_ROWSCN may be used but you need to read up on it first.

您可以使用日期、版本 ID 或其他内容作为行版本。也可以使用虚拟列 ORA_ROWSCN，但您需要先阅读它。

回答by Joe Shawfield

When a record is locked due to changes or an explicit lock statement, an entry is made into the header of that block. This is called an ITL (interested transaction list). When you come along to read that block, your session sees this and knows where to go to get the read consistent copy from the rollback segment.

当记录由于更改或显式锁定语句而被锁定时，会在该块的标题中创建一个条目。这称为 ITL（感兴趣的交易列表）。当您开始读取该块时，您的会话会看到这一点，并知道去哪里从回滚段获取读取一致的副本。