Re: Inserts, cursors, and dirty reads

Linda,

First of all, one of Oracle's important features is that it provides a consistent view of the data. i.e. dirty reads are not allowed.

In your scenario you do not mention if these are all different Oracle sessions, 2 sessions, or one session.

I will assume that they are all different session. Everyone else, please chime in for anything I miss.

> 1. Insert 1 on table x is started. Oracle needs an extent and starts
> allocation and format.
> 2. Insert 2 on table x is started. Does it wait on insert 1?

Insert 1 will require the ST ( space transaction ) latch if the the tablespace is dictionary managed. Insert 2 will not require this latch if it is not allocating an extent, so no, it does not wait on Insert 1. This is assuming that these are in 2 different sessions.

> 3. Cursor Query 1 on table x is started. Does it wait for inserts 1 and 2,
> or does it dirty read?

If Cursor Query 1 is in a different session than the two inserts, then it will not wait on those inserts to complete, and there will not be a dirty read. Until the Inserts 1&2 are commited, the data from them will not be available to Cursor Query 1.

I'm not going to go into the details on how this is accomplished ( lots of 'em ). Someone else can do that if they like.

> 4. Cursor Query 2 on table x is started. Does it wait for inserts 1 and 2,
> or does it dirty read?

Same as #3 above.

You should probably read the Concepts Manual cover to cover for a basic understanding of how Oracle works.

Jared

On Fri, 8 Sep 2000, Linda Hagedorn wrote:

> Hello,
>
> This question is about expected sql and cursor behavior in this sequence:
>
> 1. Insert 1 on table x is started. Oracle needs an extent and starts
> allocation and format.
> 2. Insert 2 on table x is started. Does it wait on insert 1?
> 3. Cursor Query 1 on table x is started. Does it wait for inserts 1 and 2,
> or does it dirty read?
> 4. Cursor Query 2 on table x is started. Does it wait for inserts 1 and 2,
> or does it dirty read?
>
> I'm trying to estimate why queries are backing up behind an insert. Due to
> the application product, I don't get ORA codes, just a generic time out from
> the application.
>
> References to documentation is appreciated.
>
> Thanks,
>
> Linda Hagedorn
> <<...OLE_Obj...>>
>
>
> --
> Author: Linda Hagedorn
> INET: Linda_at_pets.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;-) Regence BlueCross BlueShield of Oregon Received on Mon Sep 11 2000 - 09:51:41 CDT