Re: Phantom Inserts

In article <3il84m$msq_at_jac.zko.dec.com>

           smithi_at_nova.enet.dec.com "Ian Smith" writes:

>
>In article <793588545snz_at_granby.demon.co.uk>, bh_at_granby.demon.co.uk (Bruce
> Horrocks) writes:
>|>>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.
>
>Well Bruce it may be that the first "select" fetches the row for review by the
>user and the second "select" is actually an update of those rows. i.e. it is
>not the exact same query but it still must process the same set of rows.
>
>An example might be: select all salespeople who earned excess profit over
>budget, and calculate bonus amount. Now update all those same salespeople and
>apportion the bonus according to their contribution. The first query processes
>the row set A (with cardinality n) performing an aggregation. The second query
>needs to process the same set A. If a phantom was introduced (a sales person
>who delivered that last minute sale, then the bonus calulation would be wrong
>and the apportionment would be incorrect because the cardinality has changed.

This example is easily refuted: there would always be a cut-off date, e.g. the end of the financial period, for the submission of sales results and the bonus calculation would take place after this. Any late entries would count against the next financial period and so couldn't affect the result.

>These types of query are quite common. Any application which wants to display
>data, collect user input and then update may need to release row locks during
>user input. Otherwise a trip to get coffee would lock up the database too
>long. The second query (if it processes more than one row) should fetch the
>same rows again...

I don't believe these queries are nearly as common as you make out. Invariably there is, _or should be_, a business rule (the cut-off date in the example above) that means the scenario of phantom inserts is merely an academic issue. I invite counter-examples from the real world ...

Finally, can anyone suggest a scenario where you _would_ want phantom inserts to be allowed?

Regards,

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