Thank you very much for your explanation. So, under the READ COMMITTED of PostgreSQL, the UPDATE statement of Tx2 will only re-evaluate and update the initial row (null, 1), which it initially wanted to update, if the (null, 1) is then changed to (1, 3) by Tx1. Therefore, the expected result of the case is [(1,3), (1,2)] according to the rule of PostgreSQL instead of [(1, 2), (1, 2)], am I right?
-----------------------------------------------------------------------------
/* init */ CREATE TABLE t(a INT, b INT);
/* init */ INSERT INTO t VALUES (null, 1), (1, 1);
/* Tx1 */ BEGIN;
/* Tx1 */ SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
/* Tx2 */ BEGIN;
/* Tx2 */ SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
/* Tx1 */ UPDATE t SET b=3;
/* Tx2 */ UPDATE t SET b=2 WHERE a IS NOT NULL;
/* Tx1 */ UPDATE t SET a=1;
/* Tx1 */ COMMIT;
/* Tx2 */ COMMIT;
/* Tx1 */ SELECT * FROM t; -- actual: [(1, 3), (1 ,2)], expected: [(1, 2), (1, 2)]
-----------------------------------------------------------------------------
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
