Aurora DSQLの楽観同時実行制御を手を動かして学ぶ

「楽観同時実行制御ね。おーおーおー、そういうことね。なるへそ？」

Aurora DSQLは、re:Invent 2024の最大の発表でした（個人の感想です）。キーノートの最中に、いきなりGoogle CloudのSpannerが引き合いに出されるほどだったので、Spannerファンの人にも要チェックのアプデだったのではと思います。

• マルチリージョン対応
• 高可用性とスケーラビリティ
• VPCの外で稼働
• サーバーレス設計
• ACID特性のサポート
• PostgreSQL互換rd
• 楽観的同時実行制御（OCC）の採用
• 分散アーキテクチャ

など、ポイントが目白押しのAurora DSQLですが、トランザクション制御の仕組みが、RDBでは聞き慣れない楽観的同時実行制御（OCC）が採用されていると聞き、改めてその挙動を手を動かしてまとめてみました。

はっきり言います。ドキュメント読んでいるだけより手を動かしたほうが、理解は100倍です。　基本、コマンドをそのまま叩けば動作するようにしているつもりなので、これからAurora DSQL触ってみよ！って思う方は、是非この内容を試してもらえればと思います。

ほな、いってみよ！

免責事項

記事執筆時の動作環境

意外でしたが、Aurora DSQLのPostgreSQLバージョンが確認できました。

select * from version();

version                                                                               |
--------------------------------------------------------------------------------------+
PostgreSQL 16.5 on aarch64-unknown-linux-gnu, compiled by clang version 19.1.3, 64-bit|

動作比較用にローカル環境に構築したPostgreSQLのバージョン

select * from version();

version                                                                                                         |
----------------------------------------------------------------------------------------------------------------+
PostgreSQL 17.2 on x86_64-apple-darwin23.6.0, compiled by Apple clang version 16.0.0 (clang-1600.0.26.4), 64-bit|

実際のSQLコマンドの実行、コンソールのCloudShellなどでも良いですが、手回しがやりやすいように自分はDBeverを利用しました。Aurora DSQLのエンドポイントはパブリックなので、普通にクライアントのMacからでも接続設定は非常に簡単でした。接続設定はこんな感じです。

接続方法は、公式マニュアルにも記載があるのでこちらを参考にしてください。

Using SQL clients with Aurora DSQL

いろいろクエリを編集して流す時、やっぱりSQLクライアントは便利。

あんまり関係ないですが、筆者のMacのバージョンも置いておきます。

$ sw_vers
ProductName:		macOS
ProductVersion:		14.6.1
BuildVersion:		23G93

この記事を書く時に主に参照した情報

• What is Amazon Aurora DSQL? - Amazon Aurora DSQL
• Amazon Aurora DSQL の同時実行制御

検証前準備：Aurora DSQLクラスターの作成

Aurora DSQLクラスターの作成は、めちゃくちゃ簡単です。マネジメントコンソールからだとこんな感じ。検証で利用したかったため、[Add linked Regions]で、別リージョンのエンドポイントも作成しています。

しばらくすると、こんな感じでクラスターの作成が完了します。

検証前準備：検証で利用するテーブルの作成

AWSの公式ブログと同じスキーマとテーブルを作成していきます。

create schema orders;

以降、毎回テーブルをスキーマで修飾するのも面倒なので、カレントスキーマを設定しておきます。

set search_path = orders;

select current_schema();

current_schema|
--------------+
orders        |

accountsテーブルを作成し、一つデータを挿入します。

CREATE TABLE accounts (
 id int PRIMARY KEY,
 balance DECIMAL(10, 2),
 version INT NOT NULL
);

INSERT INTO accounts (id, balance, version) VALUES (1, 100.00, 1);

挿入したデータを確認しておきます。

select * from accounts;

id|balance|version|
--+-------+-------+
 1| 100.00|      1|

別トランザクションによる更新データ競合と、楽観的同時実行制御の動作確認

それでは早速、Aurora DSQLにおける別トランザクションによるデータ競合の動作確認をしていきます。例は非常にシンプル。下記テーブルの同一レコードに対して、別トランザクションで競合する更新をかけます。

元データ。

SELECT id, balance, version FROM accounts WHERE id = 1 ;

id|balance|version|
--+-------+-------+
 1| 100.00|      1|

上記データに対して、以下2種類のトランザクションを競合させます

• トランザクションA：balanceを+10で更新
• トランザクションB：balanceを-30で更新

トランザクションAで、更新をかけます。ここでは、まだCOMMITはしません。

BEGIN ;
 UPDATE accounts SET balance = balance + 10 WHERE id = 1;
 SELECT id, balance, version FROM accounts WHERE id = 1 ;

id|balance|version|
--+-------+-------+
 1| 110.00|      1|

トランザクションBでは、まだ、トランザクションAの更新前データが見えています。

SELECT id, balance, version FROM accounts WHERE id = 1 ;

id|balance|version|
--+-------+-------+
 1| 100.00|      1|

トランザクションBで、トランザクションAと競合する更新をかけます。まだCOMMITはしません。このように、後から開始されたトランザクションBにおいては、独自に書き込みセットが作成され、更新がかかります。この時点で、トランザクションAとトランザクションBが競合状態となります。

BEGIN ;
 UPDATE accounts SET balance = balance - 30 WHERE id = 1;
 SELECT id, balance, version FROM accounts WHERE id = 1 ;

id|balance|version|
--+-------+-------+
 1|  70.00|      1|

トランザクションBをCOMMITします。COMMITは無事成功し、トランザクションBのデータがaccountsテーブルに反映されます。

COMMIT;
SELECT id, balance, version FROM accounts WHERE id = 1 ;

id|balance|version|
--+-------+-------+
 1|  70.00|      1|

トランザクションAでは、まだaccountsテーブルの中はトランザクションAの更新データを持っていますが、COMMITタイミングでOCCエラーとなり、最終的なデータはトランザクションBの更新が反映されています。

SELECT id, balance, version FROM accounts WHERE id = 1 ;

id|balance|version|
--+-------+-------+
 1| 110.00|      1|
 
COMMIT;

SQLエラー [40001]: ERROR: change conflicts with another transaction, please retry: (OC000)

SELECT id, balance, version FROM accounts WHERE id = 1 ;

id|balance|version|
--+-------+-------+
 1|  70.00|      1|

トランザクションAはエラーとなったため、再度同じSQLを実行しデータに反映させる必要があります。

BEGIN ;
 UPDATE accounts SET balance = balance + 10 WHERE id = 1;
COMMIT ;

SELECT id, balance, version FROM accounts WHERE id = 1 ;

id|balance|version|
--+-------+-------+
 1|  80.00|      1|

このように、Aurora DSQLのトランザクション制御は、楽観的同時実行制御を使用しているため、基本的にトランザクションの中でロックはとらず、最終的なCOMMITのタイミングで競合が発生したときのみ解決します。この手法で、分散環境でのスケーラビリティを向上させています。

つまりは、エラーが返ってきた時にアプリケーション側でのリトライ処理の実装が不可欠ということになります。このあたりのアプローチは、公式ドキュメントの下記が詳しいです。

Understanding concurrency control in Aurora DSQL - Amazon Aurora DSQL

Aurora DSQLのトランザクション分離レベルはどうなっているのか？

公式ブログ（Amazon Aurora DSQL の同時実行制御）などを見ていると、Aurora DSQLのトランザクション分離レベルは「REPETABLE READと同等」と説明されています。

他の分散 SQL データベースがSerializable分離レベルをサポートするのに対し、Aurora DSQL は PostgreSQL の repeatable read 分離レベルに相当する強力なスナップショット分離をサポートしています。

実際どうか確認してみます。

SHOW TRANSACTION ISOLATION LEVEL;

transaction_isolation|
---------------------+
repeatable read      |

このようにrepeatable readが返ってきました。では、実際にテーブルをロックしたときのロックモードがどうなっているか確認しようとしたのですが、Aurora DSQLは、pg_locksシステムビューに対応していないため、ロックの挙動の詳細を確認することができません。残念。

参照：Using system tables and commands in Aurora DSQL - Amazon Aurora DSQL

まぁ、通常のRDBのロックの仕様とは異なるので、仕方ないのかもしれませんが。できればこのシステムビューがどのように表現されるのか見たかったですね。

ちなみに、repeatable read以外のトランザクション分離レベルを指定しようとすると、エラーになります。

set transaction isolation level serializable;

SQLエラー [0A000]: ERROR: Unsupported isolation level: SERIALIZABLE

これは、READ committedもREAD uncommittedも同様です。

参考：通常のPostgreSQLにおける別トランザクションによる更新データ競合の動作

「通常のPostgreSQLの挙動ってどうだったかな？」と感じる人も多いと思うので、同様の動作をMacにインストールした通常のPostgreSQLで試してみました。

デフォルトでは、PostgreSQLのトランザクション分離レベルは、read committedです。

SHOW TRANSACTION ISOLATION LEVEL;

transaction_isolation|
---------------------+
read committed       |

初期データはこの通り。

SELECT id, balance, version FROM accounts WHERE id = 1 ;

id|balance|version|
--+-------+-------+
 1| 100.00|      1|

トランザクションAで更新をかけます。

BEGIN ;
 UPDATE accounts SET balance = balance + 10 WHERE id = 1;
 SELECT id, balance, version FROM accounts WHERE id = 1 ;

id|balance|version|
--+-------+-------+
 1| 110.00|      1|

トランザクションBでは、まだ、トランザクションAの更新前データが見えています。

SELECT id, balance, version FROM accounts WHERE id = 1 ;

id|balance|version|
--+-------+-------+
 1| 100.00|      1|

READ COMMITTEDの場合、ダーティリードはされないので、別トランザクションのBからは、トランザクションAのコミット前のデータは見えません。

トランザクションBで、同一レコードに対して更新をかけます。

BEGIN ;
 UPDATE accounts SET balance = balance - 30 WHERE id = 1;

（処理中・・・）

となり、トランザクションBでは、トランザクションAのロック解放待ちとなります。この状態で、pg_locksテーブルを確認すると、accountテーブルにRowExclusiveLock（行排他ロック）がかかっていることがわかります。

SELECT l.pid,l.granted,d.datname,l.locktype,relation,relation::regclass,transactionid,l.mode
    FROM pg_locks l  LEFT JOIN pg_database d ON l.database = d.oid
    WHERE  l.pid != pg_backend_pid()
    ORDER BY l.pid;

pid  |granted|datname |locktype     |relation|relation     |transactionid|mode            |
-----+-------+--------+-------------+--------+-------------+-------------+----------------+
52197|true   |postgres|relation     |   16392|accounts_pkey|             |RowExclusiveLock|
52197|true   |postgres|relation     |   16389|accounts     |             |RowExclusiveLock|
52197|true   |        |virtualxid   |        |             |             |ExclusiveLock   |
52197|true   |        |transactionid|        |             |761          |ExclusiveLock   |
52197|true   |postgres|tuple        |   16389|accounts     |             |ExclusiveLock   |
52197|false  |        |transactionid|        |             |760          |ShareLock       |

そのため、トランザクションBからのUPDATEによるRowExclusiveLockと競合し、トランザクションBがトランザクションAのロック解放待ちになるというわけです。

トランザクションAをコミットし、結果を確認します。

COMMIT;

SELECT id, balance, version FROM accounts WHERE id = 1 ;

id|balance|version|
--+-------+-------+
 1| 110.00|      1|

トランザクションBを確認すると、トランザクションAがCOMMITされロックが開放されたため、UPDATEが完了し、結果がトランザクションBの中で反映されています（このタイミングでは、まだトランザクションBはコミットされていない）。

SELECT id, balance, version FROM accounts WHERE id = 1 ;

id|balance|version|
--+-------+-------+
 1|  80.00|      1|

このタイミングでは、トランザクションBはまだCOMMITされていないため、トランザクションAでは、その結果を確認できません。

SELECT id, balance, version FROM accounts WHERE id = 1 ;

id|balance|version|
--+-------+-------+
 1| 110.00|      1|

トランザクションBをコミットすると、トランザクションBとA双方で、同じ結果を確認できます。

COMMIT;

SELECT id, balance, version FROM accounts WHERE id = 1 ;

id|balance|version|
--+-------+-------+
 1|  80.00|      1|

SELECT id, balance, version FROM accounts WHERE id = 1 ;

id|balance|version|
--+-------+-------+
 1|  80.00|      1|

というわけで、Aurora DSQLとPostgreSQLでの同一レコードに対する競合するトランザクションがどのように動作するかを書いてみました。どちらもbalanceの値は同じ結果の80となりますが、Aurora DSQLでは、コミットタイミングで、競合を検知してエラーとなっていることがわかるかと思います。

通常のPostgreSQLでは絶対にエラーにならないのかというともちろんそんなことはなく、クライアント側で指定したタイムアウト時間を経過してもロックが開放されない場合、エラーとなります。

SELECT FOR UPDATEによるライトスキューの管理

こちらも、公式ブログに記載があった内容です。正直最初は理解しがたいですが、ゆっくり見ていきましょう。実際の動作確認も非常にシンプルな例で実施します。

前のところで説明したとおり、Aurora DSQLは、通常読み取りレコードの同時実行性チェックを実施しませんがSELECT FOR UPDATEを使うことで、読み取った行に同時実行性チェックのフラグを立てることができます。

ライトスキューとは何か？ライトスキューのメリットについて解説

ライトスキューという単語、自分初めて知りました。例を挙げて説明します。ここの解説では、以下の記事を引用させていただいています。この場を借りてお礼します！

• アプリケーション開発において重要なロックを掘り下げ、ORM における楽観的ロックの実現例を紹介する #楽観ロック - Qiita
• データベースのトランザクション処理について整理する（その2） #Database - Qiita

ライトスキューは、2つの同時実行トランザクションが共通のデータセットを読み取り、それぞれが異なる部分を更新する際に発生する現象です。具体的には以下のような状況で起こります。

1. 2つのトランザクション（T1とT2）が同時に実行される
2. 両方のトランザクションが同じデータセットを読み取る
3. T1がデータセットの一部（例：A）を更新し、T2が別の部分（例：B）を更新する
4. 両方のトランザクションが正常にコミットされる

この結果、個々のトランザクションは正しく実行されているように見えますが、全体としては望ましくない状態や制約違反を引き起こす可能性があります。

ライトスキューの例

具体例を挙げると、以下のようなシナリオが考えられます。

1. 2つの銀行口座があり、合計残高が負にならないという制約がある
2. 口座A: $100、口座B: $150（合計$250）
3. トランザクションT1: 口座Aから$200引き出す
4. トランザクションT2: 口座Bから$200引き出す
5. 両方のトランザクションが同時に実行され、コミットされる

結果：

• 口座A: -$100
• 口座B: -$50
• 合計残高: -$150（制約違反）

この例では、個々のトランザクションは制約を満たしているように見えますが、全体としては制約違反が発生しています。　つまりは、別々のテーブルを更新しそれぞれのトランザクションが問題なく完了しているにもかかわらず、全体として不整合が発生する事象のことです。

SELECT FOR UPDATEで、どのようにライトスキューを管理するのか、実際にSQLを叩いて動作確認する

もう少し簡単なSQLで見ていきます。事前準備として、別のテーブルを追加しデータを挿入しておきます。

CREATE TABLE IF NOT EXISTS orders.orders (
    order_id int PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount NUMERIC(10, 2)
);

 INSERT INTO orders.orders (order_id, customer_id, order_date, total_amount)
 VALUES (1, 12, '2024-09-26 14:30:00', 99.99);
 
 INSERT INTO orders.orders (order_id, customer_id, order_date, total_amount)
 VALUES (2, 123, '2024-09-26 14:32:00', 99.99);

トランザクションAを開始します。ここでは、SELECT FOR UPDATEでordersテーブルを参照しているだけで、データの更新は実施しません。

begin ;
 select order_id, customer_id, total_amount from orders where order_id = 1 FOR UPDATE ;

order_id|customer_id|total_amount|
--------+-----------+------------+
       1|         12|       99.99|

トランザクションBを開始します。トランザクションBもトランザクションAと同じくordersテーブルをSELECT FOR UPDATEし、ordersテーブルを更新しCOMMITします。

begin ;
 select order_id, customer_id, total_amount from orders where order_id = 1 FOR UPDATE ;

order_id|customer_id|total_amount|
--------+-----------+------------+
       1|         12|       99.99|

 update orders SET total_amount = 103 WHERE order_id = 1 ;
commit ;

select order_id, customer_id, total_amount from orders where order_id = 1

order_id|customer_id|total_amount|
--------+-----------+------------+
       1|         12|      103.00|

トランザクションAはトランザクションBの更新テーブルとは異なるaccountsテーブルを更新してCOMMITしようとすると、OCC例外が発生します。

UPDATE accounts SET balance = balance + 60, version = version + 1 WHERE id = 1 AND version = 1 ;

commit;

SQLエラー [40001]: ERROR: change conflicts with another transaction, please retry: (OC000)

つまり、トランザクションAはordersテーブルを更新していないが、ordersテーブルのorder_idが1のレコードを最初にSELECT FOR UPDATEで読み込んでいたため、トランザクションBでordersテーブルの同一レコードが更新されたことで楽観同時実行制御のエラー対象となり、トランザクションAのaccountsテーブルへのCOMMITが失敗した、というわけです。

AWSの公式ブログに以下の記載があるとおり、Aurora DSQLのSELECT FOR UPDATEは、プライマリーキーの指定が必須です。

また、SELECT FOR UPDATE フィルターには、選択するテーブルのプライマリーキーを含める必要があります。今回の場合、orders テーブルのプライマリーキーは order_id なので、この SELECT FOR UPDATE は失敗します。
参照：Amazon Aurora DSQL の同時実行制御 | Amazon Web Services ブログ

2024年12月16日現在では、まだ、ユーザーガイドにはこれらライトスキューの概念自体が記載街無いのですが、そのうち反映されるのかなと思います。

自分は、正直AWSの公式ブログを読んでいるだけでは意味が理解できなかったのですが、実際手でアレコレ動かしてみてようやく挙動を理解しました。

通常のPostgreSQLだと、どういう挙動になるのか？

通常のPostgreSQL（トランザクション分離レベルがデフォルトのREAD COMMITTED）の場合、トランザクションAのSELECT FOR UPDATEがかかった後、トランザクションBで同一レコードに対してSELECT FOR UPDATEをかけたタイミングで、ロック解放待ちの状態になり後続のトランザクションが動きません。

OCC例外にいかに立ち向かうのか？

このように、Aurora DSQLのOCCでは、トランザクション競合時のリトライを管理するためにバックオフとジッターの実装が重要です。詳細は、AWSより以下のブログが紹介されており、サンプルコードも提示されているので、参照すると良いでしょう。

Exponential Backoff And Jitter | AWS Architecture Blog

実際に手を動かすことで理解が深まる

非常にわかり易く解説されているAWSの公式ブログを参照しながら、自分なりに手を動かしてまとめていたのですが、これでもものすごく時間がかかりました。

Amazon Aurora DSQL の同時実行制御 | Amazon Web Services ブログ

実際に挙動を確認しながら、改めてトランザクション分離レベルや行ロックのバリエーションを復習していたっていうのもありますが、素のPostgreSQLとの動作確認も平行することで、よりAurora DSQLがどういったものか、雰囲気が掴めてきたと思います。

みなさんも、もし時間があれば、動画やドキュメントを眺めるだけでなく、実際に手を動かして体験し、Aurora DSQLの楽観同時実行制御の仕組みを身体で覚えてもらえればと思います。

最後に、このブログを書こうと思ったきっかけの動画を紹介します。ちょうどデモが始まるタイミングから再生されます。

https://youtu.be/g448Wmjg-Ag?si=QM-XShT6y5R8tyCg&t=4746

先日のre:Growth 2024の動画ですが、登壇しながら、こんな込み入ったデモをやろうとしている心意気すげぇなと思いつつ、このブログを書いてみようと思いました。登壇資料はこちら。

AWS re:Invent ふりかえり勉強会「クラスメソッド re:Growth 2024 東京」で Aurora DSQL を話してきました！ | DevelopersIO

改めて、Guri / Hajime Oguriさん、ありがとうございます！

それでは、今日はこのへんで。濱田孝治（ハマコー）（@hamako9999）でした。