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.

Possibly, but don't forget that the rollback segments have to be maintained anyway so that the transaction can be rolled-back if required (excuse me stating the obvious).

All that the manual is saying is that when a subsequent select is performed the system checks the data being read to see if it has been modified since the start of the transaction, and if so goes to the rollback segment copy to get the original version.

It doesn't matter if the data has changed a number of times because there can only be one "original" version and only one transaction can change any piece of data at any one time, so the system only has to make one check for a change, not many.

Naturally this doesn't apply to the transaction that made the changes - otherwise it wouldn't be able to see its own updates.

Regards,

-- 
-------------------------------------------------------------------------------
Bruce Horrocks
Hampshire, England                   Object Oriented programmers only
                                     need two methods - Create and Scoop-Up
bh_at_granby.demon.co.uk
-------------------------------------------------------------------------------