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, Teradata Database 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.