Re: Phantom Inserts

Hi Thomas.

In article <tIxK
l0iA$zTC065yn_at_login.dknet.dk> zeppelin_at_login.dknet.dk (Thomas B. Pedersen) writes:>From: zeppelin_at_login.dknet.dk (Thomas B. Pedersen)
>Subject: Re: Phantom Inserts
>Date: Tue, 28 Feb 1995 18:41:43 -0500
 

>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.

Oracle's multi-versioning read consistency (MVRC) is an interesting animal with its own strengths and weaknesses. Over in they Sybase newsgroup there has been extensive discussion regarding the effect that MVRC reads (SELECTSs) has on data consistency when used in _update_ transactions. This is a real no-no but something that is a necessary evil in the world of Oracle where MVRC are almost always used in update transactions.

The only way to prevent phanthom inserts in an Oracle update transaction is to set SERIALIZABLE=TRUE in the init.ora file. This locks the entire table when it is updated by any transaction. This does block phantom inserts but does have substantial impact on concurrency.

Read-only transactions can use MVRC effectively. The whole transaction can be set to read-only and then the transaction would only see a consistent version of the result set at a point in time. Your point concerning the construction of the MVRC result set is a good one. There is assuredly some overhead associated with reading rollback segments (especially if they are the long rollback segments) when try to find the correct version of a record. This problem would be most acute when accessing a database that is being heavilty updated. Another side-effect is that it is possible that Oracle cannot construct the consistent result set - i.e., the correct version of the record is no longer on the rollback segment because it has been overwritten. This is most likely to occur when a MVRC transaction has been executing for a long time and there is a high volume of update transaction. In this case the MVRC will abort.

Hope this helps a bit.

Regards,

Rich
Richard Finkelstein, Performance Computing, Inc. 3930 N. Pinegrove, Ste. 1214, Chicago, IL 606013 (312) 549-8325 (Voice) 312-549-4824 (Fax) Received on Wed Mar 01 1995 - 10:00:19 CET