Aurora DSQL（プレビュー）で楽観的同時実行制御（OCC）を確認してみる

これは **[PostgreSQL Advent Calendar 2024](https://qiita.com/advent-calendar/2024/postgresql)** 2 枚目・10 日目の記事です。

[5 日目に少しだけ試した](https://qiita.com/hmatsu47/items/d2af466d970858dad3fa) **[Aurora DSQL](https://docs.aws.amazon.com/aurora-dsql/)**（プレビュー版）は同時実行制御として楽観的同時実行制御（OCC）を採用していますが、この挙動を確認してみました。

https://qiita.com/hmatsu47/items/d2af466d970858dad3fa

## 悲観的同時実行制御（PCC）と楽観的同時実行制御（OCC）

通常の RDBMS では、悲観的同時実行制御（PCC : Pessimistic Concurrency Control）によるトランザクション処理を採用しています。

PCC では、ロックを使って書き込みの競合を管理します。

例えば、先行するトランザクションが行挿入を行う一方で後続のトランザクションが**同一の主キー値で**行挿入を行った場合、後続トランザクションは**先行トランザクションがコミットまたはロールバックされるまで待たされます**。そして先行トランザクションがコミットした場合は主キー重複エラーが発生、ロールバックした場合は行挿入が成功します。

一方、Aurora DSQL は楽観的同時実行制御（OCC : Optimistic Concurrency Control）を採用しています。

OCC では、トランザクションの途中ではロックを使わず処理を進め、コミットの段階で書き込み競合を判定します。

先ほどの例と同様に、先行するトランザクションが行挿入を行う一方で後続のトランザクションが同一の主キー値で行挿入を行った場合、**先行トランザクションがコミットまたはロールバックするのを待つことなく**後続トランザクションで行挿入が実行されます。そして、いずれかのトランザクションのうち、先にコミットした側が成功し、後からコミットした側が（変更の競合エラーで）失敗します。

:::note
Amazon Aurora DSQL の同時実行制御について、5 日目の記事には AWS 公式ブログの英語記事へのリンクを示しましたが、その後日本語訳が公開されたので、あらためて日本語ブログ記事へのリンクを示します。

- **[Amazon Aurora DSQL の同時実行制御](https://aws.amazon.com/jp/blogs/news/concurrency-control-in-amazon-aurora-dsql/)**
:::

## まずは通常の PostgreSQL でトランザクションの動作を確認する

サーバーとして PostgreSQL 16 を使って試してみます。

:::note
今回は（動作がわかりやすいので）行挿入の動作を確認・比較します。更新の場合は違う動作になります。
:::

### テーブルを準備

まずスキーマとテーブルを準備します。

```sql:スキーマ・テーブル準備
postgres=# CREATE SCHEMA hoge;
CREATE SCHEMA
postgres=# CREATE TABLE hoge.fuga(id INT PRIMARY KEY UNIQUE, val INT);
CREATE TABLE
```

### 1 つ目のトランザクションで行挿入

引き続き、トランザクションを開始して主キーが`1`の行を挿入します。

```sql:トランザクション1で行挿入
postgres=# BEGIN;
BEGIN
postgres=*# INSERT INTO hoge.fuga VALUES(1, 100);
INSERT 0 1
```

### 2 つ目のトランザクションで行挿入

別のクライアント（`psql`）で 2 つ目のトランザクションを開始して、主キーが（重複するように）`1`の行を挿入します。

```sql:トランザクション2で行挿入
postgres=# BEGIN;
BEGIN
postgres=*# INSERT INTO hoge.fuga VALUES(1, 200);
```

ロック待ち状態になりました。

### 1 つ目のトランザクションで`COMMIT`

1 つ目のトランザクションに戻って`COMMIT`します。

```sql:トランザクション1でCOMMIT
postgres=*# COMMIT;
COMMIT
```

`COMMIT`が成功しました。

そして、2 つ目のトランザクションでエラーが発生しました。

```sql:トランザクション2でエラー発生
ERROR:  duplicate key value violates unique constraint "fuga_pkey"
DETAIL:  Key (id)=(1) already exists.
```

2 つ目のトランザクションはエラーが出た状態で継続しているので`ROLLBACK`します。

```sql:トランザクション2でROLLBACK
postgres=!# ROLLBACK;
ROLLBACK
```

## DSQL でトランザクションの動作を確認する

[前回の記事](https://qiita.com/hmatsu47/items/d2af466d970858dad3fa)のとおりに複数リージョンのクラスターを作成します。

（手順は省略）

### テーブルを準備

先ほどと同様にスキーマとテーブルを準備します。

```sql:スキーマ・テーブル準備
postgres=# CREATE SCHEMA hoge;
CREATE SCHEMA
postgres=# CREATE TABLE hoge.fuga(id INT PRIMARY KEY UNIQUE, val INT);
CREATE TABLE
```

### バージニア北部リージョンで 1 つ目のトランザクションを開始し行挿入

CloudShell で`psql`を使って DSQL クラスターのバージニア北部リージョンに接続します。

1 つ目のトランザクションを開始し主キーが`1`の行を挿入します。

```sql:バージニア北部リージョンで行挿入
postgres=> BEGIN;
BEGIN
postgres=*> INSERT INTO hoge.fuga VALUES(1, 100);
INSERT 0 1
```

ここまでは同じですね。

### オハイオリージョンで 2 つ目のトランザクションを開始し行挿入

CloudShell で`psql`を使って DSQL クラスターのオハイオリージョンに接続します。

2 つ目のトランザクションを開始し主キーが（重複する）`1`の行を挿入します。

```sql:オハイオリージョンで行挿入
postgres=> BEGIN;
BEGIN
postgres=*> INSERT INTO hoge.fuga VALUES(1, 200);
INSERT 0 1
```

先ほどのようにロック待ちにはならず`INSERT`が実行されました。

### バージニア北部リージョンで`COMMIT`

バージニア北部リージョンで`COMMIT`してみます。

```sql:バージニア北部リージョンでCOMMIT
postgres=*> COMMIT;
COMMIT
```

`COMMIT`が成功しました。

この時点ではオハイオリージョンのトランザクションに変化は見られません。

### オハイオリージョンで`COMMIT`

オハイオリージョンで`COMMIT`してみます。

```sql:オハイオリージョンでCOMMIT
postgres=*> COMMIT;
ERROR:  change conflicts with another transaction, please retry: (OC000)
```

この時点で初めてエラー（変更の競合）が出ました。

```sql:ロールバックは不要
postgres=> ROLLBACK;
WARNING:  there is no transaction in progress
ROLLBACK
```

プロンプトを見るとわかりますが、すでに 2 つ目のトランザクションは終了しているので`ROLLBACK`は不要です。

:::note
2 つのトランザクションを同一リージョンで実行しても同様の（OCC に沿った）動作になります。
:::

## これ以外のパターンは？

[先の公式ブログ記事](https://aws.amazon.com/jp/blogs/news/concurrency-control-in-amazon-aurora-dsql/)に、更新処理や`SELECT ... FOR UPDATE`、DDL を使うケースが例 1・例 2・例 3 として示されています。

https://aws.amazon.com/jp/blogs/news/concurrency-control-in-amazon-aurora-dsql/

## 変更の競合でエラーが発生したら？

エラーメッセージにもありますが、基本的にはエラーが発生した側のトランザクション処理をリトライ（最初からやり直し）する形で対処します。

:::note
現実には行挿入時に主キーが重複してリトライが発生するような設計のテーブルを扱うケースは少なく、ユニークキーや更新時の競合でリトライするケースが多いと思います。
:::

---

明日（11 日目）の 2 枚目カレンダーの担当は jun2 さんです。

https://qiita.com/jun2/items/a1afb97610f5d56426df