SQL quiz. What is a phantom read?

What is a phantom read?

Phantom reads do not exist
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 one returned by the first query
All reads from the data under snapshot are phantom reads
When values in the data set before the end of the transaction are changed, giving incorrect/wrong data back

Explanation

Phantom read is one of anomalies that may happen in a database in case if proper transaction level is not imposed.
Phantom read occurs when two identical queries executed at pretty much the same time return different sets of results.
Imagine two transactions happening at the same time. First transaction selects all records from some table, then second one inserts a new record in that same table. And then first transaction selects all records from that table again. This time it will get newly inserted record in a result set. If second transaction fails before committing its result (and the newly inserted record is not saved), this record is called a phantom record - because at the end of the day it does not exist in the database.
Phantom reads occur when range locks are not acquired to the table that is modified and may be prevented by applying appropriate transaction isolation level to the database.

Theory

Phantom reads

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.
This can occur when range locks are not acquired on performing a SELECT ... WHERE operation.
This anomaly is a special case of Non-repeatable reads when Trans.1 repeats a ranged SELECT ... WHERE query and, between both operations, Trans.2 creates new rows (in the target table) which fulfill that WHERE clause.

Trans.1	Trans.2
`SELECT * FROM users WHERE age BETWEEN 10 AND 30;`
	`INSERT INTO users(id,name,age) VALUES ( 3, 'Bob', 27 ); COMMIT;`
`SELECT * FROM users WHERE age BETWEEN 10 AND 30; COMMIT;`

Phantom reads Example

Trans.1	Trans.2
`SELECT * FROM users WHERE age BETWEEN 10 AND 30;`
	`INSERT INTO users(id,name,age) VALUES ( 3, 'Bob', 27 ); COMMIT;`
`SELECT * FROM users WHERE age BETWEEN 10 AND 30; COMMIT;`

Note that Trans.1 executed the same query twice. If the highest level of isolation were maintained, the same set of rows should be returned both times, and indeed that is what is mandated to occur in a database operating at the SQL SERIALIZABLE isolation level. However, at the lesser isolation levels, a different set of rows may be returned the second time.

Phantom reads Example

Trans.1 Trans.2

Trans.1	Trans.2
`/* Query 1 / SELECT FROM users WHERE age BETWEEN 10 AND 30;`
	`/* Query 2 */ INSERT INTO users(id,name,age) VALUES ( 3, 'Bob', 27 ); COMMIT;`
`/* Query 1 / SELECT FROM users WHERE age BETWEEN 10 AND 30; COMMIT;`

/* Query 1 */
SELECT * FROM users
WHERE age BETWEEN 10 AND 30;

/* Query 2 */
INSERT INTO users(id,name,age) 
VALUES ( 3, 'Bob', 27 );
COMMIT;

/* Query 1 */
SELECT * FROM users
WHERE age BETWEEN 10 AND 30;
COMMIT;

Note that Trans.1 executed the same query twice.
In the SERIALIZABLE isolation mode, Query 1 would result in all records with age in the range 10 to 30 being locked, thus Query 2 would block until the first transaction was committed.
In REPEATABLE READ mode, the range would not be locked, allowing the record to be inserted and the second execution of Query 1 to include the new row in its results.