SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL | Teradata Vantage - Definition of Isolation Level - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
imq1591724555718.ditamap
dita:ditavalPath
imq1591724555718.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

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 Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142) 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, the 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, 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

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.