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

On Sun, Nov 22, 2020 at 5:56 AM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:

> On Sat, Nov 21, 2020 at 12:58 AM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)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.

Totally agree with that.

> Though Postgres hackers tend to think about it a lot because it is hard to
> maintain.
>

Hackers may care about this if they run into a real user case :)

> 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
>

--
Best Regards
Andy Fan