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

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