Re: Multi-Versions and Vacuum -- cf Oracle & Vacuum alt

Anthony Berglas wrote:
>
> A combined response to several posts. I am not advocating Oracle in
> general, just noting differences and places where Postgres might benefit.
>
> ORACLE MVC
>
> Oracle definitely has MVC. By default it is in Read Committed mode. But
> you can still get record locks in both Postgresql and Oracle if you Select
> FOR UPDATE, which you must do in Read Committed mode to produce correct
> transactions.
>
> ORACLE LOCKING
>
> My note about Oracle is that in its Read Committed mode transactions are
> safer without being Serializable. See http://www.SimpleORM.org/DBNotes.html
> for details. The difference is subtle but it is is important because
> otherwise report may not be consistent. And I do not think that there is
> any down side. So I commend the approach to the Postgres community.

I just tried this in Oracle 8.0.5:

CREATE TABLE employees (key integer, value varchar(32);

Session #1
---------------
SQL> insert into employees values (1, 'Mike');
SQL> insert into employees values (2, 'Joe');
SQL> commit;
SQL> insert into foo values (1) <-- To ensure a new xact
SQL> select * from employees;

1 Mike
2 Joe

Session #2:
---------------
SQL> select * from employees;

1 Mike
2 Joe

SQL> update employees set value = 'Tom' where key = 1;
SQL> insert into employees values (3, 'Jim');
SQL> delete from employees where key = 2;
SQL> select * from employees;

1 Tom
3 Jim

Session #1:
---------------
SQL> select * from employees;

1 Mike
2 Joe

Session #2:
---------------
SQL> commit;

Session #1:
---------------
SQL> select * from employees;

1 Tom
3 Jim

So when you say:

"By memory, Oracle has similar behaviour in Read Committed mode except
that the PostCommitSelect would NOT show the changes made by session
two, and thus be more serializable."

it doesn't match the behavior with Oracle 8.0.5. Tom and Jim are phantom
reads, as expected, in Read Committed mode. Perhaps this has changed in
version 9?

Mike Mascari
mascarm(at)mascari(dot)com