15.00 - Examples - Teradata Database

Teradata Database Temporal Table Support

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1182-015K

Examples

Note: 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.

    Example : Increase Order occurs before Reduce Discount

    Assume the individual transactions have the following characteristics:

     

    Times

    Increase Order Transaction

    Reduce Discount Transaction

    Begin Time

    2009-01-02

    2009-01-09

    Modification Time

    2009-01-07

    2009-01-10

    End Time

    2009-01-08

    2009-01-11

  • Begin Time is the value of TEMPORAL_DATE for the example transactions. This time is used both to qualify rows for participation in the transaction, and to timestamp the modified rows.
  • Modification Time is the time when the modification is made by the transaction.
  • End Time is when the transaction is committed and completed.
  • Notice that Increase Order ends before Reduce Discount begins.

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

    Orders table state after the 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)

    In the original state of the tables, the P1 rows qualified for the CURRENT transaction because their valid times overlapped current time, value of TEMPORAL_DATE at the time when the transaction started, shown as Begin Time in the table of transaction characteristics. The period (2008-01-01, 2011-01-01) overlaps 2009-01-02.

    Additionally, the P1 row in the Orders table was modified, because the P1 row in the Parts table fulfilled the transaction WHERE test (discount >= 10).

    The modification of the P1 row in the Orders 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.

    Parts Table state after the Reduce Discount transaction:

     

    Part_ID

    Supplier_ID

    Price

    Discount

    Part_Validity

    P1

    S1

    $10

    10%

    (2008-01-01, 2011-01-01)

    The P1 row was not changed, because the current time at the time of the Reduce Discount transaction was 2009-01-09. Using this current time, only the second row in the Orders table qualifies for the transaction, however that row fails the WHERE test (quantity < 100), due to the changes made by the preceding Increase Order transaction.

    Example : Reduce Discount occurs before Increase Order

    Assume the individual transactions have the following characteristics:

     

    Times

    Increase Order Transaction

    Reduce Discount Transaction

    Begin Time

    2009-01-05

    2009-01-02

    Modification Time

    2009-01-10

    2009-01-03

    End Time

    2009-01-11

    2009-01-04

    Notice that Reduce Discount ends before Increase Order begins.

    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-02)

    P1

    S1

    $10

    5%

    (2009-01-02, 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 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, 2011-01-01)

    The row was not changed, because the current time at the time of the Increase Order transaction was 2009-01-05. Using this current time, only the second row in the Parts table qualifies for the transaction, however that row fails the WHERE test (discount >= 10), due to the changes made by the preceding Reduce Discount transaction.

    Example : Increase Order and Reduce Discount occur concurrently

    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.

    Recommendations

    The following techniques can be used to avoid potential serializability issues with CURRENT temporal transaction concurrency.

  • Do not concurrently run multiple applications or transactions that are likely to read or modify the same set of rows using at least one CURRENT VALIDTIME temporal SQL statement. Run these applications and transactions only sequentially, one after the other.
  • Apply table level locks preemptively on temporal tables that are to be modified. These locks must be applied at the beginning of the transaction, which requires a BT/ET transaction or an ANSI transaction that uses a “LOCKING TABLE FOR WRITE” qualifier before any non-locking SQL is issued.
  • Note: It is not sufficient for the operation itself to apply a table-level lock, because the timestamp value for qualification may be earlier than the actual acquisition of the lock on the temporal table.

  • Use the SEQUENCED VALIDTIME temporal qualifier with an explicit PA in the modification SQL rather than CURRENT VALIDTIME:
  • SEQUENCED VALIDTIME PERIOD (TEMPORAL_DATE/TEMPORAL_TIMESTAMP, UNTIL_CHANGED)

    Be aware of the following restrictions on this technique:

  • A variable PA cannot be specified at a session level. Therefore, the SEQUENCED VALIDTIME qualifier with PA must be mentioned at the statement level. This can require modifications to applications that interact with Teradata Database.
  • Only equality inner joins are supported with the SEQUENCED VALIDTIME qualifier. Therefore applications that use other forms of joins must use one of the other options for avoiding concurrency issues.
  • Special Case: Modifying the Same Row

    Teradata Database can detect and abort the special case where concurrent CURRENT temporal transactions attempt to modify the same row of a temporal table at the same time. This capability must be enabled by Teradata Support. If this capability meets the needs of your situation, contact your Teradata Support representative.

    Related Information

     

    For more information on...

    See...

    Transaction isolation levels and ACID

    SQL Request and Transaction Processing.

    Timestamping temporal transactions

    “Timestamping” on page 27.