Editing Isolation (database systems) (section)

===Phantom reads===
A ''phantom read'' occurs when a transaction retrieves a set of rows twice and new rows are inserted into or removed from that set by another transaction that is committed in between.

In this example, transaction 1 retrieves the set of rows with age greater than 17, then transaction 2 inserts a row with age 26 and is committed, and finally transaction 1 retrieves the set of rows with age greater than 17 again. At the READ UNCOMMITTED, READ COMMITTED, and REPEATABLE READ isolation levels, the second SELECT in transaction 1 retrieves the new set of rows that includes the inserted row: this is a phantom read. At the SERIALIZABLE isolation level, the second SELECT in transaction 1 retrieves the initial set of rows.

{|
|-
! Transaction 1
! Transaction 2
|-
|<syntaxhighlight lang="sql">
BEGIN;
SELECT name FROM users WHERE age > 17;
-- retrieves Alice and Bob
</syntaxhighlight>
|
|-
|
|<syntaxhighlight lang="sql">
BEGIN;
INSERT INTO users VALUES (3, 'Carol', 26);
COMMIT;
</syntaxhighlight>
|-
|<syntaxhighlight lang="sql">
SELECT name FROM users WHERE age > 17;
-- READ UNCOMMITTED retrieves Alice, Bob and Carol (phantom read)
-- READ COMMITTED retrieves Alice, Bob and Carol (phantom read)
-- REPEATABLE READ retrieves Alice, Bob and Carol (phantom read)
-- SERIALIZABLE retrieves Alice and Bob (phantom read has been avoided)
COMMIT;
</syntaxhighlight>
|}

There are two basic strategies used to prevent non-repeatable reads and phantom reads. In the first strategy, ''lock-based concurrency control'', transaction 2 is committed after transaction 1 is committed or rolled back. It produces the serial [[database transaction schedule|schedule]] ''T1, T2''. In the other strategy, ''[[multiversion concurrency control]]'', transaction 2 is committed immediately while transaction 1, which started before transaction 2, continues to operate on an old snapshot of the database taken at the start of transaction 1, and when transaction 1 eventually tries to commit, if the result of committing would be equivalent to the serial schedule ''T1, T2'', then transaction 1 is committed; otherwise, there is a commit conflict and transaction 1 is rolled back with a serialization failure.

Under lock-based concurrency control, non-repeatable reads and phantom reads may occur when read locks are not acquired when performing a SELECT, or when the acquired locks on affected rows are released as soon as the SELECT is performed. Under multiversion concurrency control, non-repeatable reads and phantom reads may occur when the requirement that a transaction affected by a commit conflict must be rolled back is relaxed.