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.

Phantom reads Example

Phantom reads Example

Follow CodeGalaxy

Mobile Beta

Keep exploring
SQL snippets

Phantom reads Example

Phantom reads Example

Related Content

Follow CodeGalaxy

Mobile Beta

Keep exploring SQL snippets

Keep exploring
SQL snippets