5

https://habr.com/en/company/postgrespro/blog/467437/ gives the following example of a Lost Update:

For example, two transactions are going to increase the amount on the same account by ₽100 (₽ is the currency sign for Russian rouble). The first transaction reads the current value (₽1000) and then the second transaction reads the same value. The first transaction increases the amount (this gives ₽1100) and writes this value. The second transaction acts the same way: it gets the same ₽1100 and writes this value. As a result, the customer lost ₽100

I read this a few times. But I don’t understand how the customer lost P100. Please explain.

Kevin Meredith
  • 517
  • 1
  • 5
  • 13

2 Answers2

14

There are two separate transactions (T1 and T2) that each add ₽100 to the customer's balance.

The intended outcome is:

  • T1 reads the current balance as ₽1000, adds ₽100, and writes ₽1100
  • T2 reads the current balance as ₽1100, adds ₽100, and writes ₽1200

Or the other way around (T2 then T1). The important point is that both increments of ₽100 are applied.

In the example of a lost update, something like the following occurs:

  • T1 reads the current balance as ₽1000
  • T2 reads the current balance as ₽1000
  • T2 adds ₽100 (to the ₽1000 it read), and writes ₽1100
  • T1 adds ₽100 (to the ₽1000 it read), and writes ₽1100

This way the final balance is ₽1100, not ₽1200, so the customer has lost ₽100.

Paul White
  • 83,961
  • 28
  • 402
  • 634
2

Adding to @Paul White's answer: the article you link to is making the point that there is more than one "level" of transaction "isolation" (think "strength"), and it's important to know what you're dealing with in each case.

When I first encountered transactions, I assumed they were all-or-nothing and either worked the way I'd naively expect or the database was broken. But that's not quite true: there are different kinds of inconsistency that may be allowed under the SQL 92 standard

The article gives a decent overview of what those errors look like, so you can decide whether they're a problem for your use case. If a particular error wouldn't be a problem for your application, it might make sense to allow it in exchange for higher database performance.

Paul has answered your direct question - how a lost update works (and it's never allowed under SQL 92). The article also shows how other types of error happen, and the names of the isolation levels that permit those errors.

Jason
  • 121
  • 4