‹ BACK

Phantom reads Example

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.
phantom-read transactions isolation SERIALIZABLE REPEATABLE-READ

To check your knowledge the following tests are recommended:
Check your sql sql skills.