Aurora DSQL が外部キー制約をサポートしたので競合時の挙動を確認してみた
はじめに
2026年8月27日、Amazon Aurora DSQL が外部キー制約をサポートしました。
対象は新しく作るテーブルだけではありません。
Amazon Aurora DSQL now lets you add foreign key constraints to new and existing tables.
参照整合性のチェックをアプリからデータベースへ寄せられるようになりました。本記事では、外部キー制約付きのテーブルを並行操作したときに何が返るのかを、psql と Python から確認しました。
検証内容
検証環境
接続先のサーバーは PostgreSQL 16 互換でした。
version
---------------
PostgreSQL 16
(1 row)
server_version
----------------
16.15
(1 row)
検証に使った環境は次のとおりです。
- リージョン: ap-northeast-1
- クラスター構成: リンククラスターを持たない単一リージョン構成
- psql: 17 系
- Python: 3.12
- psycopg: 3.2.9
外部キー制約の定義
部署テーブルと、そこを参照する従業員テーブルを作成しました。
CREATE TABLE departments (
id int PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE employees (
id int PRIMARY KEY,
dept_id int NOT NULL REFERENCES departments(id),
name text NOT NULL
);
どちらも成功しました。以降の検証で使う初期データは次の2行です。
INSERT INTO departments (id, name) VALUES (1, 'Sales');
INSERT INTO employees (id, dept_id, name) VALUES (1, 1, 'Alice');
制約が効いていることを、子の挿入と親の削除の両方で確認しました。まず、存在しない親行を指定して INSERT INTO employees (id, dept_id, name) VALUES (2, 999, 'Bob'); を実行した結果です。
psql:/work/sql/02-fk-basic.sql:22: ERROR: 23503: insert or update on table "employees" violates foreign key constraint "employees_dept_id_fkey"
DETAIL: Key (dept_id)=(999) is not present in table "departments".
SCHEMA NAME: public
TABLE NAME: employees
CONSTRAINT NAME: employees_dept_id_fkey
次に、子行から参照されている親行を DELETE FROM departments WHERE id = 1; で削除しようとした結果です。
psql:/work/sql/02-fk-basic.sql:25: ERROR: 23503: update or delete on table "departments" violates foreign key constraint "employees_dept_id_fkey" on table "employees"
DETAIL: Key (id)=(1) is still referenced from table "employees".
SCHEMA NAME: public
TABLE NAME: employees
CONSTRAINT NAME: employees_dept_id_fkey
いずれも SQLSTATE は 23503 で、違反した制約名とキーの値がエラー本文に含まれていました。
既存テーブルへ後から制約を追加する場合、Aurora DSQL は NOT VALID を付けた文しか受け付けませんでした。これはALTER TABLE のドキュメントに明記されている仕様です。この確認では、次の2つのテーブルに1行ずつ入れた状態から始めました。
CREATE TABLE p_alt (id int PRIMARY KEY);
CREATE TABLE c_alt (id int PRIMARY KEY, pid int);
INSERT INTO p_alt VALUES (1);
INSERT INTO c_alt VALUES (1, 1);
まず NOT VALID を付けずに制約を追加してみます。
ALTER TABLE c_alt ADD CONSTRAINT c_alt_pid_fkey FOREIGN KEY (pid) REFERENCES p_alt(id);
Aurora DSQL はこの文をそのままでは受け付けませんでした。
psql:/work/sql/12-alter-add-constraint-notvalid.sql:11: ERROR: 0A000: unsupported ALTER TABLE ADD CONSTRAINT statement
末尾に NOT VALID を付けた文は成功しました。
ALTER TABLE c_alt ADD CONSTRAINT c_alt_pid_fkey FOREIGN KEY (pid) REFERENCES p_alt(id) NOT VALID;
制約は convalidated が f の未検証状態で登録されます。
conname | convalidated | pg_get_constraintdef
----------------+--------------+--------------------------------------------------
c_alt_pid_fkey | f | FOREIGN KEY (pid) REFERENCES p_alt(id) NOT VALID
c_alt_pkey | t | PRIMARY KEY (id) INCLUDE (pid)
(2 rows)
主キーが PRIMARY KEY (id) INCLUDE (pid) と表示されるのは、Aurora DSQL が主キーの定義時にテーブルの全列を含むインデックスを作るためです（Primary keys in Aurora DSQL）。テーブル作成時に INCLUDE を書いたわけではありません。
未検証の状態でも、新しく入る行には制約が効いています。INSERT INTO c_alt VALUES (2, 999); は 23503 で拒否されました。
既存行の検証は ALTER TABLE ASYNC c_alt VALIDATE CONSTRAINT c_alt_pid_fkey; で実行します。この文は即座に job_id を返します。検証は sys.jobs 上の VALIDATE_CONSTRAINT ジョブとして実行されました。
次の出力の job_id は実行ごとに変わる値なのでマスクしてあります。
job_id | status | job_type
----------------------------+-----------+---------------------
xxxxxxxxxxxxxxxxxxxxxxxxxx | completed | VALIDATE_CONSTRAINT
(1 row)
conname | convalidated | pg_get_constraintdef
----------------+--------------+----------------------------------------
c_alt_pid_fkey | t | FOREIGN KEY (pid) REFERENCES p_alt(id)
c_alt_pkey | t | PRIMARY KEY (id) INCLUDE (pid)
(2 rows)
ジョブの完了後は convalidated が t になり、定義から NOT VALID が外れています。なお、既存テーブルへの追加を確認したこの一連の手順は、他の節とは別のクラスター（構成は同一）で実施しました。
並行トランザクションの競合
外部キー制約が絡む更新を2つのセッションから同時に行いました。子行を1件も持たない親行を用意するため、departments に id = 2 の行を事前に追加してあります。手順は次のとおりです。
- セッションA が
id = 2の親行を削除し、コミットせずに5秒待つ - セッションA の開始から2秒後に、セッションB が同じ親行を参照する子行を追加して先にコミットする
- セッションA がコミットする
実行は1回で、後からコミットしたセッションA が 40001 で失敗しました。セッションA の出力です。開始と終了の時刻はこのスクリプトの clock_timestamp() による出力で、エラー行は直前の COMMIT に対するものです。
session_a_start
-------------------------------
2026-08-29 06:05:59.926605+00
(1 row)
BEGIN;
BEGIN
-- 子行が1件も無い親行を削除する（この時点では FK 違反にならない）
DELETE FROM departments WHERE id = 2;
DELETE 1
SELECT pg_sleep(5);
pg_sleep
----------
(1 row)
COMMIT;
SELECT clock_timestamp() AS session_a_end;
psql:/work/sql/03a-session-a.sql:8: ERROR: 40001: change conflicts with another transaction (OC000)
session_a_end
-------------------------------
2026-08-29 06:06:05.003089+00
(1 row)
セッションB 側の INSERT INTO employees (id, dept_id, name) VALUES (4, 2, 'Dave'); とコミットは、どちらも成功しました。
サーバー側で記録した時刻（UTC）を並べます。A の終了時刻は、失敗したコミットの直後に計測したものです。
| セッション | 開始 | 終了 |
|---|---|---|
| A（親行 DELETE・後にコミット） | 2026-08-29 06:05:59.926605+00 | 2026-08-29 06:06:05.003089+00 |
| B（子行 INSERT・先にコミット） | 2026-08-29 06:06:02.088162+00 | 2026-08-29 06:06:02.193520+00 |
セッションA が DELETE をコミットしていない状態でも、セッションB の INSERT からコミット完了までは約0.11秒で、ロックによる待ちは発生していません。セッションA は、開始から約5.08秒後にコミットが失敗しました。5秒の待機が終わった直後です。SQLSTATE は 40001 で、制約違反の 23503 とは別のコードです。エラー本文に外部キー制約名は現れず、change conflicts with another transaction (OC000) という競合メッセージでした。
この挙動については公式ドキュメントに記載があります。
To adjudicate these conflicts, Aurora DSQL implicitly applies the
KEY SHAREclause to referenced rows to detect whether any concurrent change invalidated your snapshot. If Aurora DSQL detects a conflict, it fails the transaction with a serialization error.
外部キー制約を張らない同じ形のテーブルで同じ手順を試したところ、今回は両セッションのコミットが成功し、40001 は発生しませんでした。
アプリ側からはどう見えるかを Python で確認しました。次のスクリプトは、psycopg で2つの接続を開き、同じ手順を再現します。後からコミットする側で例外の SQLSTATE を判定して出力します。接続には、PGPASSWORD 環境変数で渡した認証トークンを使っています。トークンは aws dsql generate-db-connect-admin-auth-token で発行したものです。
Python スクリプト全文
#!/usr/bin/env python3
"""Aurora DSQL: 外部キー制約付きテーブルの並行更新で返る SQLSTATE を確認する。
セッションA が子行を持たない親行を削除し、コミットする前にセッションB が
同じ親行を参照する子行を追加してコミットする。後からコミットするセッションA で
何が返るかを、例外の SQLSTATE で判定して出力する（リトライは行わない）。
"""
import os
import psycopg
CONNINFO = (
f"host={os.environ['DSQL_ENDPOINT']} port=5432 dbname=postgres "
f"user=admin password={os.environ['PGPASSWORD']} sslmode=require"
)
PARENT_ID = 3
CHILD_ID = 5
def setup() -> None:
"""検証対象の親行（子行なし）を用意する。"""
with psycopg.connect(CONNINFO, autocommit=True) as conn:
conn.execute("DELETE FROM employees WHERE id = %s", (CHILD_ID,))
conn.execute(
"INSERT INTO departments (id, name) VALUES (%s, %s) ON CONFLICT DO NOTHING",
(PARENT_ID, "Finance"),
)
def main() -> None:
setup()
conn_a = psycopg.connect(CONNINFO)
conn_b = psycopg.connect(CONNINFO)
try:
# セッションA: 子行が1件も無い親行を削除する（この時点ではエラーにならない）
conn_a.execute("DELETE FROM departments WHERE id = %s", (PARENT_ID,))
print("session A: DELETE departments -> ok (未コミット)")
# セッションB: 同じ親行を参照する子行を追加して先にコミットする
conn_b.execute(
"INSERT INTO employees (id, dept_id, name) VALUES (%s, %s, %s)",
(CHILD_ID, PARENT_ID, "Erin"),
)
conn_b.commit()
print("session B: INSERT employees -> committed")
# セッションA: 後からコミットする側で何が返るかを確認する
try:
conn_a.commit()
print("session A: COMMIT -> ok（競合なし）")
except psycopg.Error as exc:
print(f"session A: COMMIT -> {type(exc).__name__}")
print(f" sqlstate: {exc.sqlstate}")
print(f" message : {exc}")
if exc.sqlstate == "40001":
print(" -> serialization_failure（並行トランザクションとの競合）")
elif exc.sqlstate == "23503":
print(" -> foreign_key_violation（外部キー制約違反）")
else:
print(" -> 上記以外のエラー")
finally:
conn_a.close()
conn_b.close()
if __name__ == "__main__":
main()
実行結果です。
session A: DELETE departments -> ok (未コミット)
session B: INSERT employees -> committed
session A: COMMIT -> SerializationFailure
sqlstate: 40001
message : change conflicts with another transaction (OC000)
-> serialization_failure（並行トランザクションとの競合）
psycopg では psycopg.errors.SerializationFailure として上がり、sqlstate とメッセージは psql で見たものと同じでした。
なお、この競合への対処として、公式ドキュメントは失敗したトランザクションのリトライをベストプラクティスに挙げています。
Implement retry logic
Conflicts cause errors instead of waits. Design your workload to retry failed transactions.
まとめ
Aurora DSQL が外部キー制約をサポートし、参照整合性をデータベース側で担保しやすくなりました。ただし、競合が起きたときの挙動は PostgreSQL の外部キー制約と同じではありません。ロックで待たされるのではなく、トランザクションが失敗します。
外部キー制約を前提にしたワークロードを Aurora DSQL で動かす場合は、この違いとアプリ側のエラーハンドリングを十分に評価することをおすすめします。
