Skip to Main Content
  • Questions
  • Read committed--Why transaction can read uncommitted data

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, jiang huang.

Asked: May 01, 2008 - 1:57 am UTC

Last updated: May 19, 2008 - 4:14 pm UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

Hello Tom
Thanks for your time.. I have a question: I understand that by default Oracle uses read committed by statement level, normally other session can't see the modification execept the session that makes the modification itself. Read committed is caculated based on the SCN of the satement. What I dont understand is that,if I start a transaction by issuing following in sqlplus:

SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 RD Guangzhou
70 Pro Dalian

6 rows selected.

SQL> update dept set dname='GOV' where deptno=70;

1 row updated.

SQL> select * from dept;----- ******* this confuses me.

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 RD Guangzhou
70 GOV Dalian

6 rows selected.

SQL>


the *select* statement after *update* , why is it not a read committed? It seems to me that this select statement reads dirty data? The scn of the statement not involves in this case? How oracle achivement read committment in this case?

Thanks again...




and Tom said...

A transaction may always see it's own changes - that is part of the definition as well. The current consistent state of the database from a transactions point of view is everything that is committed plus their changes.


Read committed, as defined by ANSI, says you cannot read other sessions uncommitted work. Your transaction however can see it's own changes.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Very helpful information

jiang huang zheng, May 01, 2008 - 12:05 pm UTC

That is very useful and Clear my doubts. Transaction can see its change! Thank you!

why this transaction cant read its change?

jiang huang zheng, May 10, 2008 - 2:51 pm UTC

Hello Tom
   from sqlplus:

SQL> insert /*+append */ into qq_i (x) select object_id from user_objects;

156 rows created.

SQL> select *  from qq_i;
select *  from qq_i
               *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

I think that transaction always reads its change,but above doesnt, I think it must be related to the direct insert mode,but I cant figure out the technical reason, why is that?
am I still in a transaction before I commit or rollback?

Tom Kyte
May 12, 2008 - 1:17 pm UTC

direct path mode loads like that have to be committed prior to reading them, yes, they are "special", they are used for loading - not really for any transactional type applications, they must be used with care (and understanding how they work - they never reuse space for example)

APPEND must be followed by a commit

Hemant K Chitale, May 11, 2008 - 12:22 am UTC

The APPEND hint is a direct path insert.
It doesn't update existing table blocks but uses new
blocks above the high water mark.
It requires a COMMIT.

Thanks

jiang huang zheng, May 16, 2008 - 5:32 am UTC

Thank you and it clears my doubts.!

Why does this work then?

Max, May 18, 2008 - 8:57 am UTC

insert /*+append */ into qq_i (x) values( 42 );

... without raising ORA-12838?
Tom Kyte
May 19, 2008 - 4:14 pm UTC

because /*+ APPEND */ only works with insert SELECT, never with values - fortunately!!!!

If you understand what append does, you would know why using it with a single row insert would be "not smart" (it never reuses existing allocate space, you would allocate new space above the high water mark and put one row per block!!!!!!! it would be about as wasteful as you can get)



ops$tkyte%ORA10GR2> drop table t;

Table dropped.

ops$tkyte%ORA10GR2> create table t ( x int );

Table created.

ops$tkyte%ORA10GR2> begin
  2          for i in 1 .. 1000
  3          loop
  4                  insert /*+ append */ into t select 1 from dual;
  5                  commit;
  6          end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec show_space( 'T' )
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................           1,000<b>
Total Blocks............................           1,024</b>
Total Bytes.............................       8,388,608
Total MBytes............................               8
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               4
Last Used Ext BlockId...................          22,025
Last Used Block.........................             128

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop table t;

Table dropped.

ops$tkyte%ORA10GR2> create table t ( x int );

Table created.

ops$tkyte%ORA10GR2> begin
  2          for i in 1 .. 1000
  3          loop
  4                  insert /*+ append */ into t values ( 1 );
  5                  commit;
  6          end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec show_space( 'T' )
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               4
Full Blocks        .....................               1<b>
Total Blocks............................               8</b>
Total Bytes.............................          65,536
Total MBytes............................               0
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               4
Last Used Ext BlockId...................          16,169
Last Used Block.........................               8

PL/SQL procedure successfully completed.