Transaction Isolation Levels

Dirty Read
A dirty read occurs if a one transaction reads changes made by another transaction
that has not yet been committed. This is dangerous, because the changes made by
the other transaction may later be rolled back, and invalid data may be written by
the first transaction, as shown in the figure



Non-repeatable Read
An unrepeatable read occurs if a transaction reads a row twice and reads different
state each time. For example, another transaction may have written to the row
and committed between the two reads as shown in the figure



Phantom Read
A phantom read is said to occur when a transaction executes a query twice, and
the second result set includes rows that weren’t visible in the first result set or rows
that have been deleted. (It need not necessarily be exactly the same query.) This
situation is caused by another transaction inserting or deleting rows between the
execution of the two queries as shown in the figure


Lost update
A lost update occurs if two transactions both update a row and then the second
transaction aborts, causing both changes to be lost. This occurs in systems that
don’t implement locking.


Transaction Isolation Levels

Read Uncommitted
A system that permits dirty reads but not lost updates is said to operate in
read uncommitted isolation. One transaction may not write to a row if another
uncommitted transaction has already written to it. Any transaction may read
any row, however.

Read Committed
A system that permits unrepeatable reads but not dirty reads is said to implement
read committed transaction isolation. This may be achieved by using
shared read locks and exclusive write locks. Reading transactions don’t
block other transactions from accessing a row. However, an uncommitted
writing transaction blocks all other transactions from accessing the row.

Repeatable Read
A system operating in repeatable read isolation mode permits neither unrepeatable
reads nor dirty reads. Phantom reads may occur. Reading transactions
block writing transactions (but not other reading transactions), and
writing transactions block all other transactions.

Serializable
Serializable provides the strictest transaction isolation. This isolation level
emulates serial transaction execution, as if transactions were executed one
after another, serially, rather than concurrently. Serializability may not be
implemented using only row-level locks. There must instead be some other
mechanism that prevents a newly inserted row from becoming visible to a
transaction that has already executed a query that would return the row.