Editing Isolation (database systems) (section)

==Read phenomena==
The ANSI/ISO standard SQL 92 refers to three different ''read phenomena'' when a transaction retrieves data that another transaction might have updated.

In the following examples, two transactions take place. In transaction 1, a query is performed, then in transaction 2, an update is performed, and finally in transaction 1, the same query is performed again.

The examples use the following relation:

{|class="wikitable"
|+ users
! id !! name !! age
|-
| 1  || Alice  || 20
|-
| 2  || Bob || 25
|}

===Dirty reads===
{{Main|Write-read conflict}}

A ''dirty read'' (aka ''uncommitted dependency'') occurs when a transaction retrieves a row that has been updated by another transaction that is not yet committed.

In this example, transaction 1 retrieves the row with id 1, then transaction 2 updates the row with id 1, and finally transaction 1 retrieves the row with id 1 again. Now if transaction 2 rolls back its update (already retrieved by transaction 1) or performs other updates, then the view of the row may be wrong in transaction 1. At the READ UNCOMMITTED isolation level, the second SELECT in transaction 1 retrieves the updated row: this is a dirty read. At the READ COMMITTED, REPEATABLE READ, and SERIALIZABLE isolation levels, the second SELECT in transaction 1 retrieves the initial row.

{|
|-
! Transaction 1
! Transaction 2
|-
|<syntaxhighlight lang="sql">
BEGIN;
SELECT age FROM users WHERE id = 1;
-- retrieves 20
</syntaxhighlight>
|
|-
|
|<syntaxhighlight lang="sql">
BEGIN;
UPDATE users SET age = 21 WHERE id = 1;
-- no commit here
</syntaxhighlight>
|-
|<syntaxhighlight lang="sql">
SELECT age FROM users WHERE id = 1;
-- READ UNCOMMITTED retrieves 21 (dirty read)
-- READ COMMITTED retrieves 20 (dirty read has been avoided)
-- REPEATABLE READ retrieves 20 (dirty read has been avoided)
-- SERIALIZABLE retrieves 20 (dirty read has been avoided)
COMMIT;
</syntaxhighlight>
|
|-
|
|<syntaxhighlight lang="sql">
ROLLBACK;
</syntaxhighlight>
|}

===Non-repeatable reads===
A ''non-repeatable read'' occurs when a transaction retrieves a row twice and that row is updated by another transaction that is committed in between.

In this example, transaction 1 retrieves the row with id 1, then transaction 2 updates the row with id 1 and is committed, and finally transaction 1 retrieves the row with id 1 again. At the READ UNCOMMITTED and READ COMMITTED isolation levels, the second SELECT in transaction 1 retrieves the updated row: this is a non-repeatable read. At the REPEATABLE READ and SERIALIZABLE isolation levels, the second SELECT in transaction 1 retrieves the initial row.

{|
|-
! Transaction 1
! Transaction 2
|-
|<syntaxhighlight lang="sql">
BEGIN;
SELECT age FROM users WHERE id = 1;
-- retrieves 20
</syntaxhighlight>
|
|-
|
|<syntaxhighlight lang="sql">
BEGIN;
UPDATE users SET age = 21 WHERE id = 1;
COMMIT;
</syntaxhighlight>
|-
|<syntaxhighlight lang="sql">
SELECT age FROM users WHERE id = 1;
-- READ UNCOMMITTED retrieves 21 (non-repeatable read)
-- READ COMMITTED retrieves 21 (non-repeatable read)
-- REPEATABLE READ retrieves 20 (non-repeatable read has been avoided)
-- SERIALIZABLE retrieves 20 (non-repeatable read has been avoided)
COMMIT;
</syntaxhighlight>
|}

===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.