Re: Visibility of committed changes

I absolutely did NOT have autocommit on. I know that for sure because the change in one transaction did not become visible in the other transaction until AFTER I explicitly typed COMMIT. Point is, though, the change became visible no matter when the other transaction started.

vlad

"Justin Cave" <jocave_at_yahoo.com> wrote in message news:233b7a65.0301110306.5400679d_at_posting.google.com...
> "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 - 10:42:03 CST