On Fri, Nov 20, 2020 at 3:04 PM Andreas Karlsson <andr...@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
