SQL-92 - Four Isolation Levels:

Overview
While creating or reusing an existing acess method, it is required to know the degree of isolation supported between processes attempting to access the same data. This is commonly defined by referring to the SQL-92 Isolation levels.

SQL-92 defines four Isolation Levels:

• Read Uncommitted
• Read Committed
• Repeatable Read
• Serializable

Isolations levels are stated in terms of three prohibited operation sequences, called Phenomena:

1. Dirty Read
2. Non-Repeatable Read
3. Phantom Read

If an access method does not support Serializable, then it is possible that the data set qualifying for a particular query may change within a transaction because of an update by another transaction. If this can occur, then it is very important for user to know what level is supported, so that they may design their applications accordingly. Phenomena define the ways in which the qualifying data set may change in the transaction.

---

Dirty Read

This occurs if one transaction can see the results of the actions of another transaction before it commits. Consider the following example:

Transaction 1	Write(a)		Rollback
Transaction 2		Read(a)

If Transaction 2 reads the value written by Transaction 1, then a DIRTY READ has occurred.

---

Non-Repeatable Read (also called Fuzzy Read)

This occurs if the results of one transactions can be modified or deleted by another transaction before it commits. This is illustrated below:

Transaction 1	Read(a)			Read(a)
Transaction 2		Write/Delete(a)	Commit

If Transaction 1 gets a different result from the each Read, then a NON-REPEATABLE READ has occurred

---

Phantom Read

This occurs if the results of a query in one transaction can be changed by another transaction before it commits. This is illustrated below:

Transaction 1	Select(criteria)			Select(criteria)
Transaction 2		Update/Create(match to criteria)	Commit

If Transaction 1 gets a different result from each Select, then a PHANTOM READ has occurred

---

SQL-92 Isolation Levels Defined

The SQL-92 Isolation levels are defined in terms of what Phenomena can occur:

Isolation Level	P1	P2	P3
Read Uncommitted	YES	YES	YES
Read Committed	NO	YES	YES
Repeatable Read	NO	NO	YES
Serializable	NO	NO	NO

Thus if Read Committed is supported you will never experience a DIRTY READ, but you might experience a NON-REPEATABLE READ or a PHANTOM READ.
The above order is in terms of "strength" in that Read Uncommitted is less restrictive, and thus less "strong" or at a "lower level" (of isolation) than Read Committed.