Re: Phantom Inserts

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.

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

-- 
Ian Smith
Oracle Rdb Engineering Group
(Standard disclaimer: These opinions are mine and in no way represent a
		      commitment or opinion of my employer)