Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Reads Block Writes?
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 CorpReceived on Mon Jun 03 2002 - 15:43:02 CDT