Re: keep select in transaction

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 14 Apr 2005 03:16:40 -0700
Message-ID: <1113473800.627482.319120@z14g2000cwz.googlegroups.com>

Rene Nyffenegger wrote:
> >> as far as i remember Oracle works in 'read commited' mode per
default.
> >> That means that you will 'see' rows inserted AND commited in other

> >> transactions within your transaction. What you need in this case
is to
> >> set the transaction isolation level to 'serializable'.
> >>
> >> And: Please don't cross post.
> >
> > 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.

Regards
/Rauf Received on Thu Apr 14 2005 - 05:16:40 CDT