Editing Isolation (database systems) (section)

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