Temporal Tableのレコードはいつから有効か？ - トランザクション開始時刻とcommit時刻との違いについて -

ENECHANGE所属のエンジニア id:tetsushi_fukabori こと深堀です。
花粉症の大変厳しい季節、皆様いかがお過ごしでしょうか。
私は「あー」か「うー」しか発することができない生き物として日々を過ごしています。
スギ花粉の飛散を抑制する研究が一日でも早く結実することを願いこのブログを書いています。

いわゆるミッションクリティカルなシステムの構築に携わった皆様であれば百も承知かと思われますが、システムにおいてトレーサビリティは大変むずかしい課題です。
特に処理とデータのトレーサビリティ、つまり「この処理はどの状態のデータにアクセスしたか」は難しい課題かと思います。
今回はTemporal TableというRDBMSの機能を使うことでデータのトレーサビリティを構築することを検討し、その中で気がついたRDBMSの振る舞いの違いについて解説します。

この記事を届けたい人

Temporal Tableについて概要を知りたい人
Temporal Tableを提供するRDBMSを選定したい人

この記事を届けたい人
Temporal Tableとは何か？
RDBMSによる実装
Temporal Tableのレコードは「いつ」有効になるか？
実験
なぜPostgreSQLはトランザクション開始時刻を使うのか？
トランザクション開始時刻とcommit時刻との比較
まとめ

Temporal Tableとは何か？

今回の検証ではTemporal Tableという機能を使い、データの状態を時刻断面でトレース可能にすることを考えました。
Temporal TableはSQL:2011（ISO/IEC 9075:2011）で規定されたSQLの機能で、タイムスタンプと紐づけてテーブルの履歴を残す方式を指し、これにより「この処理時点ではテーブルはどういう状態だったか」を後追いできるようになります。

もう少し具体的には、Temporal Tableにおいてはレコードは有効期間を持ち、最新の有効期間（終了日時が未定義）のレコードを現在のレコード状態とし、過去の状態を有効期限切れのレコードとして保持します。
レコードの既存のキーに加えて有効期間がキーとして加わったものを想像すると良いかもしれません。
このようなデータ構造を持つため、CRUD操作はTemporal Table以外とは異なったものになります。

レコードのINSERTは現在時刻から開始する有効期限付き（終了日時未定義）のレコードを作成します
レコードに対するSELECTは条件に有効期間の指定が入ります（未指定の場合、暗黙的に最新の有効レコードへのSELECTとなるようです）
レコードに対するUPDATEは「UPDATE対象レコードの有効期間終了（UPDATE）」+「UPDATE後相当の最新有効レコードの挿入（INSERT）」によって行われます。
レコードに対するDELETEは「DELETE対象レコードの有効期間終了（UPDATE）」によって行われます。

この有効期間レコードをいくつもつかでuni-temporal, bi-temporal, tri-temporalまで方式が分かれますが、この記事では簡単のためすべてuni-temporal、つまり単一の有効期間カラムを持つものとします。

トレーサビリティの文脈では、ある処理が参照するデータの一貫性を「全てのSELECTクエリで同一時刻Tを指定する」ことで担保する方式が考えられます。
具体的には、アプリケーションが処理開始時に時刻Tを記録し、その処理内のすべての読み取りを時刻T時点基準で行います。
これにより、処理全体を通じて時刻T時点のデータ断面を一貫して参照でき、「この処理がどの状態のデータにアクセスしたか」を時刻Tという単一の値で追跡可能になります。

RDBMSによる実装

2011年12月に公開されたSQLの標準仕様なので、この機能は全RDBMSが実装している…かと思いきや、実は全然そんな事はありません。
このような標準はあくまで標準仕様であり、それを機能として実装するかどうかは全てRDBMSの開発者に委ねられているようです。
有名どころの対応状況は以下のとおりです。

PostgreSQL

ネイティブサポートはありません。
Application Timeについては以下の対応がされていますが、これらはいずれもユーザーが期間を管理するapplication-time機能であり、DBが自動的に行の有効期間を追跡するTemporal Tableとは別物です。

PostgreSQL 17でWITHOUT OVERLAPSによるtemporal primary key/unique制約
PostgreSQL 18でPERIODによるtemporal foreign keyが追加

Temporal Tableを使いたい場合は、サードパーティの拡張に頼ることになります。
代表的なものは以下の2つです。

arkhipov/temporal_tables: C言語で実装されたPostgreSQL拡張で、system-period data versioningを提供します。ただしC拡張であるため、AWS RDS/Aurora、Google Cloud SQL、Azure Database for PostgreSQLなどのマネージドDB環境ではインストールできません。
nearform/temporal_tables: arkhipovの実装をPL/pgSQLで書き直したもので、C拡張が許可されないマネージド環境を主なターゲットとしています。

MySQL

MySQL（Oracle社が開発する本家）は、現時点でSQL:2011のTemporal Tableをサポートしていません。
MySQL Bug Trackerには機能リクエスト（Bug #99490）が存在し、Tencent社からの実装パッチの投稿もありましたが、特に進展はないようです。

MariaDB

MariaDB 10.3でSystem Versioned Table（WITH SYSTEM VERSIONING構文、FOR SYSTEM_TIMEによるクエリ）がネイティブサポートされました。
MariaDBはMySQLからフォークしたRDBMSですが、Temporal Tableの対応状況はMySQLとは大きく異なります。
MariaDB 10.4.3ではapplication-time periodもサポートされ、両者を組み合わせたbi-temporal tableも利用可能です。

Oracleなどその他のRDBMS

Wikipediaのまとめによると、主要なRDBMSの対応状況は以下のとおりです。

IBM DB2: バージョン10で「Time Travel Queries」として、SQL:2011準拠の最初の実装を謳っています。
Oracle: 12cでSQL:2011準拠のTemporal Table機能をサポートしています。ただし、Oracle 9i/10g/11gの「Flashback Query」はrollback segmentに依存しており、保持期間内の最近の変更に対してのみtemporal queryが可能という制約があります。
Microsoft SQL Server: 2016からTemporal Tableを実装しています。
SAP HANA: 2.0 SP03でTemporal Tableをサポート、2.0 SP04でapplication-time versioningを部分的にサポートしています。
CockroachDB: v1.0.7以降でAS OF SYSTEM TIMEクエリをサポートしています。

Temporal Tableのレコードは「いつ」有効になるか？

さて、上記の通りTemporal Tableのレコードは最新のものと履歴のものに分かれることになりますが、ここで「最新のレコードはいつから有効になるのか？」が疑問として浮かびます。
つまり、時刻Tで有効な最新のレコードというのは、時刻Tまでに何が行われたレコードなのでしょうか？
もちろん答えはシンプルに時刻Tよりも過去の有効期間開始日時をもつ（かつ有効期間終了日時を持たない）です。
ですが、ではここでいう「日時」とはSQL操作における何のタイムスタンプのことでしょうか？

レコードの可視時刻

RDBMSにおいて、あるレコードが他のセッションから「見える」ようになるタイミングは、そのレコードを操作したトランザクションがCOMMITされた瞬間です。
たとえば、セッションAがINSERTを実行しても、そのトランザクションがCOMMITされるまでは、セッションBからSELECTしても当該レコードは見えません（Read Committed以上の分離レベルを想定しています）。
つまり、レコードの「可視時刻」とは、INSERT/UPDATE/DELETEといったSQL文を実行した時刻ではなく、それを含むトランザクションがCOMMITされた時刻ということになります。

長時間トランザクションにおけるレコードの可視時刻

ほとんどのケースではトランザクションは短時間で完了するため、トランザクション開始時刻とCOMMIT時刻はほぼ同じです。しかし、長時間トランザクションではこの2つの時刻に無視できない差が生じます。

たとえば、10:00にトランザクションを開始し、10:05にUPDATEを実行し、10:10にCOMMITしたとします。
このとき、他のセッションがこのUPDATEの結果を参照できるようになるのは10:10（commit時刻）です。10:00（トランザクション開始時刻）でも10:05（UPDATE実行時刻）でもありません。
つまり、レコードが他のセッションから「見える」ようになるのは、あくまでCOMMITが完了した瞬間であり、トランザクションの開始時刻やSQL文の実行時刻とは一致しません。

トランザクション開始時刻による有効化とcommit時刻による有効化

Temporal Tableにおいても、レコードの有効期間の開始日時として「いつの時刻を使うか」には選択肢があり、実はこの選択は実装によって異なります。

PostgreSQLの拡張（arkhipov/temporal_tablesおよびnearform/temporal_tables）は、トランザクション開始時刻（transaction_timestamp()）を使用します。
これはトランザクション開始時に決まる固定値で、トランザクション内のどのタイミングでUPDATEしても同じ時刻が記録されます。
つまり、先ほどの例では有効期間の開始日時として10:00が記録されます。

一方、MariaDBのSystem Versioned Tableはtransaction-precise versioning（trx_idモード）においてCOMMIT時の実時刻、commit時刻を使用します。
同じ例では有効期間の開始日時として10:10が記録されます。

前者は「レコードが他のセッションから実際に見えるようになった時刻」とは乖離し、後者は可視性の意味でより正確な時刻を記録します。
本当にこのような差異が出るのか、簡単に実験をしてみます。

実験

実験概要

Docker Compose上でPostgreSQL 16とMariaDB 11を並べて起動し、同一のテーブル・同一のシナリオで長時間トランザクションを実行します。
履歴行に記録されるタイムスタンプを比較し、前節で述べた挙動の違いを実際に確認します。さらに「ある時点のデータ」を取得するAS OFクエリの可視性が正しいかを検証します。

項目	PostgreSQL	MariaDB
イメージ	`postgres:16`	`mariadb:11`
Temporal Table実装	nearform/temporal_tables (PL/pgSQL)	ネイティブ (`WITH SYSTEM VERSIONING`, trx_idモード)

nearform/temporal_tablesはarkhipov/temporal_tables（C言語実装）のPL/pgSQL移植版であり、タイムスタンプの挙動は同一です。
AWS RDS等のマネージド環境ではC拡張をインストールできないため、今回はこちらを使用しています。

MariaDBではtransaction-precise versioning（trx_idモード）を使用しています。このモードではROW_START/ROW_ENDにタイムスタンプではなくトランザクションIDが記録され、FOR SYSTEM_TIME AS OFクエリ時にmysql.transaction_registryテーブルのcommit_timestampを基準にした可視性判定が行われます。

実験シナリオは以下の通りです。両DBに初期データ (1, 'Widget', 100) を挿入した上で実行します。

BEGIN
現在時刻を記録（T+0: トランザクション開始）
5秒間スリープ (pg_sleep(5) / SLEEP(5))
現在時刻を記録（T+5: UPDATE直前）
UPDATE products SET price = 200 WHERE id = 1
現在時刻を記録（T+5: UPDATE直後）
5秒間スリープ
現在時刻を記録（T+10: COMMIT直前）
COMMIT
現在時刻を記録（T+10: COMMIT直後）

UPDATEの前後にスリープを入れることで、「UPDATE実行時刻」と「COMMIT時刻」を明確に区別できるようにしています。
COMMIT後、記録した各時刻を使ってAS OFクエリを実行し、可視性が正しく制御されているかを検証します。

PostgreSQLではtransaction_timestamp()（トランザクション開始時に固定される時刻）とclock_timestamp()（呼び出し時点の壁時計時刻）の両方を記録し、トランザクション内で時刻がどう扱われるかも可視化します。

ソースコード

実験に使用したソースコード一式を以下に掲載します。

docker-compose.yml

services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_DB: testdb
      POSTGRES_USER: testuser
      POSTGRES_PASSWORD: testpass
    ports:
      - "15432:5432"
    volumes:
      - ./postgres/init:/docker-entrypoint-initdb.d
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U testuser -d testdb"]
      interval: 2s
      timeout: 5s
      retries: 10

  mariadb:
    image: mariadb:11
    environment:
      MARIADB_DATABASE: testdb
      MARIADB_USER: testuser
      MARIADB_PASSWORD: testpass
      MARIADB_ROOT_PASSWORD: rootpass
    ports:
      - "13306:3306"
    volumes:
      - ./mariadb/init:/docker-entrypoint-initdb.d
    healthcheck:
      test: ["CMD", "healthcheck.sh", "--connect", "--innodb_initialized"]
      interval: 2s
      timeout: 5s
      retries: 10

run-experiment.sh

#!/usr/bin/env bash
set -euo pipefail

PG="docker compose exec -T postgres"
MY="docker compose exec -T mariadb"

PGCMD="$PG psql -U testuser -d testdb -X"
MYCMD="$MY mariadb -u testuser -ptestpass testdb"

docker compose up -d --wait

# 初期データ挿入
$PGCMD -c "INSERT INTO products (id, name, price) VALUES (1, 'Widget', 100);"
$MYCMD -e "INSERT INTO products (id, name, price) VALUES (1, 'Widget', 100);"

# ============================================================
#  PostgreSQL: 長時間トランザクション
# ============================================================
# clock_timestamp()はwall-clock時刻（トランザクション中も進む）。
# 各フェーズのタイムスタンプをts_logテーブルに記録し、
# COMMIT後にAS OF相当のクエリで可視性を検証する。
$PGCMD <<'PGSQL'
BEGIN;
INSERT INTO ts_log VALUES ('tx_start', clock_timestamp());
SELECT transaction_timestamp() AS "tx_start (transaction_timestamp)";
SELECT clock_timestamp() AS "tx_start (clock_timestamp)";
SELECT pg_sleep(5);
INSERT INTO ts_log VALUES ('during_first_sleep', clock_timestamp());
SELECT clock_timestamp() AS "after_first_sleep (clock_timestamp)";
UPDATE products SET price = 200 WHERE id = 1;
INSERT INTO ts_log VALUES ('after_update', clock_timestamp());
SELECT transaction_timestamp() AS "after_update (transaction_timestamp)";
SELECT clock_timestamp() AS "after_update (clock_timestamp)";
SELECT pg_sleep(5);
INSERT INTO ts_log VALUES ('before_commit', clock_timestamp());
SELECT clock_timestamp() AS "before_commit (clock_timestamp)";
COMMIT;
PGSQL
$PGCMD -c "INSERT INTO ts_log VALUES ('after_commit', clock_timestamp());"

# 履歴テーブル確認
$PGCMD -c "SELECT id, name, price, lower(sys_period) AS period_start,
            upper(sys_period) AS period_end FROM products_history;"

# AS OF相当のクエリ（sys_periodの範囲述語で実現）
# nearform/temporal_tablesにはFOR SYSTEM_TIME AS OF構文がないため、
# 「時刻Tに有効だった行」を products と products_history の UNION ALL で取得する。
PG_TS_FIRST_SLEEP=$($PGCMD -t -A -c \
  "SELECT ts FROM ts_log WHERE phase = 'during_first_sleep';")
PG_TS_BEFORE_COMMIT=$($PGCMD -t -A -c \
  "SELECT ts FROM ts_log WHERE phase = 'before_commit';")
PG_TS_AFTER_COMMIT=$($PGCMD -t -A -c \
  "SELECT ts FROM ts_log WHERE phase = 'after_commit';")

echo "--- AS OF $PG_TS_FIRST_SLEEP (UPDATE前) ---"
$PGCMD -c "
  SELECT id, name, price, sys_period FROM products
  WHERE lower(sys_period) <= '$PG_TS_FIRST_SLEEP'::timestamptz
  UNION ALL
  SELECT id, name, price, sys_period FROM products_history
  WHERE lower(sys_period) <= '$PG_TS_FIRST_SLEEP'::timestamptz
    AND upper(sys_period) > '$PG_TS_FIRST_SLEEP'::timestamptz;"

echo "--- AS OF $PG_TS_BEFORE_COMMIT (COMMIT前) ---"
$PGCMD -c "
  SELECT id, name, price, sys_period FROM products
  WHERE lower(sys_period) <= '$PG_TS_BEFORE_COMMIT'::timestamptz
  UNION ALL
  SELECT id, name, price, sys_period FROM products_history
  WHERE lower(sys_period) <= '$PG_TS_BEFORE_COMMIT'::timestamptz
    AND upper(sys_period) > '$PG_TS_BEFORE_COMMIT'::timestamptz;"

echo "--- AS OF $PG_TS_AFTER_COMMIT (COMMIT後) ---"
$PGCMD -c "
  SELECT id, name, price, sys_period FROM products
  WHERE lower(sys_period) <= '$PG_TS_AFTER_COMMIT'::timestamptz
  UNION ALL
  SELECT id, name, price, sys_period FROM products_history
  WHERE lower(sys_period) <= '$PG_TS_AFTER_COMMIT'::timestamptz
    AND upper(sys_period) > '$PG_TS_AFTER_COMMIT'::timestamptz;"

# ============================================================
#  MariaDB: 長時間トランザクション
# ============================================================
# trx_idモード: ROW_START/ROW_ENDにはトランザクションIDが格納される。
# 各フェーズのタイムスタンプをts_logテーブルに記録し、
# COMMIT後にFOR SYSTEM_TIME AS OFクエリで可視性を検証する。
$MYCMD <<'MYSQL'
START TRANSACTION;
INSERT INTO ts_log VALUES ('tx_start', NOW(6));
SELECT NOW(6) AS 'tx_start (NOW)';
SELECT SLEEP(5);
INSERT INTO ts_log VALUES ('during_first_sleep', NOW(6));
SELECT NOW(6) AS 'after_first_sleep (NOW)';
UPDATE products SET price = 200 WHERE id = 1;
INSERT INTO ts_log VALUES ('after_update', NOW(6));
SELECT NOW(6) AS 'after_update (NOW)';
SELECT SLEEP(5);
INSERT INTO ts_log VALUES ('before_commit', NOW(6));
SELECT NOW(6) AS 'before_commit (NOW)';
COMMIT;
MYSQL
$MYCMD -e "INSERT INTO ts_log VALUES ('after_commit', NOW(6));"

# 履歴行 + transaction_registry（trx_id → タイムスタンプ変換）
# 実運用時はFOR SYSTEM_TIME AS OFがこの変換を内部的に行うため、
# ユーザーがtransaction_registryを直接参照する必要はない。
$MYCMD -e "
SELECT p.id, p.name, p.price,
       p.row_start AS start_trx_id, rs.commit_timestamp AS start_committed_at,
       p.row_end   AS end_trx_id,   re.commit_timestamp AS end_committed_at
FROM products FOR SYSTEM_TIME ALL AS p
LEFT JOIN mysql.transaction_registry rs ON p.row_start = rs.transaction_id
LEFT JOIN mysql.transaction_registry re ON p.row_end   = re.transaction_id
ORDER BY p.row_end;"

# FOR SYSTEM_TIME AS OFによる可視性検証
TS_FIRST_SLEEP=$($MYCMD -sN -e \
  "SELECT ts FROM ts_log WHERE phase = 'during_first_sleep';")
TS_BEFORE_COMMIT=$($MYCMD -sN -e \
  "SELECT ts FROM ts_log WHERE phase = 'before_commit';")
TS_AFTER_COMMIT=$($MYCMD -sN -e \
  "SELECT ts FROM ts_log WHERE phase = 'after_commit';")

echo "--- AS OF $TS_FIRST_SLEEP (UPDATE前) ---"
$MYCMD -e "SELECT * FROM products FOR SYSTEM_TIME AS OF TIMESTAMP '$TS_FIRST_SLEEP';"

echo "--- AS OF $TS_BEFORE_COMMIT (COMMIT前) ---"
$MYCMD -e "SELECT * FROM products FOR SYSTEM_TIME AS OF TIMESTAMP '$TS_BEFORE_COMMIT';"

echo "--- AS OF $TS_AFTER_COMMIT (COMMIT後) ---"
$MYCMD -e "SELECT * FROM products FOR SYSTEM_TIME AS OF TIMESTAMP '$TS_AFTER_COMMIT';"

docker compose down

PostgreSQL側のテーブル定義:

CREATE TABLE products (
    id    INT PRIMARY KEY,
    name  TEXT NOT NULL,
    price INT  NOT NULL,
    sys_period tstzrange NOT NULL DEFAULT tstzrange(CURRENT_TIMESTAMP, NULL)
);

CREATE TABLE products_history (LIKE products);

CREATE TRIGGER products_versioning
    BEFORE INSERT OR UPDATE OR DELETE ON products
    FOR EACH ROW EXECUTE PROCEDURE versioning('sys_period', 'products_history', true);

-- 各フェーズのタイムスタンプを記録するためのヘルパーテーブル（実験用）
CREATE TABLE ts_log (
    phase VARCHAR(50) PRIMARY KEY,
    ts    TIMESTAMPTZ NOT NULL
);

MariaDB側のテーブル定義:

-- Transaction-precise versioning (trx_idモード)
-- ROW_START/ROW_ENDにはタイムスタンプではなくトランザクションIDが格納される。
-- 実際のタイムスタンプはmysql.transaction_registryテーブルで管理され、
-- FOR SYSTEM_TIME AS OFクエリ時にcommit_timestampを基準にした可視性判定が行われる。
CREATE TABLE products (
    id        INT PRIMARY KEY,
    name      VARCHAR(255) NOT NULL,
    price     INT          NOT NULL,
    row_start BIGINT UNSIGNED GENERATED ALWAYS AS ROW START INVISIBLE,
    row_end   BIGINT UNSIGNED GENERATED ALWAYS AS ROW END INVISIBLE,
    PERIOD FOR SYSTEM_TIME(row_start, row_end)
) WITH SYSTEM VERSIONING;

-- 各フェーズのタイムスタンプを記録するためのヘルパーテーブル（実験用）
CREATE TABLE ts_log (
    phase VARCHAR(50) PRIMARY KEY,
    ts    DATETIME(6) NOT NULL
);

-- transaction_registryの参照権限を付与（実験用）
GRANT SELECT ON mysql.transaction_registry TO 'testuser'@'%';

PostgreSQLではメインテーブルと履歴テーブルを分けてトリガーで管理する必要があるのに対し、MariaDBではWITH SYSTEM VERSIONINGを付けるだけでシステムバージョニングが有効になります。
今回はcommit時刻に基づく可視性判定を得るため、ROW START/ROW ENDをBIGINT UNSIGNEDで宣言するtrx_idモードを使用しています。
デフォルトのTIMESTAMP(6)モードでは文の実行時刻が記録され、commit時刻ベースの可視性判定は行われません。

実験結果

PostgreSQL:

 tx_start (transaction_timestamp):     2026-03-31 01:49:43.875450+00   -- T+0
 tx_start (clock_timestamp):           2026-03-31 01:49:43.876126+00   -- T+0
 after_first_sleep (clock_timestamp):  2026-03-31 01:49:48.885891+00   -- T+5
 after_update (transaction_timestamp): 2026-03-31 01:49:43.875450+00   -- 変わらない
 after_update (clock_timestamp):       2026-03-31 01:49:48.896340+00   -- T+5
 before_commit (clock_timestamp):      2026-03-31 01:49:53.908057+00   -- T+10

--- History table ---
 id | name   | price | period_start                  | period_end
  1 | Widget |   100 | 2026-03-31 01:49:43.628630+00 | 2026-03-31 01:49:43.875450+00
                                                        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
                                                        = transaction_timestamp() (T+0)

transaction_timestamp()は10秒間のトランザクションを通じて01:49:43.875のまま変わりません。
履歴行のperiod_endにもこのトランザクション開始時刻が記録されています。

MariaDB:

 tx_start (NOW):          2026-03-31 01:49:55.334225   -- T+0
 after_first_sleep (NOW): 2026-03-31 01:50:00.340227   -- T+5
 after_update (NOW):      2026-03-31 01:50:00.345938   -- T+5
 before_commit (NOW):     2026-03-31 01:50:05.348557   -- T+10

--- History rows + transaction_registry ---
 id | name   | price | start_trx_id | start_committed_at          | end_trx_id | end_committed_at
  1 | Widget |   100 | 26           | 2026-03-31 01:49:43.750414  | 30         | 2026-03-31 01:50:05.348680
  1 | Widget |   200 | 30           | 2026-03-31 01:50:05.348680  | (max)      | NULL
                                                                     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
                                                                     = COMMIT時刻 (T+10)

trx_idモードではROW_START/ROW_ENDにトランザクションIDが格納されます。
mysql.transaction_registryをJOINすることで、commit_timestamp = 01:50:05.348（COMMIT時刻）であることが確認できます。

結果を比較します。

	PostgreSQL	MariaDB (trx_idモード)
履歴行の終了時刻	`01:49:43.875` (T+0)	`01:50:05.348` (T+10)
何の時刻か	トランザクション開始時刻	COMMIT時刻
UPDATEやスリープの影響	受けない	COMMIT時点まで遅延される

AS OFクエリによる可視性の検証:

COMMIT後、記録した各フェーズのタイムスタンプを使って「その時点のデータ」を取得します。
PostgreSQLではsys_periodの範囲述語で等価なクエリを構築し、MariaDBではFOR SYSTEM_TIME AS OFを使用します。

時点	壁時計時刻	PostgreSQL	MariaDB	あるべき値
UPDATE前（1回目のスリープ中）	T+5	price=200	price=100	price=100
COMMIT前（2回目のスリープ中）	T+10	price=200	price=100	price=100
COMMIT後	T+10	price=200	price=200	price=200

PostgreSQLでは3つの時点すべてでprice=200が返りました。
period_endにトランザクション開始時刻（T+0）が記録されているため、T+0以降の任意の時刻を指定すると、まだUPDATEすら実行されていない時点であっても更新後の値が見えてしまいます。

一方MariaDBのtrx_idモードでは、commit_timestamp（T+10）を基準に可視性が判定されるため、COMMIT前の時刻を指定した場合は旧値（price=100）が正しく返り、COMMIT後の時刻を指定した場合にのみ新値（price=200）が返りました。

これにより、前章で述べたタイムスタンプの違いがAS OFクエリの可視性に直接影響することが確認できました。
長時間トランザクションでは、PostgreSQLのtemporal_tables拡張は「いつから他のセッションに見えるようになったか」という観点で不正確な結果を返す一方、MariaDBのtrx_idモードはcommit時刻を基準とした正確な可視性判定を提供します。

なぜPostgreSQLはトランザクション開始時刻を使うのか？

実験により、PostgreSQLのtemporal_tables拡張はトランザクション開始時刻を、MariaDBはcommit時刻をタイムスタンプとして使うことが確認できました。
直感的には「レコードが他のセッションから見えるようになるのはCOMMITの瞬間なのだから、commit時刻を使う方が自然では？」と感じるかもしれません。
ではなぜPostgreSQLの拡張はトランザクション開始時刻を採用しているのでしょうか。

SQL:2011標準と"transaction timestamp"

SQL:2011標準はsystem-time periodの値として"transaction timestamp"を使うと規定しています（Kulkarni & Michels, 2012）。

INSERT into a system-versioned table automatically sets the value of Sys_start column to the transaction timestamp, a special value associated with every transaction, and...

# 自動翻訳
システムバージョン管理されたテーブルへのINSERTでは、Sys_start列の値がトランザクションタイムスタンプ（すべてのトランザクションに関連付けられた特別な値）に自動的に設定されます。

ただし、この"transaction timestamp"の具体的な値を何にするかは実装に委ねられています（Lu et al., 2019）。

Interestingly, SQL:2011 leaves the transaction time up to SQL-implementations to pick an appropriate value for the transaction timestamp of a transaction. While many temporal implementations in conventional DBMSs, either pick up the start time of the transaction, or the time of the operation that inserts/updates/deletes the record, we argue that this could potentially cause an incorrect result based on the snapshot isolation theorem.

# 自動翻訳
興味深いことに、SQL:2011ではトランザクションのタイムスタンプとして適切な値を選択するのはSQL実装に委ねられています。従来のDBMSにおける多くの時間的実装では、トランザクションの開始時刻、またはレコードの挿入/更新/削除操作の時刻のいずれかを選択しますが、スナップショット分離定理に基づくと、これは誤った結果を引き起こす可能性があると我々は主張します。

つまり、トランザクション開始時刻を使うかcommit時刻を使うかはRDBMSやその周辺ツールの作成者の解釈次第ということのようです。

PostgreSQLのtemporal_tables拡張はこれをCURRENT_TIMESTAMP（= transaction_timestamp()、トランザクション開始時に固定される値）と解釈しました。
C拡張の作者もIssue #13でこの設計判断について言及し、トランザクション開始時刻を使う主な理由はSQL:2011標準がそう定義しているためであると説明しています。
同Issueでは文実行時刻（statement start time）を使う案も議論されましたが、1トランザクション内の複数操作が異なるタイムスタンプを持つことになり実用的でないとして取り下げられています。一方MariaDB（transaction-precise versioning（trx_idモード））は同じ"transaction timestamp"をcommit時の実時刻と解釈しており、同じ標準を参照しながら異なる実装が生まれている状態です。

学術的な背景

この問題は学術的にも古くから議論されているようです。
Jensen & Snodgrass（2001）の論文 "Semantics of Time-Varying Information" では、Temporal Tableのタイムスタンプの付与タイミングについて以下のように整理されています。

タイムスタンプはできるだけ遅く（commit時に）付けるべきである。そうすればトランザクションのCOMMIT順序と時系列が整合する
トランザクション開始時刻の問題点は明確で、他のトランザクションからその変更が見えるのはcommit後なのに、記録される時刻はトランザクション開始時刻になる。長時間トランザクションほどこの乖離が大きくなる
ただしcommit時刻にも課題がある。トランザクション実行中の時点ではまだcommit時刻が確定していないため、トランザクション内部で「今の自分の時刻」を参照することができない

SQL:2011標準が"transaction timestamp"という曖昧な表現を採用した背景には、こうしたトレードオフの中で具体的な値の選択を実装側に委ねるという判断があったものと考えられます。

個人的な所感

SQL:2011標準が"transaction timestamp"の解釈を実装に委ねているということは、利用者から見ると同じ「SQL:2011準拠」を謳うRDBMSであってもタイムスタンプの挙動が異なりうることを意味します。
これはTemporal Tableを使ったシステムの設計においてかなり厄介な問題です。

Temporal Tableを導入する動機の多くは「ある時点でのデータの状態を正確に再現したい」というものでしょう。
そのとき「有効期間の開始時刻」が実際にレコードが見えるようになった時刻（commit時刻）と乖離するのは、利用者にとっては直感に反する振る舞いです。
特にトレーサビリティの観点、つまり「ある処理がどの状態のデータにアクセスしたか」を追跡する目的では、commit時刻でないと意味のある時刻にならないケースがあります。前述のように処理が時刻T時点のデータを参照する場合、Temporal Tableのタイムスタンプが可視性（commit時刻）と一致していなければ、時刻Tの時点では未コミットだったデータが時刻T時点を指定したクエリ結果に含まれてしまい、処理が実際にアクセスしえたデータと乖離します。

この点において、commit時刻を採用したMariaDBのtrx_idモードは実用的で、RDBMS選定の際にTemporal Tableの使い勝手を重視するのであれば、MariaDBのtrx_idモードのような仕様の方が選びやすいと考えています。

トランザクション開始時刻とcommit時刻との比較

ここまでの内容を踏まえて、両方式の特徴を整理します。

トランザクション開始時刻（PostgreSQL temporal_tables拡張の方式）

トランザクション内部からCURRENT_TIMESTAMPで参照でき、値が確定している
1トランザクション内の複数操作が同一の時刻を持つため、トランザクション内部の一貫性がある
他のセッションからレコードが実際に見えるようになる時刻（commit時刻）とは乖離する。長時間トランザクションほど乖離が大きくなる

commit時刻（MariaDBの方式）

他のセッションからの可視性と一致した時刻が記録される
COMMITの瞬間まで値が確定しないため、トランザクション内部から「今書き込んでいるレコードの有効期間開始時刻」を参照できない
RDBMS側でcommit時にタイムスタンプを付与する仕組みが必要で、トリガーベースの拡張では実現が難しい

commit時刻にも課題はありますが、RDBMS側の仕組みが有るのであれば、他はトランザクション内部の挙動に限った話です。
「ある時点でテーブルがどういう状態だったか」を後追いするというTemporal Tableの主な用途を考えると、トランザクション間での可視性と一致するcommit時刻方式の方が扱いやすい場面が多いのではないかと思います。

まとめ

データのトレーサビリティを確保する方法としてTemporal TableというSQL標準について説明し、実はRDBMSによってその実装や振る舞いが異なることを実験で説明しました。
データのトレーサビリティの確保を考えている開発者のみなさまの参考になればと思います。

ENECHANGE Developer Blog

ENECHANGE開発者ブログ