DBMS Phantom Read

Question

A Phantom Read is when two SELECT queries within the same transaction return a different number of rows. But if the number of rows returned is the same and the data is different, is that also considered as Phantom?

Comments

When number of rows are same but data is different then someone performed UPDATE on it.

Hence these problems will be considered as Nonrepeatable read

Phantom read occurs only when rows are increased or decreased.

Answer 1

Yes,

A phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second query is different from the first.

To read more see this link:https://stackoverflow.com/questions/11043712/what-is-difference-between-non-repeatable-read-and-phantom-read

Comments

@Rajnish

According to your link also this case is considered as non repeatable read.

Hence this problem can't be considered as phantom.

---

Phantom-read occurs when we execute some query on no. of rows and Non-repeatable read is considered only on a particular row in the table.

Example of Phantom Read Query are:  AVG SUM(), COUNT() etc.

It depends upon on query which you execute.

Answer 2

yes, that would be consider as the Phantom read,

• let there be Update operation, and you changed some data(on attribute(s) which is in the where clause, obviously), then the result query of the result will be different from before.
• then the second case would be, let query Q generate result set R = {a, b, c, d} and then you perform first delete, {a, b, c} and then insert, {a, b, c, e}. Now, again the query is same but generating a different result set. Same number of rows and different data, as you asked.

Can be avoided by acquiring range locks.

Also refer, https://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Phantom_reads