What is a phantom read?
Explanation
Get an explanation when it's available:
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
    /* 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.

Follow CodeGalaxy

Mobile Beta

Get it on Google Play
Send Feedback
Keep exploring
SQL quizzes
Cosmo
Sign Up Now
or Subscribe for future quizzes