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

On Fri, Nov 20, 2020 at 3:04 PM Andreas Karlsson <andreas(at)proxel(dot)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