Search code examples
postgresqlisolation-levellost-update

How can postgresql have a "lost update" in Read Committed isolation level?

On page 52 of PostgreSQL 14 Internals, Egor Rogov indicates that "lost updates" can occur with the "Read Committed" isolation level. There is also a sentence that reads:

But in some cases, there is a risk of losing changes at the Read Committed level.

I would like to understand what one of the (special?) cases is. Under what circumstance does this anomaly happen?

Solution

  • Short answer is there's no such case.

    The special case the author has in mind isn't one where Postgres can lose an update, it's where the larger system Postgres is a part of, appears to lose an update. The case in question requires users in concurrent transactions to re-use values read previously, disregarding the fact that between the initial read and its re-use, someone's commit might have rendered the value outdated.

    Thing is, if you want to do things that way, you're supposed to use repeatable read mode and/or select for update (or another explicit lock), locking the rows you plan to update.

    In the PDF version the quote you refer to is on page 44. Page 52 describes said case of lost update:

    The application reads and registers (outside of the database) the current balance of Alice’s account:

     => BEGIN;
 => SELECT amount FROM accounts WHERE id = 1;
 amount
 −−−−−−−−
 800.00
 (1 row)

    Meanwhile, the other transaction does the same:

     => BEGIN;
 => SELECT amount FROM accounts WHERE id = 1;
 amount
 −−−−−−−−
 800.00
 (1 row)

    The first transaction increases the previously registered value by $800 and commits this change:

    => UPDATE accounts SET amount = 800.00 + 100 WHERE id = 1
RETURNING amount;
amount
−−−−−−−−
900.00
(1 row)
UPDATE 1
=> COMMIT;

    The second transaction does the same:

    => UPDATE accounts SET amount = 800.00 + 100 WHERE id = 1
RETURNING amount;
amount
−−−−−−−−
900.00
(1 row)
UPDATE 1

    Unfortunately, Alice has lost $100. The database system does not know that the registered value of $800 is somehow related to accounts.amount, so it cannot prevent the lost update anomaly. At the Read Committed isolation level, this code is incorrect.