Re: fuzzy/phantom read

"Miroslaw Grygolec" <mg_at_imio.pw.edu.pl> wrote in message news:3A2397A4.4600247F_at_imio.pw.edu.pl...
> Hello everybody,
>
> I wonder about the meaning of two concepts defined in SQL92 (definitions
>
> after "Oracle 8 Concepts"):
> a) FUZZY READ: A transaction rereads data it has previously read and
> finds that another committed transaction has modified or deleted the
> data.
> b) PHANTOM READ: A transaction re-executes a query returning a set of
> rows that satisfies a search condition and finds that another committed
> transaction has inserted additional rows that satisfy the condition.
>
> I understand that the only difference between these two is that fuzzy is
> related to the modification/deletion, while phantom to the insertion
> only. But the reason for we distinguish these two is not obvious for me.
>
> Why don't we treat insertion as a kind of modification and therefore
> unify these two terms into one? Or - why don't we separate FUZZY READS
> into a) related to modification; b) related to deletion only. Are there
> some cases when it is important to prevent from phantom while allow
> fuzzy (or vice-versa)? I can hardly imagine such a thing.
>
> Can anybody clarify a bit the issue?
> Mirek

What you call "fuzzy read" is called "non-repeatable read" in the SQL-92 specification.

According to the SQL-92 definiton, a phantom could be due to either insertion
or deletion: It says only that the second search returns "a different collection of
rows".

The key difference is that the "non-repeatable read" phenomenon is defined in
terms of a transaction that rereads specific rows, while a phantom is related to
a search condition. The reason for making such a distinction is purely practical. Simple record locking can be used to prevent non-repeatable reads,
but it is not sufficient to prevent phantoms (or at least phantom inserts). Therefore, an application that could tolerate phantoms might be able to use less severe locking than one that was fully serializable. Received on Tue Nov 28 2000 - 19:57:01 CET