Re: keep select in transaction

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: Tue, 12 Apr 2005 22:02:00 +0000 (UTC)
Message-ID: <d3hggo$itd$1@klatschtante.init7.net>

>> 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/

Received on Tue Apr 12 2005 - 17:02:00 CDT