Transaction Read only and isolation level serializable
Logo Palanisamy, June 01, 2001 - 4:08 pm UTC
Well said.
set transaction read write
michael, March 26, 2004 - 10:06 am UTC
Tom,
I understand the need for set transaction read only; however, I do not understand the need for set transaction read write.
If the transaction is not committed due to error, will the entire transaction ROLLBACK?
Can you please give me an example of a situation where SET TRANSACTION READ WRITE is necessary and not necessary?
Thank you.
March 26, 2004 - 10:55 am UTC
it is our way of "begin transaction" if you well. It is part of the standard SQL.
you don't ever really need to use it. I've never used it.
TRANSACTION READ ONLY ?!
Sergei, April 08, 2004 - 10:07 am UTC
I am totally confused, sorry.
A 'TRANSACTION' is something that CHANGES the data, as opposed to SQL QUERY, that does not change data.
Therefore, the TRANSACTION should be able to write, and can not be set READ ONLY by definition.
Please set me strait, I'm missing something here.
Sergei.
April 08, 2004 - 10:53 am UTC
a transaction is a set of statements -- nothing more, nothing less.
if you set transaction read only -- then all sql statements issued in that transaction, until you commit, are run "as of that point in time".
if you set transaction readonly at 12 noon -- run a query and go to lunch. when you come back and run that query again, the answer will be the same -- no matter what has happened to the table in other sessions (until you commit)
a transaction is just a set of consecutive statements.
What about v$views ?
Marc, April 08, 2004 - 1:00 pm UTC
Is the phrase <if you set transaction read only -- then all sql statements issued in that transaction, until you commit, are run "as of that point in time"> also true and valid for queries against v$views?
Thanks in advance.
April 08, 2004 - 3:36 pm UTC
v$views are documented to not return read consistent results as they are the results of querying in memory structures, not data in tables.
so -- no, it does not apply to them at all.
read only vs serializable
Menon, December 27, 2004 - 10:23 am UTC
"set transaction read only predates serializable by a couple of years -- if they
had come out at the same time, we probably would not have read only as serializable is a superset of read only. "
I understand the difference between the two but how is
serializable a superset of read only? read only is more
restrictive than serializable. If you did not have the
read only "isolation level", how would you have implemented
read only?
Thanx.
December 27, 2004 - 10:41 am UTC
something that is a superset contains all of the functionality plus.
read only is a subset of the functionalty of serializable.
serializable is a superset of read only.
hmmm...
Menon, December 27, 2004 - 10:50 am UTC
that way "read uncommitted" is a super set of "read committed"?
I know what you mean - it is just that here functionality
is defined in a way by being more restrictive.
read only disallows insert, update etc.
serializable does not.
in that way, serializable does not provide the
functionality provided by read only.
December 27, 2004 - 10:58 am UTC
no, read uncommitted is not a super set of read committed .
they have totally different attributes, they are not supersets/subsets of eachother at all.
serializable provides all of the functionality of READ ONLY plus other stuff that read only cannot do. Hence serializable is a SUPERSET of read only. read only is a SUBSET of the functionality of serializable.
A read only transaction is in fact a serializable transaction whereby modifications are not permitted.
read uncommitted and read committed -- they have entirely different attributes -- they are not subsets of each other at all.
a silly question
amit poddar, December 27, 2004 - 11:03 am UTC
Tom,
All these terms and their definitions, they are always in your head or you refer to some documentation/your books to answer these type of questions.
December 27, 2004 - 11:30 am UTC
the terms on this page -- in the head, they are just "part of the database"
Guess these terms stick because I learned about databases -- not "SQL", but databases.
See the "LINKS I LIKE" tab, the Book on "Transaction Processing" is one of the original books I learned from.
well..
Menon, December 27, 2004 - 11:08 am UTC
"no, read uncommitted is not a super set of read committed ."
I know that - that was a "rhetorical" question...
"serializable provides all of the functionality of READ ONLY plus other stuff that read only cannot do."
I am not sure about that (or rather I would not put
it that way.)
serializable is less restrictive than read only.
If serializable could provide all functionality of read only
then read only is redundant right? Tell me how would
you implement read only functionality if it were not
there (there may be ways I am not aware of.)
I think we are disagreeing more on how to say something
rather than how something works...
December 27, 2004 - 12:14 pm UTC
not really - rhetorical questions are those that have "obvious, to the speaker, answers"
"that way "read uncommitted" is a super set of "read committed"?"
is not an accurate portrayal :)
read only is pretty much redundant in light of serializable -- yes. If you don't perform an insert, update, delete, or merge in a serializable tranaction, all you have is a "read only" one. read only is in fact serializable without modifications, nothing more, nothing less.
If read only didn't exist syntactically, I would tell you to use serializable.
continuing the debate...
Menon, December 27, 2004 - 12:57 pm UTC
"not really - rhetorical questions are those that have "obvious, to the speaker,
answers"
a. the answer to my question was obvious - NO - you said it yourself...:),
b. Hey anyways I put the word rhetorical in quotes!:)
"If you don't perform an insert, update, delete, or merge in a serializable tranaction,
all you have is a "read only" one"
The functionality that I am talking about is that
of not being *able* to insert/update etc..:
scott@ORA92I> create table t1 ( x number );
Table created.
scott@ORA92I>
scott@ORA92I> set transaction islolation level = serializable;
set transaction islolation level = serializable
*
ERROR at line 1:
ORA-00900: invalid SQL statement
scott@ORA92I> insert into t1(x ) values( 1 );
1 row created.
scott@ORA92I> rollback;
Rollback complete.
scott@ORA92I> set transaction read only;
Transaction set.
scott@ORA92I> insert into t1(x ) values( 1 );
insert into t1(x ) values( 1 )
*
ERROR at line 1:
ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction
--
(I know you dont need the above code - just for
my thinking...)
read only provides it, serializable does not...
In the end, it is all a matter of how you think about it.
(Just like read committed provides the "restrictive"
functionality of being able to see only committed
transactions - as opposed to "anything goes" in
read uncommitted...
December 27, 2004 - 1:38 pm UTC
read only is therefore a subset of serializable.
just like standard edtion of Oracle is a subset of the Enterprise Edition which is a superset of Standard.
I can do everything in Enterprise I can do in Standard
I can do everything in Serializable I can do in Read only
There are things in Enterprise Edition I can do that I cannot in Standard
There are things in Serializable I can do that I cannot in Read only
the "may not" is removal of functionality -- "you cannot do that". But everything I CAN DO in read only I CAN DO in serializable.
David Aldridge, December 27, 2004 - 1:23 pm UTC
"islolation"?
Shurely shome mishtake?
good catch David:)
Menon, December 27, 2004 - 1:46 pm UTC
just for the record, the corrected test...
---
scott@ORA92I> drop table t1;
Table dropped.
scott@ORA92I> create table t1 ( x number );
Table created.
scott@ORA92I>
scott@ORA92I> set transaction isolation level serializable;
Transaction set.
scott@ORA92I> insert into t1(x ) values( 1 );
1 row created.
scott@ORA92I> rollback;
Rollback complete.
scott@ORA92I> set transaction read only;
Transaction set.
scott@ORA92I> insert into t1(x ) values( 1 );
insert into t1(x ) values( 1 )
*
ERROR at line 1:
ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction
-------
Tom,
no point in arguing any further on this...
getting the "set transaction" values
Menon, January 12, 2005 - 11:22 am UTC
Hi Tom
Simple question.
Is there a way to get the value of the "set transaction"
setting for a given transaction? e.g. if you set it
to read only or serializable...(dbms_transaction does
not provide it.) The view v$transaction does not
seem to provide it..
By the same token is there a way to get your session's
settings set by "alter session"?
January 12, 2005 - 1:09 pm UTC
not that I am aware of