Start of change

System-period temporal table timestamp value conflicts

The database manager will prevent a historical row of a system-period temporal table from being generated with a row-end timestamp that is less than its row-begin timestamp. This type of conflict can happen when multiple transactions have updated the same row of a system-period temporal table.

An example scenario is shown in the table below. The timestamps for this example have been simplified as a place holder instead of a sample system clock reading.

Table 1. Concurrent transactions on a system-period temporal table
Current Timestamp Transaction A Trans A SYS_START value Transaction B Trans B SYS_START value
T1 
INSERT INTO 
 policy_info (policy_id, 
              coverage) 
 VALUES ('S777',7000)
 T1    
T2     
INSERT INTO 
 policy_info (policy_id, 
              coverage) 
 VALUES ('T888',8000)
 T2
T3     
COMMIT
 T2
T4 
UPDATE policy_info 
  SET policy_id = 'X999'
  WHERE policy_id = 'T888'
 T1    
T5 
COMMIT
 T1    

When multiple rows are inserted or updated in the same transaction, the row-begin column is the same for all impacted rows. The value for the row-begin comes from a reading of the system clock at the moment of the first data change in the transaction. In the table above, all rows inserted or updated by transaction A have a row-begin timestamp of T1, and the row inserted by transaction B has a row-begin timestamp of T2. At time T3, the insert of row two is committed with a row-begin timestamp of T2.

When transaction A updates the row where the POLICY_ID is equal to 'T888' at time T4, the updated row's row-begin timestamp is changed to T1 because the update occurred in transaction A. The original row is inserted into the history table, and the value of the row-end timestamp for the historical row is updated to match the updated row's row-begin timestamp value, T1. The row-begin value of the historical row is T2. This would result in a historical row with a row-end timestamp value, T1, that is earlier than its row-begin timestamp value, T2. This is not allowed.

The SYSTIME_PERIOD_ADJ QAQQINI option determines what action the system takes when a historical row has a row-end timestamp that is less than the row-begin timestamp. The QAQQINI value can be set to either *ERROR or *ADJUST, with *ERROR being the default value. When *ERROR is used, an error with SQLCODE -20528 and SQLSTATE 57062 will be returned when the row-end timestamp value is less that the row-begin timestamp value. When *ADJUST is used, an adjustment is made to the value of the row-begin timestamp and the row-end timestamp instead of returning an error. In such cases, the values in the row begin column and the transaction start-ID column will differ. For more information on the SYSTIME_PERIOD_ADJ QAQQINI option, see Performance and query optimization.

Another example of when this situation may occur is when the system clock is adjusted. This could happen when changing the time to account for daylight savings time. As in the previous case, the value of the QAQQINI option would determine whether an error is returned or if the timestamp values are adjusted.

End of change