I am a bit confused by exactly what constitute a Phantom Read. Does it
include ONLY the case where ADDITIONAL rows have been added during 2
queries. Then what about rows DELETED between 2 queries? I have this
concern because many textbooks do define Phantom read as addition of rows
but never mention anything about removed rows.

If Oracle or any database says its Serializable isolation level does prevent
Phantom Read, can I assume that it means no rows inserted nor DELETED
between 2 queries?

Thanks for any advice.

dd wrote:
> I am a bit confused by exactly what constitute a Phantom Read. Does
> it include ONLY the case where ADDITIONAL rows have been added during
> 2 queries. Then what about rows DELETED between 2 queries? I have
> this concern because many textbooks do define Phantom read as
> addition of rows but never mention anything about removed rows.

AFAIK "Phantom reads" just refer to rows added after an earlier read. If
you have deleted rows it's a nonrepeatable read. See chapter "Data
Concurrency and Consistency" of the Concepts document.

> If Oracle or any database says its Serializable isolation level does
> prevent Phantom Read, can I assume that it means no rows inserted nor
> DELETED between 2 queries?

Yes, because Serializable prevents phantom reads *and* nonrepeatable
reads.

Kind regards

robert

dd, the drawback of serializable reads is that attempting to make
transactions run this way kills application performance. Either the
sessions single thread or error off because Oracle is unable to
serialize the transaction.

The Oracle concurrency model was chosen to provide high concurrent data
access while providing time consistent views of the data. You need to
give consideration to the effect of concurrent access to the data
involved in all multistatement transactions. The fact that writers do
not block readers is very important in situation such as where you try
to use table triggers to enforce referential integrity rules rather
than FK.

HTH -- Mark D Powell --