Re: Phantom Inserts

In article <3ikkjp$qi6_at_jac.zko.dec.com>

           feenan_at_nova.enet.dec.com "Jay Feenan" writes:

>
>In article <793588545snz_at_granby.demon.co.uk>, bh_at_granby.demon.co.uk (Bruce
> Horrocks) writes...
>>
>>Funny how both the responses form Oracle personnel mentioned Rdb
>>rather than (original) Oracle.
>
>Since the original posting was in the comp.databases.rdb newsgroup and we
>tend to follow it closely...

I didn't intend this to become a big issue, its just that the original was posted to both c.d.rdb _and_ c.d.oracle but only the Rdb people responded.

[snip]

>>>BTW, how important is the protection against phantom inserts anyway?
>>>It doesn't seem common in applications that I have seen.
>>
>>I don't think that I've ever had to worry about it in any application
>>that I've written. Doing the same select twice in a row in the same
>>transaction would be so obviously inefficient that I would try to
>>design around it as a matter of course.
>
>But phantom protection is in this tread has been shown
>in its most primitive form...of really pairing a select and either an
>insert/update or select together. When a database engine supports other
>features that the industry requires such as referential integrity it becomes
>much more complex [but can always be broken down to the primitives presented].

You contradict yourself: yes the example was simple but, as you say, more complex cases can still be resolved into the primitives presented. Which means that my point applies: an "unnecesary" select is still being performed. In your defence though I would say that spotting such statements is not easy but then if it were I would be out of a job.

>The fact of the matter is, when a database engine supports referential, the
>many applications that you have developed would have no clue of this
>requirement. The bottom line is that although your statement and mind set is
>true for the applications that you've developed...phantom prevention can be a
>requirement for the database that you run your application against, but you
>never see it.

I agree absolutely: if the requirement to prevent phantoms is introduced by the database itself then the database should be responsible for preventing them. However I don't think the orignal poster had (declarative, I presume) referential integrity in mind when posing the original question.

Regards,

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