We can reproduce this difference with the following steps. 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; Then session 2 can get the result. PostgreSQL: a | b ---+--- 1 | 2 (1 row) Oracle: It gets 0 rows. Oracle's plan is pretty similar to Postgres. PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2828511618 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 52 | 4 (0)| 00:00:01 | | 1 | FOR UPDATE | | | | | | | 2 | BUFFER SORT | | | | | | |* 3 | HASH JOIN SEMI | | 1 | 52 | 4 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL| SU | 1 | 26 | 2 (0)| 00:00:01 | |* 5 | TABLE ACCESS FULL| SU | 1 | 26 | 2 (0)| 00:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- ----------------------------------------------------------------------------- Any thoughts on who is wrong? -- Best Regards Andy Fan
