Re: BUG #18750: Inappropriate update when it is blocked in RC

From: Greg Sabino Mullane <htamfids(at)gmail(dot)com>
To: yrshen(at)stu(dot)xidian(dot)edu(dot)cn, pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #18750: Inappropriate update when it is blocked in RC
Date: 2024-12-24 17:24:07
Message-ID: CAKAnmmKrudhHmcfMVjh5VRojDjJM4Yz=ndU=e_5qPxPEa5fX5A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

It will re-evaluate the rows it is already slated to update, it is not
going to re-run the whole query and get a fresh list of rows to update.
Here's two other examples. Uppercase is messages returned to psql from the
server.

create table t (id int);
insert into t values (1),(2);

/* tx1 */ begin; -- isolation level does not matter
/* tx1 */ select * from t for update; -- lightweight lock on all rows
/* tx2 */ begin transaction isolation level read committed;
/* tx2 */ update t set id=999 where id = 2; -- hangs, waiting for the lock
/* tx1 */ update t set id = 4; -- the where clause is now no longer true
for that row
/* tx1 */ UPDATE 2 -- server says we have updated two rows
/* tx1 */ commit;
/* tx2 */ UPDATE 0 -- server says that row no longer meets the WHERE
clause, so no update
/* tx2 */ update t set id=999 where id = 2;
/* tx2 */ UPDATE 0 -- everything is a 4
/* tx2 */ update t set id=999 where id = 4;
/* tx2 */ UPDATE 2

truncate table t;
insert into t values (1),(2);

/* tx1 */ begin; select * from t for update;
/* tx2 */ begin transaction isolation level read committed;
/* tx2 */ update t set id=999 where id = 2; -- hangs, waiting for the lock
/* tx1 */ update t set id = 2; -- now have two rows that match the where
clause
/* tx1 */ UPDATE 2 -- server says we have updated two rows
/* tx1 */ commit;
/* tx2 */ UPDATE 1 -- server verifies our original row is still valid, but
does not update the "new" 2
/* tx2 */ update t set id=999 where id = 2; -- fresh look at all the rows
/* tx2 */ UPDATE 1 -- we have updated the "new" 2
/* tx2 */ update t set id=999 where id = 2;
/* tx2 */ UPDATE 0 -- nothing left

If you set the second transaction to "repeatable read", you will find that
the above scenario will result in a "could not serialize access due to
concurrent update" error, which, in my opinion, is a more sane result. One
of the many reasons I tend to avoid "read committed".

Cheers,
Greg

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message yrshen 2024-12-25 05:31:02 Re: Re: BUG #18750: Inappropriate update when it is blocked in RC
Previous Message Pantelis Theodosiou 2024-12-24 12:37:10 Re: BUG #18750: Inappropriate update when it is blocked in RC