Thank all of you for your great insight! On Sat, Nov 21, 2020 at 9:04 AM Peter Geoghegan <p...@bowt.ie> wrote:
> 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'd agree with you about this point, this difference can cause more different behavior between Postgres & Oracle (not just select .. for update). create table dml(a int, b int); insert into dml values(1, 1), (2,2); -- session 1: begin; delete from dml where a in (select min(a) from dml); --session 2: delete from dml where a in (select min(a) from dml); -- session 1: commit; In Oracle: 1 row deleted in sess 2. In PG: 0 rows are deleted. > I'm not sure if this means that it locks multiple rows or not. This is something not really exists and you can ignore this part:) About the statement level rollback, Another difference is related. create table t (a int primary key, b int); begin; insert into t values(1,1); insert into t values(1, 1); commit; Oracle : t has 1 row, PG: t has 0 row (since the whole transaction is aborted). 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. -- Best Regards Andy Fan
