Lost Update Understanding

Question

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.

Answer 1

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.

Answer 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.

Answer 3

Technically not an answer to the question, but I want to demo this anomaly descried in the question using Postgres with the isolation level Read Committed:

db=# create table customer (name varchar(10), balance int);
CREATE TABLE
db=# insert into customer values ('foo', 0);
INSERT 0 1
db=# select * from customer;
 name | balance
------+---------
 foo  |       0
(1 row)

Now execute the following queries using 2 transactions (2 psql clients):

tx1: begin isolation level read committed;
tx2: begin isolation level read committed;
tx1: update customer set balance = (select balance from customer where name='foo')+1 where name='foo';
tx2: update customer set balance = (select balance from customer where name='foo')+1 where name='foo';

# here tx2 will BLOCK as we are updating the same row from 2 transactions
# Postgres locks it

tx1: commit; 

# tx2 no longer blocks because tx1 commits and the lock has been released

tx2: commit;
tx1 & tx2: select * from customer;
 name | balance
------+---------
 foo  |       1
(1 row)

---

NOTE

If you change the "update" query to update customer set balance = balance + 1 where name='foo';, then you won't reproduce it as when tx2 reads the balance, it reads the committed data, i.e., 1, so ultimately, balance will be 2 (the correct result).

If you try the above example with other isolation levels, you cannot reproduce it either as Postgres will abort tx2:

ERROR:  could not serialize access due to concurrent update