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