Re: Phantom Inserts

In article <finkel.212.0036CAE6_at_links.com>, Richard Finkelstein wrote:

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

This was pretty much what the Concepts Manual said. I especially don't like the idea of the possibility of getting an ORA-1555 message: Snapshot too old (rollback segment too small).

I am planning to make my very simple database package transactional and perhaps put SQL on top of it. I used to think that designing and implementing the actual parsing, optimization and execution of SQL would be a difficult task, but now that all seem like nothing compared to the problems involved in providing ACID properties.   

I have read "Transaction Processing: Concepts and Techniques" by Andreas Reuter and Jim Gray, which I can highly recommend to anyone interested in the subject. But I am now looking for a similiar book about implementing SQL which hopefully addresses some of these problems.

Thomas Pedersen   

--
   ____________ ___ ____ _   _ _  _
  |_  |  _/| _ | _ |  _/| | | | \| |  zeppelin_at_login.dknet.dk
   / /|  _>|  _|  _|  _>| |_| |    |
  /___|___\|_| |_| |___\|___|_|_|\_|