Oracle 中同时更新的一致性，当 WHERE 子句依赖于旧值时

Question

我一直在阅读有关 Oracle 数据一致性保证和支持的事务隔离级别(例如：https://docs.oracle.com/database/121/CNCPT/consist.htm#CNCPT121)的信息，我觉得我得到了很多高级信息，但我不确定它如何应用于我的特定问题。

我将描述我的用例的简化版本，我正在寻找令人信服的答案，最好是参考，关于我需要如何构建我的交易以获得所需的结果。(请不要在我的问题中过于纠结于语法或数据规范化甚至数据类型;这是一个稻草人 - 所以如果你明白我的意思，请继续关注并发问题。:)

场景(简化)：

许多用户(数以万计)同时在玩在线游戏。玩家都是两支球队的成员，红色或蓝色。每次玩家完成游戏时，我们都希望记录用户、他们的团队隶属关系、时间戳和分数。我们还希望汇总每个团队有史以来的最高分。我们的数据模型如下所示：

// each game is logged in a table that looks kind of like this:
GAMES {
time NUMBER,
userid NUMBER,
team NUMBER,
score NUMBER
}

// high scores are tracked here, assume initial 0-score was inserted at time 0
HIGH_SCORES {
team NUMBER,
highscore NUMBER
}

因此，对于我收到的每份分数报告，我都会执行如下所示的交易

BEGIN
UPDATE HIGH_SCORES set highscore=:1 WHERE team=:2 and :1>highscore;
INSERT into GAMES (time, userid, team, score) VALUES (:1,:2,:3,:4);
COMMIT

我希望保留的不变性是，在任何时间点，每支球队的高分(如HIGH_SCORES表所示)将是我扫描GAMES表并以艰难的方式找到高分时会找到的最高分。

我对READ_COMMITED隔离级别的理解表明，这不会得到我想要的：

已提交读取事务中的写入冲突

在已提交读取事务中，当事务尝试更改由未提交的并发事务(有时称为阻塞事务)更新的行时，会发生冲突写入。已提交读取事务等待阻塞事务结束并释放其行锁。

选项如下：

• 如果阻塞事务回滚，则等待事务将继续更改先前锁定的行，就好像其他事务从未存在过一样。

• 如果阻塞事务提交并释放其锁，则等待事务将继续对新更改的行进行预期更新。

在我看来，如果红队(队 1)的高分为 100，并且两个玩家同时提交更好的分数，那么多线程服务器可能会同时开始两个数据库事务：

# Transaction A
UPDATE HIGHSCORES set highscore=150 where team=1 and 150>highscore;
INSERT into GAMES (time, userid, team, score) VALUES (9999,100,1,150);

和

# Transaction B
UPDATE HIGHSCORES set highscore=125 where team=1 and 125>highscore;
INSERT into GAMES (time, userid, team, score) VALUES (9999,101,1,125);

因此(在READ_COMMITED模式下)，您可以获得以下顺序：(参见上面引用的 Oracle 链接中的表 9-2)

A updates highscore for red team row; oracle locks this row

B still sees the 100 score and so tries to update red team highscore; 
oracle Blocks trasaction B because that row is now locked with a conflicting write

A inserts into the games table;

A commits;

B is unblocked, and completes the update, clobbering the 150 with a 125 and my invariant condition will be broken.

第一个问题——这是对READ_COMMITED的正确理解吗？

然而，我对可序列化的阅读：

Oracle 数据库允许可序列化事务仅在

可序列化事务开始时已提交其他事务对行所做的更改时修改行。当可序列化事务尝试更新或删除在可序列化事务开始后提交的其他事务更改的数据时，数据库将生成错误。

建议在上述情况下，可序列化也不会做正确的事情，唯一的区别是事务 B 会出错，我可以选择回滚或重试。这是可行的，但似乎没有必要的困难。

第二个问题 - 这是对可序列化的正确理解吗？

。如果是这样，我很困惑。这似乎是一件简单而常见的事情。在代码中，我可以通过在每个团队的高分测试和更新周围放置互斥锁来轻松完成此操作。

第三个也是最重要的问题：我如何让Oracle(或任何SQL数据库，就此而言)达到我想要的东西？

更新：进一步阅读表明我可能需要做一些明确的表锁定，如 (https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9015.htm) - 但我不清楚我到底需要什么。哈尔普！？

Answer 1

哇，长问题。 简短的回答是，您只需要READ_COMMITTED。

您不会获得丢失更新，因为事务 B 执行的UPDATE将在事务 A 提交后重新启动。UPDATE将在重新启动时与读取一致，而不是从提交的时间点

开始。也就是说，在您的示例中，事务 B 将在HIGH_SCORES中更新 0 行。

在 Oracle 概念指南的第 9 章中有一个很好的例子，它演示了 Oracle 如何保护应用程序免受更新丢失的影响。

Tom Kyte对Oracle如何以及为什么在内部重启UPDATE语句以实现读取一致性有一个很好的解释，在这里：https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:11504247549852

Answer 2

这是

对READ_COMMITED的正确理解吗？

差一点。您的方案不是您链接到的文档的表 9-2 中显示的方案。您的方案实质上是表 9-4 中的内容。

不同之处在于 9-2 版本(显示丢失的更新)不检查正在更新的值 - 它不会过滤现有工资，即它正在更新的列。9-4 版本正在更新电话号码，但作为更新的一部分查看该列的现有值，并且阻止的更新最终不会更新任何行，因为它会重新读取新更改的值 - 现在与过滤器不匹配。

实质上，当删除锁定上一个锁时，会重新运行被阻止的更新，因此它会重新读取新提交的数据，并使用它来确定现在是否需要更新该行。

正如该文件还说的那样：

锁满足以下重要的数据库要求：

• 一致性

在用户完成之前，其他会话不得更改会话正在查看或更改的数据。

• 正直

数据和结构必须以正确的顺序反映对它们所做的所有更改。

Oracle 数据库通过其锁定机制在事务之间提供数据并发性、一致性和完整性。锁定会自动发生，无需用户操作。

最后两句话意味着您无需担心它，并且通过手动更新两个会话表中的同一行来验证行为相当容易。

在自动锁定下：

DML 锁(也称为数据锁)保证多个用户同时访问的数据的完整性。例如，DML 锁可防止两个客户购买在线书商提供的图书的最后一本。DML 锁可防止同时发生冲突的 DML 或 DDL 操作的破坏性干扰。

在重新启动事务 B 中阻止的更新的情况下，它找不到highscore小于 125 的团队 1 的行。语句执行的数据包括从会话 A 更新的已提交数据，即使该提交发生在 B 首次识别并请求锁定行之后，该行 - 此时 - 确实与其过滤器匹配。因此，它没有要更新的内容，并且会话 A 的更新不会丢失。