Re: Visibility of committed changes

vlad wrote:
> Hello. I've been reading up on Oracle lately and I find something
> quite strange in the "Concepts" manual. This is what they claim:
>
> -------------------------------------------------------------------
> The changes made by the SQL statement(s) of your transaction become
> permanent and visible to other users only after your transaction has
> been committed. Only other users' transactions that started after
> yours will see the committed changes.
> -------------------------------------------------------------------
>
> Actually, even other users' transactions that started before mine will
> see my changes, as long as the other guys are not running in
> serializable mode. Read committed mode (default isolation level) is
> transaction-set consistent at statement level, not transaction level.
>
> I tested this multiple times, and have found that, as expected,
> committed changes are immediately visible to all transactions running
> in read committed mode. The disturbing thing is that I find their
> claim stated over and over again. Here's another bit from the manual:
>

Hello vlad,
I think the term 'transaction' is not quite correct in the Oracle doc. There is something called consistent read which means a SQL query cannot see rows commited after it started.
Consider a query summing up some accounts taking 30 minutes. If this query saw rows committed during the run it would not get consistent data.
So when a query starts, all relevant data is frozen (using rollback or undo segments) in a consintant state.
If you issue the same query after the first finishes you will see the rows committed during the first run.
If you say

set transaction read only;

you get a consistent view for the duration of this transaction and can issue several queries which all see the same consistent data even if other transactions commit data in the meantime.

Hope that helps,
Lothar

-- 
Lothar Armbrüster       | la_at_oktagramm.de
Hauptstr. 26            | la_at_heptagramm.de
D-65346 Eltville        | lothar.armbruester_at_t-online.de