Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Reads Block Writes?

Re: Reads Block Writes?

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 3 Jun 2002 13:43:02 -0700
Message-ID: <adgkcm02e0k@drn.newsguy.com>


In article <adghqv$dl5$1_at_lust.ihug.co.nz>, "Howard says...
>
>
>"Buck Turgidson" <jc_va_at_hotmail.com> wrote in message
>news:53bf207579c6f09f7c05b678b7bb15f5.38849_at_mygate.mailgate.org...
>> I know that a "select for update" will lock rows and block an update
>> statement, but is it possible for a simple select to block an update of
>> the same table? Using a "lockby" script, it appears to be the case that
>> a long running select is blocking an update, but I didn't think that
>> this would happen.
>
>
>It doesn't. Ever.

well, in 7.x and before, if you set serializable = true in the init.ora, it would (as all selects in that mode took a table level share lock) but that is probably not the case here. Totally redone in 7.3.3 and up with the serializable isolation level (which rendered serializable =true in the init.ora obsolete)

(my guess is that the update is blocked by a session that is currently running a select, the blocking statement is no longer being executed by the blocking session. The select isn't blocking the other session, but some prior statement in that session is)

>
>A regular select takes no locks whatsoever,
> generates no redo,

well, USUALLY generates no redo would be more applicable ;)

Block cleanouts will generate redo. This following was done with 100 block buffers (small - realizing that we'll do a commit cleanout on at most the number of blocks that fit in 10% of the buffer cache -- or 10 blocks in this example...). The table T has 320 blocks in it, all of which we'll update:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t as select * from all_objects; Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> update t set object_name = lower(object_name);
23045 rows updated.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> commit; Commit complete.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> set autotrace traceonly statistics ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select object_name from t;

23045 rows selected.

Statistics


          0  recursive calls
         12  db block gets
       2146  consistent gets
        426  physical reads
      18960  redo size                        <<<<<<<<<<<==================
     909739  bytes sent via SQL*Net to client
     170921  bytes received via SQL*Net from client
       1538  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      23045  rows processed

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> commit;

Commit complete.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select object_name from t;

23045 rows selected.

Statistics


          0  recursive calls
         12  db block gets
       1830  consistent gets
        317  physical reads
          0  redo size                        <<<<<<<<<<<==================
     909739  bytes sent via SQL*Net to client
     170921  bytes received via SQL*Net from client
       1538  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      23045  rows processed

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> commit;

Commit complete.

> no undo, and
>therefore cannot block other readers and writers.
>

it would suffice to say that a normal select takes no locks, and that is what would need to take place in order for a select to block someone else...

>It is, however, possible that a large report could chew up all available
>temporary tablespace and prevent another large report that needs to sort in
>that tablespace from completing successfully -but you'd know about that
>because of the rash of error messages produced.
>
>Regards
>HJR
>
>>
>> Thanks for any replies.
>>
>>
>>
>> --
>> Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
>
>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 
Received on Mon Jun 03 2002 - 15:43:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US