How T‑TDSQL Implements Dual‑Temporal Data Management in Distributed Environments

Full‑State Data Reading

Three read scenarios are supported:

Read historical state from a dedicated history table.

Read current and transitional state from the user table using the existing MVCC mechanism.

Read full‑state data (both current and historical) by combining the user and history tables. Mainstream DBMSs do not provide this capability.

History Table Creation

The CREATE TABLE statement is extended with the SYSTEM_VERSIONING keyword. When a table is created with this option, a history table named $<em>user_table_name</em>_history is automatically generated with a schema identical to the user table.

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (
    column_definitions,
    ...
) [table_options] [partition_options] [SYSTEM_VERSIONING];

Snapshot Read Types

Historical snapshot diff read : given two snapshots, return the historical rows that changed between them.

Current snapshot read : uses standard MVCC to read the current and transitional rows.

Historical snapshot read : given a past transaction snapshot, return the full state from that snapshot up to the present.

The user table and its history table are treated as a single logical table, enabling the three read modes.

Query Syntax

Full‑state SELECT statements can include a READVIEW clause to specify the snapshot range:

SELECT select_expr[, select_expr...] FROM tbl_name
    [READVIEW START start_view TO end_view]
    [TYPE {INSERT|UPDATE|DELETE|ALL}];

READVIEW START s1 TO s2

– historical snapshot diff read.

Omit READVIEW – current snapshot read. READVIEW START s1 – historical snapshot read.

Transaction‑time queries follow the SQL:2011 SYSTEM_TIME syntax:

SELECT select_expr FROM tbl_name
    [SYSTEM_TIME AS OF ts]
    [SYSTEM_TIME FROM ts1 TO ts2]
    [SYSTEM_TIME BETWEEN ts1 AND ts2]
    [WHERE ...];

Transaction Snapshot

A snapshot records the state of all active transactions at creation time. It contains:

upperBound : maximum active transaction ID; IDs greater than this are invisible.

lowerBound : minimum active transaction ID; IDs smaller than this are visible.

trxCreator : the ID of the transaction that created the snapshot.

createTime : timestamp of snapshot creation.

Full‑State Visibility Algorithm

The algorithm combines historical and current visibility checks. For each tuple r in the result set R, it invokes HISTORY_VISIBILITY_JUDGEMENT with the start and stop snapshots. If the tuple is visible, it is added to the output set; otherwise the algorithm walks the version chain of r to find a visible historical version.

function FULL_STATE_VISIBILITY_JUDGEMENT(R, s_start, s_stop)
    S ← ∅
    for r in R do
        if HISTORY_VISIBILITY_JUDGEMENT(r, s_start, s_stop) then
            Add(r, S)
        while r = r.prev() do
            if HISTORY_VISIBILITY_JUDGEMENT(r, s_start, s_stop) then
                Add(r, S)
    return S

Transaction State Management

State Record Structure

Each transaction occupies a 16‑byte record storing:

status (2 bytes) : one of "not started", "running", "committed", "rolled back".

begin_time (7 bytes) : start timestamp.

end_time (7 bytes) : end timestamp (null for running transactions).

Transaction IDs are derived from the page ID and offset within the state page:

trx_id = page_id * num_per_page + offsets / info_record_size

where

num_per_page = page_size / info_record_size
page_id = trx_id / num_per_page
offsets = (trx_id % num_per_page) * info_record_size

Optimizing Historical Reads

The transaction‑state log enables fast visibility checks: if a version’s transaction ID refers to a transaction that is still uncommitted, the version is invisible; otherwise the normal MVCC visibility rules apply.

Utility Functions

TRXTOTIME(trx_id, x, y)

– returns transactions whose IDs lie in [trx_id‑x, trx_id+y]. TIMETOTRX(time_value, x, y) – returns transactions that ended within [time_value‑x, time_value+y] seconds. GETTRXID() – returns the current maximum allocated transaction ID.

Index Design for History Tables

Creation

The CREATE TABLE syntax can specify a primary key for the history table via SYSTEMVERSIONING:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (
    column_definitions,
    ...
) [SYSTEMVERSIONING history_pk_expr];

history_pk_expr

may differ from the user table’s primary key and typically includes the transaction ID.

Index Structure

A B+‑tree index is built on the history table. Leaf pages store rows clustered by the chosen primary key, enabling efficient point and range queries. For example, a composite key (user_id, trx_id) accelerates snapshot‑diff queries.

Valid‑Time Management (SQL:2011)

Period Definition

The CREATE TABLE statement can include a PERIOD FOR clause to define a valid‑time period:

CREATE TABLE tbl_name (
    column_definitions,
    ...,
    PERIOD FOR <em>period_name</em> (valid_from, valid_to)
) ...;

Valid‑Time Queries

Queries can filter rows by valid time using the PERIOD keyword:

SELECT ... FROM tbl_name
    [WHERE ...]
    [PERIOD <em>period_name</em> AS OF ts]
    [PERIOD <em>period_name</em> FROM ts1 TO ts2]
    [PERIOD <em>period_name</em> BETWEEN ts1 AND ts2];

AS OF ts

– rows valid at the exact timestamp. FROM ts1 TO ts2 – rows whose valid‑time interval fully covers the range. BETWEEN ts1 AND ts2 – rows whose valid‑time interval overlaps the range.

Valid‑Time Updates

Updates can target a specific valid‑time interval:

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    PERIOD <em>period_name</em> FROM ts_start TO ts_end
    SET column = value, ...;

Illustrative figures (omitted here) show how rows are split or merged across time intervals.

Valid‑Time Deletes

Deletes can also be scoped to a valid‑time window:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    PERIOD <em>period_name</em> FROM ts_start TO ts_end;

Figures (omitted) demonstrate row removal within the specified temporal range.