Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: keep select in transaction
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