Examples | Concurrency Issues with Temporal DML | Teradata Vantage - 17.05 - Examples - Teradata Database

Teradata Vantage™ - Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1182-170K
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.