The ANSI SQL:2011 standard defines isolation level as follows: "The isolation level of an SQL-transaction defines the degree to which the operations on SQL-data or schemas in that SQL-transaction are affected by the effects of and can affect operations on SQL-data or schemas in concurrent SQL-transactions" (International Standard ISO/IEC 9075-2:2011(E), Part 2: Foundation (SQL/Foundation), 2011, pages 133 and 134). Isolation level is a concept related to concurrently running transactions and how well their updates are protected from each other as a system processes their respective transactions. The definition for isolation used in the original definition of the ACID properties of transactions (see Database Transactions) is synonymous with the definition for serializable, so it does not allow different levels of isolation for transactions: a transaction is either isolated or it is not isolated.
The standard defines three different phenomena that have different characteristics for different transaction isolation levels. These phenomena are defined in the following table, which is adapted from a list in the ANSI SQL:2011 standard (International Standard ISO/IEC 9075-2:2011(E), Part 2: Foundation (SQL/Foundation), 2011, pages 134 and 135).
| Phenomenon | Abbreviation | Description |
|---|---|---|
| Dirty Read | P1 | Transaction t1 modifies a row. Transaction t2 then reads that row before t1 performs a COMMIT request. If t1 then performs a ROLLBACK request, t2 reads a row that was never committed, so that row can be considered never to have existed. |
| Non-Repeatable Read | P2 | Transaction t1 reads a row. Transaction t2 then updates or deletes that row and performs a COMMIT request. If t1 then attempts to read the row again, the database may return the updated value or the row may have been deleted. |
| Phantom Read | P3 | Transaction t1 reads a set of rows n that satisfy a search condition. Transaction t2 then runs SQL requests that generate one or more rows that also satisfy the search condition used by transaction t1. If transaction t1 then repeats its initial read using the same search condition, the database returns a different set of rows. |
The following table, which is also adapted from the ANSI standard (Table 8, International Standard ISO/IEC 9075-2:2011(E), Part 2: Foundation (SQL/Foundation), 2011, pages 134 and 135), explains how the behavior of these phenomena differs depending on the transaction isolation level.
| Isolation Level | P1 | P2 | P3 |
|---|---|---|---|
| READ UNCOMMITTED | possible | possible | possible |
| SERIALIZABLE | not possible | not possible | not possible |
Footnote to this table in the ANSI SQL:2011 standard: "The exclusion of these phenomena for SQL-transactions running at isolation level SERIALIZABLE is a consequence of the requirement that such transactions be serializable." Therefore, run critical database transactions under an isolation level of SERIALIZABLE.