Re: keep select in transaction

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 15 Apr 2005 05:17:20 -0700
Message-ID: <1113567440.497695.273530@o13g2000cwo.googlegroups.com>

Rene Nyffenegger wrote:
> > > > By default it is impossible, in Oracle, to see uncommitted
data.
> > > >
> > > > By default it is impossible, in Oracle, to see records
committed
> > after
> > > > your transaction has begun.
> > >
> > > The second statement of yours is not correct, if you meant it
like I
> > > understood it.
> > >
> > > Consider:
> > >
> > > ----------
> > > Session 1:
> > > ==========
> > >
> > > RENE> create table test_1 (a number);
> > >
> > > Table created.
> > >
> > > RENE> insert into test_1 values(42);
> > >
> > > 1 row created.
> > >
> > > RENE> commit;
> > >
> > > Commit complete.
> > >
> > > -------------------
> > > Then, in session 2:
> > > ===================
> > > RENE> begin
> > > 2 for r in (select a from test_1) loop
> > > 3 dbms_output.put_line(r.a);
> > > 4 end loop;
> > > 5 dbms_output.put_line('----------- going to sleep for a
> > while...');
> > > 6 dbms_lock.sleep(20);
> > > 7 for r in (select a from test_1) loop
> > > 8 dbms_output.put_line(r.a);
> > > 9 end loop;
> > > 10 end;
> > > 11 /
> > >
> > > -----------------------------
> > > Now, going back to session 1,
> > > and typing really fast:
> > > =============================
> > > RENE> insert into test_1 values(999);
> > >
> > > 1 row created.
> > >
> > > RENE> commit;
> > >
> > > Commit complete.
> > >
> > > ------------------------------
> > > Waiting for session 2 to
> > > complete:
> > > ==============================
> > > 42
> > > ----------- going to sleep for a while...
> > > 42
> > > 999
> > >
> > >
> > > Clearly, session 2 saw the record with 999 that was commited
> > > after the transaction of session 2 begun.
> > >
> > >
> > > Rene
> > >
> > > --
> > > Rene Nyffenegger
> > > http://www.adp-gmbh.ch/

> >
> > Your test is not valid as you have written it. All records that
were
> > committed before a cursor is opened are visible in a cursor. Which
> > means that any record committed before openning second cursor in
your
> > example is visble. That is what you see.

> >

> > The correct test would be e.g.

> >

> > declare
> > cursor cur is select ...;
> > t blah;
> > begin
> > open cur;
> > dbms_lock.sleep(10);
> > loop
> > fetch cur into t;
> > if (cur%notfound) then
> > exit;
> > end if;
> > dbms_output.put_line(t);
> > end loop;
> > close cur;
> > end;
> > /

> >
> > Insert a record from the other session and commit after starting
the
> > plsql block but before it is finished.
> > In this instance... you ONLY see committed records when the cursor
is
> > opened. Any record committed after the cursor is opened will not be
> > visible until after the cursor is closed and reopened.
>
> Huh? The point I am arguing over is "By default it is impossible, in Oracle,

> to see records committed after your transaction has begun."
>
> I start an (implicit) transaction in session 2. Session 2 selects
> all records from a table which returns one record (42).
> Session 2 does NOT end the transaction.
>
> While session 2 waits 20 seconds, session 1 inserts a record into
> the table and COMMITS it. According to "By default it is impossible,

in

> Oracle, to see records committed after your transaction has begun.",
> session 2 should not see the newly inserted record. Yet it does.
>
> Your point, however, is that as soon a cursor is opened, it doesn't

matter
> what other transactions do, especially, whether or not the commit changed
> data (=> read consistency). I agree with you over that, but, as pointed

> out, that was not my point.
>
> Hope this clears things up
> Rene

My understanding of the word "Transaction" in Daniel's post was a single DML which involves either select, insert, update or delete. You on the other hand read "Transaction" as a single PLSQL call. Two completely different things as you may open/close/commit/rollback one to many DML's in a single PLSQL call.

That is the confusion as I see it.

Regards
/Rauf Received on Fri Apr 15 2005 - 07:17:20 CDT