From: | Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Different results between PostgreSQL and Oracle for "for update" statement |
Date: | 2020-11-19 13:11:48 |
Message-ID: | CAKU4AWq3rfjZQJBSvmbEsRp6ENArgMmzW4MHVDQ9UTic6p2pcA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Kapila | 2020-11-19 13:24:23 | Re: [HACKERS] logical decoding of two-phase transactions |
Previous Message | Daniel Gustafsson | 2020-11-19 13:05:35 | Re: scram-sha-256 broken with FIPS and OpenSSL 1.0.2 |