Re: ISOLATION LEVEL
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: niall.litchfield@xxxxxxxxx, "DBA Deepak" <oracle.tutorials@xxxxxxxxx>
- Date: Tue, 10 Jul 2007 23:38:10 +0800
Niall,
Also read in the same article by Tom Kyte, in the section on READ COMMITTED
(the default mode) :
The READ COMMITTED isolation level states that a transaction may read
only data that has been committed in the database. There are no dirty
reads (reads of uncommitted data). There may be nonrepeatable reads
(that is, rereads of the same row may return a different answer in
the same transaction) and phantom reads (that is, newly inserted and
committed rows become visible to a query that weren't visible earlier
in the trans-action).
What Deepak needs is REPEATABLE READs. Furthermore, he wants to prevent
PHANTOM READs.
That is why he is asking for either of SERIALIZABLE or READ ONLY.
The difference between SERIALIZABLE and READ ONLY (also explained in
Tom Kyte's article)
is that READ ONLY does not allow any DML by the transaction.
Both SERIALIZABLE and READ ONLY meet Deepak's requirements.
Hemant K Chitale
At 09:13 PM Tuesday, Niall Litchfield wrote:
Oracle will always return consistent data from standard selects,
unless you try extremely hard to shoot yourself in the head. code
that looks like this
begin
select cols from tab1 where ...;
select more cols from tab2 where...;
select even more cols from tab3 where ...;
do some calculations and validations.
select yet more cols from tab4 based on the earlier calcs;
end;
will be consistent. What Oracle is doing essentially is the following
begin -- note that I have started a transaction at SCN x .
select cols from tab1 where ...; as of x
select more cols from tab2 where...; as of x
select even more cols from tab3 where ...; as of x
do some calculations and validations.
select yet more cols from tab4 based on the earlier calcs; as of x
end; -- note the transaction has now ended.
when Oracle comes to query the tables it will built a version of the
datablocks in memory as of SCN x. Later changes will not be seen.
This comes at a cost of course, Oracle needs access to the undo data
to rebuilt the blocks it will need to satisfy the query You might
also wish to look at
<http://www.oracle.com/technology/oramag/oracle/05-nov/o65asktom.html>http://www.oracle.com/technology/oramag/oracle/05-nov/o65asktom.html,
you most certainly should look at
<http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10743/consist.htm#sthref1934>http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10743/consist.htm#sthref1934
which describes concurrency in an Oracle database clearly and simply.
cheers
Niall
Hemant K Chitale
http://hemantoracledba.blogspot.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
