From: yrshen@stu.xidian.edu.cn
To: Greg Sabino Mullane <htamfids@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Re: BUG #18750: Inappropriate update when it is blocked in RC
Date: Wed, 25 Dec 2024 13:32:08 +0800 (GMT+08:00)
Message-ID: <e8839c8.226.193fc4d0c40.Coremail.yrshen@stu.xidian.edu.cn> (raw)
In-Reply-To: <CAKAnmmKrudhHmcfMVjh5VRojDjJM4Yz=ndU=e_5qPxPEa5fX5A@mail.gmail.com>
References: <18750-e0a79ec68de35cec@postgresql.org>
<CAKAnmmKrudhHmcfMVjh5VRojDjJM4Yz=ndU=e_5qPxPEa5fX5A@mail.gmail.com>
I'm so sorry that I forgot to provide you with the value of initial table t. The table is created as follows:
/* init */ CREATE TABLE t(a INT, b INT);
/* init */ INSERT INTO t VALUES (null, 1), (1, 1);
2024-12-25 01:24:07 "Greg Sabino Mullane" <htamfids@gmail.com> 写道：
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: yrshen@stu.xidian.edu.cn, htamfids@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Re: BUG #18750: Inappropriate update when it is blocked in RC
In-Reply-To: <e8839c8.226.193fc4d0c40.Coremail.yrshen@stu.xidian.edu.cn>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox