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. 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 Vantage.
- 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.