Re: Visibility of committed changes

"vlad" <bulk_at_sfatcu.com> wrote in message news:<YaQT9.520599$GR5.237852_at_rwcrnsc51.ops.asp.att.net>...
> 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.

The Oracle documentation is absolutely correct. How are you doing your testing? My guess is that you've turned on autocommit, so each statement is a transaction.

Justin Cave

>
> 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:
>
> -------------------------------------------------------------------
> A query in a transaction can see the changes made by previous DML
> statements in the same transaction, but cannot see the changes of other
> transactions begun after its own transaction.
> -------------------------------------------------------------------
>
> Another one:
>
> -------------------------------------------------------------------
> All locks acquired by statements within a transaction are held for the
> duration of the transaction, preventing destructive interference
> including dirty reads, lost updates, and destructive DDL operations from
> concurrent transactions. The changes made by the SQL statements of one
> transaction become visible only to other transactions that start after
> the first transaction is committed.
> -------------------------------------------------------------------
>
> So, what am I missing? Are my senses completely deceiving me? Or can
> Oracle documentation be so completely misguided? I'm getting quite
> frustrated.
>
> vlad
> --
Received on Sat Jan 11 2003 - 05:06:18 CST