Re: Phantom Inserts

In article <tIxKl0iA$zTC065yn_at_login.dknet.dk> zeppelin_at_login.dknet.dk (Thomas B. Pedersen) writes:
>In article <pwongD4C773.F15_at_netcom.com>, Philip S. Wong wrote:
>
>> I was wondering how different databases/vendors implement protection
>> against phantom inserts. In particular, how can they achieve phantom
>> protection while maintaining a good degree of concurrency?
>
>The ORACLE7 Concepts Manual (10-6) mentions something called a SCN
>(system change number) which is used to ensure that transaction A
>never sees data committed by transaction B after transaction A started.
>ORACLE calls this "statement-level read consistency".
>
>When providing read-consistency, the manual says, "blocks with changed
>data (more recent SCNs) are reconstructured using data in the rollback
>segments...". I don't doubt that it is necessary to provide some kind
>of read-consistency, but doesn't this approach cause an enormous
>performance degradation? Especially for long-running transactions the
>same data could have been changed a number of times.
>

Yes, but if you factor in the complicated buffering algorithms, such as the possibilty that continuing to find the record in memory avoids disk hits, the performance _can_ be much better than one would expect.

On the other hand, it can be unexpectedly worse. It is a non-linear function that can only be evaluated empirically. And extremely sensitive to design and tuning.

>
>zeppelin
>
>
>--
> ____________ ___ ____ _ _ _ _
> |_ | _/| _ | _ | _/| | | | \| | zeppelin_at_login.dknet.dk
> / /| _>| _| _| _>| |_| | |
> /___|___\|_| |_| |___\|___|_|_|\_|

-- 
Joel Garry           joelga_at_amber.rossinc.com            Compuserve 70661,1534
These are my opinions, not necessarily those of Ross Systems, Inc.
%DCL-W-SOFTONEDGEDONTPUSH, Software On Edge - Don't Push.  
panic: ifree: freeing free inodes...