Examples | Concurrency Issues with Temporal DML | Teradata Vantage - 17.10 - Examples - 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)
Time and date values are exaggerated to make the sequence of events clearer.

Assume the following two tables describe parts and orders:

Parts table:

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

Orders table:

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

Consider the following two transactions, that are to be applied to the tables:

  • Increase Order

    If the current discount for part P1 is greater than or equal to 10%, double the order quantity.

    This transaction modifies the order table based on the parts table:

    CURRENT VALIDTIME
    UPDATE Orders
    FROM Parts
    SET Quantity= quantity*2
    WHERE discount >= 10 AND Orders.part_id = Parts.part_id;
  • Reduce Discount

    If the current order quantity for P1 is less than 100, reduce the discount by one half.

    This transaction modifies the parts table based on the order table

    CURRENT VALIDTIME
    UPDATE Parts
    FROM Orders
    SET discount = discount/2.0
    WHERE quantity < 100 AND Parts.part_id = Orders.part_id ;

Assuming the transactions execute serially, the results will depend on which transaction executes first. This is demonstrated by the first two examples below.