Different results between PostgreSQL and Oracle for "for update" statement
От | Andy Fan |
---|---|
Тема | Different results between PostgreSQL and Oracle for "for update" statement |
Дата | |
Msg-id | CAKU4AWq3rfjZQJBSvmbEsRp6ENArgMmzW4MHVDQ9UTic6p2pcA@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Different results between PostgreSQL and Oracle for "for update" statement
|
Список | 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.
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
--------------------------------------------------------------------------------
-----------------------------------------------------------------------------
--------------------------------------------------------------------------------
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
В списке pgsql-hackers по дате отправления: