17.10 - Transaction Isolation - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1182-171K
Language
English (United States)

Isolation is one of the ACID properties of database transaction processing. Ideally, “concurrent” transactions are serializable, each occurring in isolation from the others, the results of each being independent of any changes resulting from the others. Normally, Vantage guarantees serializable transactions when the transaction isolation level is set to SERIALIZABLE for the transaction or for the session.

However, under some circumstances, CURRENT VALIDTIME selections from temporal tables can violate serializability in temporal transactions, even when the transaction isolation level is set to SERIALIZABLE. This can happen because of the interaction of two characteristics of temporal transactions:
  • When rows in temporal tables are changed, they usually generate history rows, which preserve the state of the row as it existed before the change occurred, and which are stored in the same table.
  • A CURRENT temporal qualifier selects rows based on the TEMPORAL_DATE or TEMPORAL_TIMESTAMP, which is defined as the clock time of the first access to the temporal table, or the first access of the transaction to the TEMPORAL_DATE or TEMPORAL_TIMESTAMP function.

This section illustrates the issues, and describes techniques that can be used to ensure serializable temporal transactions.