Re: keep select in transaction

> > > 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 Received on Fri Apr 15 2005 - 05:15:09 CDT