?
Joachim Seekopp, June 13, 2005 - 2:58 pm UTC
SQL> set transaction isolation level serializable;
Transaktion wurde gesetzt.
SQL> select * from t where x=55;
select * from t where x=55
*
FEHLER in Zeile 1:
ORA-00942: table or view does not exist
SQL> declare procedure t is
2 pragma autonomous_transaction;
3 begin
4 execute immediate 'create table t(x integer)';
5 end;
6 begin t; end;
7 /
PL/SQL-Prozedur wurde erfolgreich abgeschlossen.
SQL> select * from t where x=55;
Es wurden keine Zeilen ausgewõhlt
SQL>
June 13, 2005 - 8:55 pm UTC
umm, what is your point?
every time you queried the table T in that transaction, it returned the same result.
I should add - you can see YOUR modifications, that is a subtle point.
You can see YOUR changes.
in serializable, it is as if you are the only one in the database.
is that what you were trying to say?
point
Joachim Seekopp, June 14, 2005 - 3:13 am UTC
sorry, I thought I was in the same transaction when I queried
the second time, however I got two different results ?
ok, make it like that
SQL> set transaction isolation level serializable;
Transaktion wurde gesetzt.
SQL> select * from t where x=55;
select * from t where x=55
*
FEHLER in Zeile 1:
ORA-00942: table or view does not exist
SQL> $sqlplus ... /* same user */
SQL*Plus: Release 9.2.0.1.0 - Production on Di Jun 14 09:05:11 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Kennwort eingeben:
Verbunden mit:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
SQL> create table t(x integer);
Tabelle wurde angelegt.
SQL> exit
Verbindung zu Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production beendet
SQL> /
Es wurden keine Zeilen ausgewõhlt
so the same query resulted - in the same transaction -
at first in ORA-00942, then, without doing anything in that
transaction in "no rows selected."
This seems to contradict the serializable isolation level to me.
What I am missing or misunderstanding?
PS. tried to play around with transaction IDs
trying to prove I am in the same transaction, however
SQL> exec dbms_output.put_line(DBMS_TRANSACTION.LOCAL_TRANSACTION_ID(true))
13.25.8445
PL/SQL-Prozedur wurde erfolgreich abgeschlossen.
SQL> set transaction isolation level serializable;
set transaction isolation level serializable
*
FEHLER in Zeile 1:
ORA-00600: internal error code, arguments: [setiso02], [5635], [], [], [], [],
[], []
ouch - don't want to touch things which do not work
June 14, 2005 - 9:52 am UTC
why? the first query didn't really "even exist", it failed, it wasn't a valid query, it did not run.
I see nothing contradictory here. Suppose you typed in:
SQL> this is garbage -- table = t;
<failed>
create table in another session
SQL> select * from t;
no rows
so what? the first statement wasn't "executed, run, doesn't exist, was meaningless, was an error"
Well, you are just getting a bad error message - set transaction has to be the first statement in the transaction - it wasn't. I see no serious issue here at all.
Isolation level
Tarek, June 14, 2005 - 4:54 am UTC
Thanks for the response.. I did some test and now it's more clear for me but i still have a doubt..
serializable isolation level
T alter session set isolation_level=serializable;
T+1 select * from t where x = 55;
in another transaction:
T+2 delete from t where x=55;
T+3 commit;
in my transaction
T+4 select * from t where x = 55;
T+5 commit;
T+6 select * from t where x = 55;
I understood that in serializable mode transaction T+1 and T+4 give the same results.. but T+4 gives a wrong result, not consistent!! why do I need to see wrong data? that data doesn't exist anymore..
please, if it's possible, provide me an example that shows when serializable mode is useful.
Thanks again,
Tarek
June 14, 2005 - 9:54 am UTC
you don't show us what you did, so we have to assume you did something other than what you say you did.
Here is my example, the autonomous transaction is as if it were in another session, you could easily use another session:
ops$tkyte@ORA9IR2> create table t ( x int );
Table created.
ops$tkyte@ORA9IR2> insert into t values ( 1 );
1 row created.
ops$tkyte@ORA9IR2> commit;
Commit complete.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set transaction isolation level serializable;
Transaction set.
ops$tkyte@ORA9IR2> select * from t;
X
----------
1
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
2 pragma autonomous_transaction;
3 begin
4 delete from t;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t;
X
----------
1
ops$tkyte@ORA9IR2> commit;
Commit complete.
ops$tkyte@ORA9IR2> select * from t;
no rows selected
Works for me...
Bart Verstegen, June 14, 2005 - 6:22 am UTC
@ Tarek
I replayed your testcase and it worked for me.
At t+4 the resultset is exactly the same as at t+1.
You must be doing something different than what you describe. And that can be many things...
* you entered some DDL (create, alter etc -> implicit commit) beween selects
* you were looking at T+6 instead of T+4
* isolation level was not really set or undone by a reconnect
* actions were effectuated in the wrong session/wrong order
* you didn't use sql*plus but some other clienttool *toad* (boy what a problems I had with testers who uses this tool - unintended locking etc).
* ...
You should replay it yourself again carefully and cut/paste from sql*plus without removing lines
Isolation level
Tarek, June 14, 2005 - 9:30 am UTC
Bart,
maybe I haven't explained well.
I know that both queries give the same result.
What I'm saying is that transaction t+4 has a wrong data. I mean the data does not really exist anymore in the database because it has been deleted by a second transaction.
The query shows that value because I'm in a serializable transaction. But what's the benefit of showing data that doesn't exist anymore in the database? I see not consistent data.
Hope I explained well my point of view.
Thanks
June 14, 2005 - 10:08 am UTC
that is not WRONG data.
your transaction is seeing a CONSISTENT view of the database, it is as if you owned the database. You can see that data, it is all consistent, the database will not change while you are in your transaction (that is the very point).
If you tried to modify that data, it would fail with cannot serialize access.
did you know that if you run:
select * from billion_row_table;
and while you are fetching rows - someone else comes and deletes all of them and commits,
you'll continue to fetch rows.
This did not show you any "wrong data", it shows you the data that was committed in the database when your transaction began, that is what serializable by definition does, what it is, what it must do.
Raj, June 14, 2005 - 10:27 am UTC
Tarek,
Serialiable transaction ensures that results are consistent from the time the transaction was set till the transaction is committed or rolled back.
Therefore you are seeing the results at t4 which would be same for your session (i.e no phantom read and no non-repeatable read) as that in time t1 when serialiable transaction was set with assumption that you have not modified the data between t1 and t4.
Though data was deleted and committed in another session at time T3 you were still able to see in your session at T4 as the transaction was set as serializable.
Tom, Did I get the concept ofr serializable right?
Thanks,
Raj
June 14, 2005 - 1:39 pm UTC
absolutely
Raj, June 14, 2005 - 10:29 am UTC
oops sorry tom , I did not see you have responded all ready and posted a reply to Tarek.
Thanks,
Raj
June 14, 2005 - 1:39 pm UTC
no problem, you said it very well
Isolation level
Tarek, June 14, 2005 - 11:28 am UTC
Thanks, very clear explanation.
The only thing I'm still missing is when do I need to use the serializable isolation level. But I'm not designing any application.. so that's ok.
Thanks again for your help.
June 14, 2005 - 1:52 pm UTC
if you want to get a pretty good book on transaction processing -- click on the links I like and get the Transaction Processing Book.
ok
Joachim Seekopp, June 14, 2005 - 12:03 pm UTC
<quote source=tkyte>
In serializable, those two statements are guaranteed to return the same results
-- (or fail) regardless of the amount of time that transpires between them.
</quote source=tkyte>
I interpreted that ("or fail") this way, that when a statement
fails - for example with ORA-00942, it will fail for the second time as well.
so what do you really meant by "or fail" ?
and, don't believe that
ORA-00600: internal error code, arguments: [setiso02], [5635], [], [], [], [],
[], []
is the exception you're supposed to get when
set transaction
wasn't the first statement in a transaction
- should be documented IMHO otherwise
June 14, 2005 - 4:04 pm UTC
the "or fail" meant ora-8177 or ora-1555.
as for the ora-600, that is a bug, but the bug is simply "wrong error message being raise", hence I do not see it as being a logical reason to "not use it". I said as much above, it is the wrong error message - NO QUESTION. But a reason to not use it? no, i would not say that.
I filed a bug against the behavior already...
thx
Joachim Seekopp, June 15, 2005 - 2:27 am UTC
for clarification
Scofield, July 09, 2009 - 5:13 am UTC
Sir you mentioned that;
begin the transaction
select * from t where x = 55;
select * from t where x = 55;
end the transaction
"Those two queries might each return different results."
In your book you have given "Account" example which oracle reconstructs the modified data as it appeared when the query began.
(this behaviour is by default Read commimitted isolation)
Why doesnt oracle look for the before image from the undo tablespace in this scenerio? I am little bit confused?
July 14, 2009 - 2:20 pm UTC
because there are TWO statements present, they are run at different points in time.
each statement is read consistent with respect to itself. But not with eachother in read commit isolation.
If you want to ensure you get the same answer from both in that transaction (well, unless of course YOU made the changes yourself) you would use SERIALIZABLE isolation (or read only). Then all statements are read consistent (as of) the point in time the transaction began.
how to start a transaction
Loz, July 15, 2009 - 9:54 am UTC
So how do you start a transaction (to get consistent reads) if you don't want to do any updates?
begin the transaction <--- HOW for select only ??
select * from t where x = 55;
select * from t where x = 55;
end the transaction
July 15, 2009 - 12:19 pm UTC
read only transactions
A reader, October 04, 2010 - 4:34 pm UTC
This information is very helpful. It clearly explains in which cases read only trasaction needed.
Java apps and developers tools like TOAD and sql developers seems to be opening transactions by default because I see my sessisions which ran some select queries are aslo appearing in v$transaction from Toad and some of our reporting connections(which just does selects) also shows up in v$trasanction. Is there any way we cna disable the opening of transaction by default from these tools ?
Thanks
Anand
October 05, 2010 - 12:01 pm UTC
you'd have to ask the vendor of those tools if there is a way to control what their tools do. Asking me how to make toad do something would be like asking me about MS word.
the database doesn't know toad from a hole in the ground - all applications look the same to us - we just do what they ask us to do.
read only vs. SERIALIZABLE
volker, October 08, 2010 - 12:42 pm UTC
Hi Tom,
in your followup (July 14, 2009) you wrote:
... you would use SERIALIZABLE isolation (or read only)
I know that only SELECTs are possible in a read only TA, but apart from that: are there other differences between
- SET TRANSACTION READ ONLY
- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
concerning behaviour, performance, concurrency etc?
And I suppose that both can easily be used in an autononous TA, is that true?
Thank you
October 11, 2010 - 11:55 am UTC
both would work there, yes.
I have not measured any difference in processing between them, it is just that read only prevents modifications.