Thread: Different results between PostgreSQL and Oracle for "for update" statement

Andy Fan <zhihui.fan1213@gmail.com> writes:
> create table su (a int, b int);
> insert into su values(1, 1);

> - session 1:
> begin;
> update su set b = 2 where b = 1;

> - sess 2:
> select * from su where a in (select a from su where b = 1) for update;

This'd probably work the way you expect if there were "for update"
in the sub-select as well.  As is, the sub-select will happily
return "1".

            regards, tom lane

On 11/20/20 9:57 AM, Andy Fan wrote:
> Thank you for your attention. Your suggestion would fix the issue.  However
> The difference will cause some risks when users move their application 
> from Oracle
> to PostgreSQL. So I'd like to think which behavior is more reasonable.

I think PostgreSQL's behavior is more reasonable since it only locks the 
rows it claims to lock and no extra rows. This makes the code easy to 
reason about. And PostgreSQL does not re-evaluate sub queries after 
grabbing the lock which while it might be surprising to some people is 
also a quite nice consistent behavior in practice as long as you are 
aware of it.

I do not see why these two scenarios should behave differently (which I 
think they would with your proposed patch):

== Scenario 1

create table su (a int, b int);
insert into su values(1, 1);

- session 1:
begin;
update su set b = 2 where b = 1;

- sess 2:
select * from su where a in (select a from su where b = 1) for update;

- sess 1:
commit;

== Scenario 2

create table su (a int, b int);
insert into su values(1, 1);

create table su2 (a int, b int);
insert into su2 values(1, 1);

- session 1:
begin;
update su set b = 2 where b = 1;
update su2 set b = 2 where b = 1;

- sess 2:
select * from su where a in (select a from su2 where b = 1) for update;

- sess 1:
commit;

Andreas

On 11/20/20 3:25 PM, Andy Fan wrote:> On Fri, Nov 20, 2020 at 9:37 PM 
Andreas Karlsson <andreas@proxel.se
> <mailto:andreas@proxel.se>> wrote:
> 
>     On 11/20/20 9:57 AM, Andy Fan wrote:
>      > Thank you for your attention. Your suggestion would fix the
>     issue.  However
>      > The difference will cause some risks when users move their
>     application
>      > from Oracle
>      > to PostgreSQL. So I'd like to think which behavior is more
>     reasonable.
> 
>     I think PostgreSQL's behavior is more reasonable since it only locks
>     the
>     rows it claims to lock and no extra rows. This makes the code easy to
>     reason about. And PostgreSQL does not re-evaluate sub queries after
>     grabbing the lock which while it might be surprising to some people is
>     also a quite nice consistent behavior in practice as long as you are
>     aware of it.
> 
> I admit my way is bad after reading your below question, but I
> would not think *it might be surprising to some people* is a good signal
> for a design.  Would you think "re-evaluate the quals" after grabbing the
> lock should be a good idea? And do you know if any other database uses
> the postgres's way or Oracle's way?   I just heard Oracle might do the
> re-check just some minutes before reading your reply and I also found
> Oracle doesn't lock the extra rows per my test.

Re-evaluating the sub queries is probably a very bad idea in practice 
since a sub query can have side effects, side effects which could really 
mess up some poor developer's database if they are unaware of it. The 
tradeoff PostgreSQL has made is not perfect but on top of my head I 
cannot think of anything less bad.

I am sadly not familiar enough with Oracle or have access to any Oracle 
license so I cannot comment on how Oracle have implemented their behvior 
or what tradeoffs they have made.

Andreas

On Fri, Nov 20, 2020 at 3:04 PM Andreas Karlsson <andreas@proxel.se> wrote:
> I am sadly not familiar enough with Oracle or have access to any Oracle
> license so I cannot comment on how Oracle have implemented their behvior
> or what tradeoffs they have made.

I bet that Oracle does a statement-level rollback for READ COMMITTED
mode's conflict handling. I'm not sure if this means that it locks
multiple rows or not. I think that it only uses one snapshot, which
isn't quite what we do in the Postgres case. It's really complicated
in both systems.

Andy is right to say that it looks like Postgres is using 2 different
snapshots for the same query. That's *kind of* what happens here.
Technically the executor doesn't take a new snapshot, but it does the
moral equivalent. See the EvalPlanQual() section of the executor
README.

FWIW this area is something that isn't very well standardized, despite
what you may hear. For example, InnoDBs REPEATABLE READ doesn't even
use the transaction snapshot for UPDATEs and DELETEs at all:

https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

Worst of all, you can update rows that were not visible to the
transaction snapshot, thus rendering them visible (see the "Note" box
in the documentation for an example of this). InnoDB won't throw a
serialization error at any isolation level. So it could be worse!

--
Peter Geoghegan

On Sat, Nov 21, 2020 at 12:58 AM Andy Fan <zhihui.fan1213@gmail.com> wrote:
> I don't mean we need to be the same as Oracle, but to support a
> customer who comes from Oracle, it would be good to know the
> difference.

Actually, it is documented here:
https://www.postgresql.org/docs/devel/transaction-iso.html

The description starts with: "UPDATE, DELETE, SELECT FOR UPDATE, and
SELECT FOR SHARE commands behave the same as SELECT in terms of
searching for target rows...".

I imagine that the number of application developers that are aware of
this specific aspect of transaction isolation in PostgreSQL (READ
COMMITTED conflict handling/EvalPlanQual()) is extremely small. In
practice it doesn't come up that often. Though Postgres hackers tend
to think about it a lot because it is hard to maintain.

I'm not saying that that's good or bad. Just that that has been my experience.

I am sure that some application developers really do understand the
single most important thing about READ COMMITTED mode's behavior: each
new command gets its own MVCC snapshot. But I believe that Oracle is
no different. So in practice application developers probably don't
notice any difference between READ COMMITTED mode in practically all
cases. (Again, just my opinion.)

-- 
Peter Geoghegan