Snippets Phantom re ...

Phantom reads

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;
non-repeatable-reads transactions range-locks isolation phantom-read

Related Content
SQL Quizzes
SQL
Quiz

 
Login to learn SQL

or Read more about SQL Quizzes

Follow CodeGalaxy

Mobile Beta

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