postgresqlisolationread-committed

Postgres read commited doesn't re read updated row


Good day. I was playing with READ COMMITTED isolation level in postgres and found strange behavior which doesn't follow official documentation. Let's say I have a table account(id int,name text,amount int) and two rows.

test> select * from account;                       
-[ RECORD 1 ]-------------------------
id     | 1
name   | Bob
amount | 800
-[ RECORD 2 ]-------------------------
id     | 2
name   | Bob
amount | 200

Now I start two READ COMMITTED transactions . First one executes the following query

UPDATE account set amount = 100 where id = 2; -- 1

And then second one executes this query

 UPDATE account set amount = amount+50 --2
  where name in 
      (select DISTINCT name from account group by
      name having sum(amount)>=1000); 

Now it is locked because first transaction is not committed yet. So the second transaction wants to add 50 to each account whose total amount is bigger or equals to 1000. As Bob has two accounts (800+200) then it should add 50 to each account. However , now first transaction was committed COMMIT; --1 and now Bob has 900 at total and according to Documentation Read committed transaction will

The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition. If so, the second updater proceeds with its operation using the updated version of the row

As far as I understand , second transaction will re-evaluate where condition and skip Bob's accounts. However when second transaction was committed the final rows look like this

id     | 1                                                                                            │
name   | Bob                                                                                          │
amount | 850                                                                                          │
-[ RECORD 3 ]-------------------------                                                                │
id     | 2                                                                                            │
name   | Bob                                                                                          │
amount | 150 

which means that the second transaction didn't re-evaluate search condition and applied update to rows even if they are not matched by condition. Why does it happen . Did I miss something in the documentation ?


Solution

  • The UPDATE in the first transaction blocks the UPDATE in the second query, but not the subselect in that query. The subselect is already done, the sum has been determined to be 1000, so the UPDATE is executed and that is blocked. The subquery is not re-evaluated when the lock is gone.

    The passage you quote from the documentation is about SELECT ... FOR UPDATE (or FOR SHARE), which you don't use. It could not be used in your example, because it doesn't make sense in a query that uses aggregate functions or grouping.