通过高事务隔离级别防止更新丢失：这是一个常见的误解吗？

发布于 2025-01-11 07:58:51 字数 2018 浏览 0 评论 0原文

我注意到我的应用程序经常将依赖于先前读取操作的值写入数据库。一个常见的例子是用户可以存钱的银行帐户：

void deposit(amount) {
    balance = getAccountBalance()
    setAccountBalance(balance + amount)
}

如果这个方法同时被两个线程/客户端/ATM 调用，我想避免竞争条件，这样帐户所有者就会赔钱：

balance = getAccountBalance()       |
                                    | balance = getAccountBalance()
setAccountBalance(balance + amount) |
                                    | // balance2 = getAccountBalance() // theoretical
                                    | setAccountBalance(balance + amount)
                                    V

我经常读到Repeatable Read或Serialized可以解决这个问题。甚至德语维基百科关于丢失更新的文章也指出了这一点。翻译成英文：

隔离级别 RR（可重复读）经常被提及作为丢失更新问题的解决方案。

这个答案建议可序列化来解决 SELECT 之后 INSERT 的类似问题。

据我理解这个想法 - 当右侧的过程尝试设置帐户余额时，（理论上的）读取操作将不再返回相同的余额。因此不允许写操作。是的 - 如果您阅读这个流行的答案，它实际上听起来非常合适：

在 REPEATABLE READ 下，第二个 SELECT 保证至少显示从第一个 SELECT 未更改返回的行。并发事务可能会在那一分钟内添加新行，但无法删除或更改现有行。

但后来我想知道“它们无法删除或更改”实际上是什么意思。如果您尝试删除/更改它会发生什么？你会得到一个错误吗？或者您的事务会等到第一个事务完成并最终执行其更新吗？这使得一切变得不同。在第二种情况下，你仍然会赔钱。

如果您阅读下面的评论，情况会变得更糟，因为还有其他方法可以满足可重复读取条件。例如快照技术：可以在左侧事务写入其值之前拍摄快照，这样如果稍后在右侧事务中发生第二次读取，则可以提供原始值。例如，请参阅 MySQL 手册：

同一事务内的一致读取会读取第一次读取建立的快照

我得出的结论是，限制事务隔离级别可能是消除竞争条件的错误工具。如果它解决了问题（对于特定的 DBMS），则不是由于可重复读取的定义所致。相反，这是因为满足可重复读取条件的特定实现。例如锁的使用。

所以，对我来说，它看起来像这样：解决这个问题实际上需要的是锁定机制。一些 DBMS 使用锁来实现可重复读取这一事实被利用了。

这个假设正确吗？还是我对事务隔离级别理解错误？

您可能会生气，因为这肯定是有关该主题的第一百万个问题。问题是：示例银行账户场景绝对至关重要。就在那里，应该绝对清楚正在发生的事情，在我看来，似乎有太多误导性和矛盾的信息和误解。

原文

I noticed that my applications often write values to a database that depend on a former read operation. A common example is a bank account where a user could deposit money:

void deposit(amount) {
    balance = getAccountBalance()
    setAccountBalance(balance + amount)
}

I want to avoid a race condition if this method is called by two threads/clients/ATMs simultaneously like this where the account owner would lose money:

balance = getAccountBalance()       |
                                    | balance = getAccountBalance()
setAccountBalance(balance + amount) |
                                    | // balance2 = getAccountBalance() // theoretical
                                    | setAccountBalance(balance + amount)
                                    V

I often read that Repeatable Read or Serializable can solve this problem. Even the german Wikipedia article for Lost Updates states this. Translated to english:

The isolation level RR (Repeatable Read) is often mentioned as a solution to the lost update problem.

This SO answer suggests Serializable for a similar problem with INSERT after SELECT.

As far as I understood the idea - at the time the process on the right side tries to set the account balance, a (theoretical) reading operation wouldn't return the same balance anymore. Therefore the write operation is not allowed. And yes - if you read this popular SO answer, it actually sounds perfectly fitting:

under REPEATABLE READ the second SELECT is guaranteed to display at least the rows that were returned from the first SELECT unchanged. New rows may be added by a concurrent transaction in that one minute, but the existing rows cannot be deleted nor changed.

But then I wondered what "they cannot be deleted nor changed" actually means. What happens if you try to delete/change it anyway? Will you get an error? Or will your transaction wait until the first transaction finished and in the end also perform its update? This makes all the difference. In the second case you will still lose money.

And if you read the comments below it gets even worse, because there are other ways to meet the Repeatable Read conditions. For example a snapshot technology: A snapshot could be taken before the left side transaction writes its value and this allows to provide the original value if a second read occurs later in the right side transaction. See, for instance, the MySQL manual:

Consistent reads within the same transaction read the snapshot established by the first read

I came to the conclusion that restricting the transaction isolation level is probably the wrong tool to get rid of the race condition. If it solves the problem (for a specific DBMS), it's not due to the definition of Repeatable Read. Rather it's because of a specific implementation to fulfil the Repeatable Read conditions. For instance the usage of locks.

So, to me it looks like this: What you actually need to solve this issue is a locking mechanism. The fact that some DBMS use locks to implement Repeatable Read is exploited.

Is this assumption correct? Or do I have a wrong understanding of transaction isolation levels?

You might be annoyed, because this must be the millionth question about the topic. The problem is: The example bank account scenario is absolutely critical. Just there, where it should be absolutely clear what's going on, it seems to me as if there is so much misleading and contradictory information and misconceptions.

分享到QQ

分享到微博