Re: ISOLATION LEVEL

  • From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
  • To: niall.litchfield@xxxxxxxxx, "DBA Deepak" <oracle.tutorials@xxxxxxxxx>
  • Date: Tue, 10 Jul 2007 22:48:50 +0800


Oracle provides Statement Level Read Consistency.
Every fresh SQL execution is a new statement.
Any data updated and committed  [eg by some _other_ session]
between two SELECTs will be visible to the next SELECT statement.
From the docs
"Read committed : This is the default transaction isolation level. Each query executed by a transaction sees only data that was committed before the query (not the transaction) began. An Oracle query never reads dirty (uncommitted) data.

Because Oracle does not prevent other transactions from modifying the data read by a query, that data can be changed by other transactions between two executions of the query. Thus, a transaction that runs a given query twice can experience both nonrepeatable read and phantoms."

See my example at see http://hemantoracledba.blogspot.com/2007/06/read-consistency-across-statements.html


Deepak's requirement is explicitly
"is to query several tables several times inside a piece of code. The data needs to be consistent across multiple queries. "


Hemant
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



--
//www.freelists.org/webpage/oracle-l


Other related posts: