17.10 - Example: Increase Order and Reduce Discount occur concurrently - 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)

Assume the individual transactions have the following characteristics:

Times Increase Order Transaction Reduce Discount Transaction
Begin Time 2009-01-02 2009-01-04
Modification Time 2009-01-07 2009-01-05
End Time 2009-01-08 2009-01-06

Notice that Reduce Discount begins and ends during the time Increase Order is running.

The following shows the states of the Parts and Orders tables after transactions have completed.

Parts Table after the Reduce Discount transaction:

Part_ID Supplier_ID Price Discount Part_Validity
P1 S1 $10 10% (2008-01-01, 2009-01-04)
P1 S1 $10 5% (2009-01-04, 2011-01-01)

In the original state of the tables, the P1 rows qualified for the CURRENT transaction because their valid times overlapped TEMPORAL_DATE.

Additionally, the P1 row in the Parts table was modified, because the P1 row in the Orders table at that current time (2009-01-04) fulfilled the transaction WHERE test (quantity < 100).

The modification of the P1 row in the Parts table leaves a history row showing the state of the row prior to the modification. Notice the end time of the first row and the beginning time of the second row have both been timestamped with TEMPORAL_DATE at the time of the modification.

Orders table after Increase Order transaction:

Order_ID Part_ID Quantity Order_Validity
O1 P1 60 (2008-01-01, 2009-01-02)
O1 P1 120 (2009-01-02, 2011-01-01)

The original P1 row in the Orders table qualified for the CURRENT transaction because its valid time (2008-01-01, 2011-01-01)overlapped TEMPORAL_DATE for the Increase Order transaction (2009-01-02).

Similarly, the original row in the Parts table qualified for the CURRENT transaction because its valid time (2008-01-01, 2009-01-04) overlapped TEMPORAL_DATE for the Increase Order transaction (2009-01-02). The row also fulfilled the WHERE test (discount >= 10), so the order quantity was increased, resulting in a history row in the Orders table showing the state of the row prior to the modification.

In this case, the final state of the database tables does not match either of the cases where the transactions occurred serially, so the transaction isolation principle of ACID has been violated.