15.00 - SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL

Definition of Isolation Level

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) Note that isolation level is a concept related to concurrently running transactions and how well their updates are protected from one another as a system processes their respective transactions. Also note that the definition for isolation used in the original definition of the ACID properties of transactions (see SQL Request and Transaction Processing) 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 various 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 will have read 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, Teradata Database might return the updated value or the row might have been deleted.

Phantom Read

P3

Transaction t1 reads a set of rows n that satisfy some search condition.

Transaction t2 then executes 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, Teradata 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

A footnote to this table in the ANSI SQL:2011 standard notes the following: “The exclusion of these phenomena for SQL‑transactions executing at isolation level SERIALIZABLE is a consequence of the requirement that such transactions be serializable.” While this statement borders on being a tautology, it can, and should, also be read as indicating that any critical database transactions should always be run under an isolation level of SERIALIZABLE.