Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Migrating from 6.5 SQL Server to Oracle 8.1
"DA Morgan" <damorgan_at_exesolutions.com> wrote in message
news:3E48B24D.FA60962E_at_exesolutions.com...
> > The issue with Oracle's row level locks is that _if_ you need them,
> > the lock is implemented by writing information to the Oracle data
> > block where the row resides. If you have 100 rows on 100 different
> > data pages and you need to "SELECT FOR UPDATE" those rows, then those
> > "reads" are now also "writes"!!!
<snip>
> Your misunderstanding is, how shall I say this kindly, considerable.
Locking of
> rows is in the header. But what does that have to writing? The informatin
is
> cached in memory.
I don't believe that it is in fact the case consider the following
SQL> select count(*) from large_tab;
COUNT(*)
232632
SQL> analyze table large_tab compute statistics;
Table analyzed.
SQL> select blocks from dba_segments where segment_name='LARGE_TAB';
BLOCKS
3200
*so it really is a large table, at least for a laptop
in a second session issue SELECT * FROM LARGE_TAB FOR UPDATE; and then wait
for the select to finish
back to the same session
SQL> SELECT USED_UBLK,USED_UREC
2 FROM V$TRANSACTION;
USED_UBLK USED_UREC
---------- ----------
1892 232632
so we see that the session has used 1892 undo blocks, with one record for each row affected. This is a write. You will also get redo records written. If you modify the block (even just to update the ITL) then you will get write activity. In many cases of course the select for update is unnecessary which I think is the point Jonathan was making in the thread Pablo refers to. Perhaps we should campaign to rename pessimistic locking costly locking.
-- Niall Litchfield Oracle DBA Audit Commission UKReceived on Tue Feb 11 2003 - 06:07:15 CST