Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: keep select in transaction

Re: keep select in transaction

From: <BigBoote66_at_hotmail.com>
Date: 22 Apr 2005 08:55:21 -0700
Message-ID: <1114185321.369671.212190@f14g2000cwb.googlegroups.com>


Unless I misunderstand you, your second statement is incorrect. My proof:

run two instances of sqlplus (for purposes of this example, I will show the prompts as SQL-1> and SQL-2>). Perform the actions in this order:

SQL-1> create table mytest (col1 number); Table created.

SQL-1> insert into mytest values (1);
1 row created.

SQL-1> commit;
Commit complete.

SQL-2> insert into mytest values (2);
1 row created.

SQL-2> select * from mytest;

      COL1


         2
         1

SQL-1> insert into mytest values (3);
1 row created.

SQL-1> commit;
Commit complete.

SQL-2> select * from mytest;

      COL1


         2
         1
         3

The transaction is still open in SQL-2, and the transaction that inserted "3" in SQL-1 was begun after SQL-2's transaction and commited while SQL-2's transaction was still open, and now SQL-2 sees row "3".

You can only get the behavior you describe in your second statement if you execute "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE".

-Steve Received on Fri Apr 22 2005 - 10:55:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US