Aurora DSQL のトランザクション（スナップショット分離と OCC）

Aurora DSQL のトランザクション （スナップショット分離と OCC） クラウド LT 大会 vol.14 フリーテーマ！ 2025/8/21 まつひさ（hmatsu47） 1

自己紹介 松久裕保（@hmatsu47） ● https://qiita.com/hmatsu47 ● 現在： ○ 名古屋で Web インフラのお守り係をしています ○ SRE チームに所属しつつ技術検証の支援をしています ○ 普段カンファレンス・勉強会では DB の話しかしていません （ほぼ） ■ 今月これで 4 本目（1 本だけ珍しく AI コーディングの話） 2

• https://qiita.com/hmatsu47

本日の内容 ● 発表の動機 ● Aurora DSQL 概要 ● スナップショット分離と OCC ● Aurora DSQL での動作 ● まとめ 3

発表の動機 4

Aurora DSQL：Serverless の新たなデータストア ● DynamoDB はよくできているが扱いが難しい面がある ○ アプリケーションと密結合なテーブル設計になりがち ● Aurora DSQL なら「スケールする RDB」として使える ○ RDB(MS) のテーブル設計の知見が生かせる ● 一方で Aurora DSQL は通常の RDB とは違う部分がある ○ OCC（楽観的同時実行制御）の採用など ○ ここでハマってしまうと「使えない」「難しい」となってしまう 5

Aurora DSQL でハマらないためには？ ● まずは通常の RDB とは違う部分を理解する必要がある →ここを伝えたい 6

Aurora DSQL 概要 7

サーバーレス分散 SQL データベース ● PostgreSQL ワイヤープロトコル互換 ○ psql コマンドが使える ● シングルリージョン構成とマルチリージョン構成がある ○ マルチリージョン構成は US 3 リージョン／欧州 3 リージョン／ 東京＋大阪＋ソウルの組み合わせでサポート ■ エンドポイントは 2 リージョン、残り 1 つは Witness リージョンで構成 ○ 次ページの図はシングルリージョン構成の例 8

それぞれの階層で負荷等に合わせて水平スケール AWS Summit Japan 2025 AWS-43 資料より 引用元 : https://aws.amazon.com/jp/blogs/news/introducing-amazon-aurora-dsql/ 9

• https://aws.amazon.com/jp/blogs/news/introducing-amazon-aurora-dsql/

スナップショット分離と OCC 10

スナップショット分離（Snapshot Isolation）とは ● DBMS におけるトランザクション分離レベルの 1 つ ○ トランザクション開始時のコミット済みデータを読み取る ○ 並行する他のトランザクションが更新したデータを読み取らない ● 書き込みスキュー異常発生の可能性がある ○ 並行する複数のトランザクションで相互に関連するデータを読み 取り、その値を元に別々のデータを更新するケースで、最終的な 結果の矛盾が生じることも ■ 詳細は「Aurora DSQL での動作」にて 11

OCC（楽観的同時実行制御）とは ● 同時実行制御方式の 1 つ ○ ロックを使わない ■ 通常の RDBMS ではロックを使う→ PCC（悲観的同時実行制御） ○ 並行する複数のトランザクションが同じデータ行の更新を試みた 場合、最初にコミットしたトランザクションの処理が成功する ■ 後からコミットしたトランザクションの処理は中断（アボート） ■ 必要に応じてロールバック後にアプリケーションでリトライ 12

Aurora DSQL での動作 13

スナップショット分離（Snapshot Isolation） ⚫BEGIN ◎INSERT A (1, 100) ⚫BEGIN ◎SELECT A →空 COMMIT⭕ ◎SELECT A →空 COMMIT⭕ ◎SELECT A → (1, 100) ここはCOMMIT前とは 別のトランザクション ● ● 先行トランザクションで COMMIT 成功⭕→自トランザクションで値は表示されない 自トランザクションで COMMIT →（新たなトランザクション開始） →先行トランザクションで COMMIT した値が表示される 14

スナップショット分離（Snapshot Isolation） ・トランザクション A（テーブル準備） postgres=> CREATE SCHEMA hoge; postgres=> CREATE TABLE hoge.fuga(id INT PRIMARY KEY UNIQUE, val INT); ・トランザクション A（開始） postgres=> BEGIN; ・トランザクション B（開始） postgres=> BEGIN; ・トランザクション A（データ挿入＆コミット） postgres=*> INSERT INTO hoge.fuga VALUES(1, 100); postgres=*> COMMIT; 15

スナップショット分離（Snapshot Isolation） ・トランザクション B（データ参照しても見えない） postgres=*> SELECT * FROM hoge.fuga; id | val ----+----(0 rows) ・トランザクション B・B’（コミット →データ参照すると見える） postgres=*> COMMIT; postgres=> SELECT * FROM hoge.fuga; id | val ----+----1 | 100 (1 row) 16

同一テーブルの同一行を更新する場合 [1] ⚫BEGIN ◎UPDATE A ⚫BEGIN ◎UPDATE A ⚫BEGIN ● ◎UPDATE A COMMIT⭕ COMMIT❌ COMMIT❌ 並行トランザクションは「最初にコミット」したもの勝ち 17

同一テーブルの同一行を更新する場合 [1] ・トランザクション A（開始〜データ更新） postgres=> BEGIN; postgres=*> UPDATE hoge.fuga SET val = 110 WHERE id = 1; ・トランザクション B（開始〜データ更新） postgres=> BEGIN; postgres=*> UPDATE hoge.fuga SET val = 120 WHERE id = 1; ・トランザクション A（コミット） postgres=*> COMMIT; ・トランザクション B（コミット失敗） postgres=*> COMMIT; ERROR: change conflicts with another transaction, please retry: (OC000) 18

同一テーブルの同一行を更新する場合 [1’] ⚫BEGIN ◎UPDATE A ⚫BEGIN ◎UPDATE A ⚫BEGIN ● ◎UPDATE A COMMIT❌ COMMIT⭕ COMMIT❌ 並行トランザクションは「最初にコミット」したもの勝ち 19

同一テーブルの同一行を更新する場合 [1’] ● こちらの記事を参照 ○ 「ゲームで体感！Aurora DSQL の OCC（楽観的同時実行制御）」の 結果ログから Aurora DSQL の動作を考察する https://qiita.com/hmatsu47/items/75eee0b21e3be5b80061 20

• https://qiita.com/hmatsu47/items/75eee0b21e3be5b80061

同一テーブルの同一行を更新する場合 [2] ⚫BEGIN ◎UPDATE A COMMIT⭕ ⚫BEGIN ◎UPDATE A ⚫BEGIN ● COMMIT❌ →ROLLBACK ◎UPDATE A COMMIT❌ 成功トランザクションの COMMIT 前に BEGIN → COMMIT 時に失敗❌ （UPDATE のタイミングが成功トランザクションの COMMIT 前か後かを問わず） 21

同一テーブルの同一行を更新する場合 [2] ・トランザクション A（開始〜データ更新） postgres=> BEGIN; postgres=*> UPDATE hoge.fuga SET val = 120 WHERE id = 1; ・トランザクション B（開始〜データ更新） postgres=> BEGIN; postgres=*> UPDATE hoge.fuga SET val = 130 WHERE id = 1; ・トランザクション C（開始） postgres=> BEGIN; ・トランザクション A（コミット） postgres=*> COMMIT; 22

同一テーブルの同一行を更新する場合 [2] ・トランザクション C（データ更新） postgres=*> UPDATE hoge.fuga SET val = 140 WHERE id = 1; ・トランザクション B（コミット失敗・ロールバック） postgres=*> COMMIT; ERROR: change conflicts with another transaction, please retry: (OC000) postgres=> ROLLBACK; WARNING: there is no transaction in progress ・トランザクション C（コミット失敗） postgres=*> COMMIT; ERROR: change conflicts with another transaction, please retry: (OC000) 23

同一テーブルの同一行を更新する場合 [3] ⚫BEGIN ◎UPDATE A COMMIT⭕ ⚫BEGIN ◎UPDATE A ⚫BEGIN ● COMMIT❌ →ROLLBACK ◎UPDATE A COMMIT⭕ 1 つ目の COMMIT 成功⭕後に 3 つ目が BEGIN し、 2 つ目の COMMIT 失敗❌・ROLLBACK 後に 3 つ目が COMMIT →成功⭕ 24

同一テーブルの同一行を更新する場合 [3] ・トランザクション A（開始〜データ更新） postgres=> BEGIN; postgres=*> UPDATE hoge.fuga SET val = 200 WHERE id = 1; ・トランザクション B（開始〜データ更新） postgres=> BEGIN; postgres=*> UPDATE hoge.fuga SET val = 210 WHERE id = 1; ・トランザクション A（コミット） postgres=*> COMMIT; ・トランザクション C（開始〜データ更新） postgres=> BEGIN; postgres=*> UPDATE hoge.fuga SET val = 220 WHERE id = 1; 25

同一テーブルの同一行を更新する場合 [3] ・トランザクション B（コミット失敗・ロールバック） postgres=*> COMMIT; ERROR: change conflicts with another transaction, please retry: (OC000) postgres=> ROLLBACK; WARNING: there is no transaction in progress ・トランザクション C（コミット・データ参照 →トランザクション Cの値で上書きされている） postgres=*> COMMIT; postgres=> SELECT * FROM hoge.fuga; id | val ----+----1 | 220 (1 row) 26

更新対象行がトランザクション毎に異なる場合 [1] ⚫BEGIN ◎UPDATE A ⚫BEGIN COMMIT⭕ ◎UPDATE B COMMIT⭕ ⚫BEGIN ● ● ◎UPDATE C COMMIT⭕ 自明なので実行結果は省略 すべてのトランザクションは並行しているが、すべて更新対象が違う →すべて COMMIT 成功⭕ 27

更新対象行がトランザクション毎に異なる場合 [2] ⚫BEGIN ◎INSERT A ⚫BEGIN COMMIT⭕ ◎INSERT A ◎INSERT B ⚫BEGIN ● ◎INSERT B COMMIT❌ →ROLLBACK COMMIT⭕ 最初のトランザクションと 3 つ目のトランザクションは更新（挿入）対象が違う 2 つ目が COMMIT 失敗❌・ROLLBACK → 3 つ目が COMMIT 成功⭕ 28

更新対象行がトランザクション毎に異なる場合 [2] ・トランザクション A（テーブル準備） postgres=> DELETE FROM hoge.fuga; postgres=> SELECT * FROM hoge.fuga; id | val ----+----(0 rows) postgres=> CREATE TABLE hoge.piyo(id INT PRIMARY KEY UNIQUE, val INT); ・トランザクション A（開始〜データ挿入 A） postgres=> BEGIN; postgres=*> INSERT INTO hoge.fuga VALUES(1, 100); 29

更新対象行がトランザクション毎に異なる場合 [2] ・トランザクション B（開始〜データ挿入 A・B） postgres=> BEGIN; postgres=*> INSERT INTO hoge.fuga VALUES(1, 110); postgres=*> INSERT INTO hoge.piyo VALUES(1, 200); ・トランザクション C（開始〜データ挿入 B） postgres=> BEGIN; postgres=*> INSERT INTO hoge.piyo VALUES(1, 210); ・トランザクション A（コミット） postgres=*> COMMIT; 30

更新対象行がトランザクション毎に異なる場合 [2] ・トランザクション B（コミット失敗・ロールバック） postgres=*> COMMIT; ERROR: change conflicts with another transaction, please retry: (OC000) postgres=> ROLLBACK; WARNING: there is no transaction in progress ・トランザクション C（コミット） postgres=*> COMMIT; 31

更新対象行がトランザクション毎に異なる場合 [2] ・トランザクション C（データ参照 →トランザクション A・Cで挿入した値が表示される） postgres=*> COMMIT; postgres=> SELECT * FROM hoge.fuga; id | val ----+----1 | 100 (1 row) postgres=> SELECT * FROM hoge.piyo; id | val ----+----1 | 210 (1 row) 32

書き込みスキュー異常 ⚫BEGIN ⚫BEGIN ● ● ● ◎SELECT A ◎SELECT B → 100000・ 80000 ◎A+B<200000 →UPDATE A=A+20000 ◎SELECT A ◎SELECT B → 100000・ 80000 COMMIT⭕ ◎A+B<200000 →UPDATE B=B+20000 COMMIT⭕ ⚫BEGIN ◎SELECT A ◎SELECT B → 120000・ 100000 SELECT は競合しない UPDATE も競合しない（対象行が別） たとえば A=10 万・B=8 万のときに「A+B が 20 万未満なら +2 万して UPDATE」 →本来なら（先行トランザクションの）A が 12 万になるだけのはずが B も 10 万に 33

書き込みスキュー異常の回避 ⚫BEGIN ◎SELECT A ... FOR UPDATE ◎SELECT B ... FOR UPDATE →100000・80000 ⚫BEGIN ◎A+B<200000 →UPDATE A=A+20000 ◎SELECT A ... FOR UPDATE ◎SELECT B ... FOR UPDATE →100000・80000 COMMIT⭕ ◎A+B<200000 →UPDATE B=B+20000 COMMIT❌ ⚫BEGIN ● ◎SELECT A ◎SELECT B → 120000・ 80000 SELECT に ... FOR UPDATE を追加して A・B それぞれの行に更新フラグを立てる →後から実行した COMMIT を失敗させる（その後リトライしても B は 8 万のまま） 34

書き込みスキュー異常の回避 ・トランザクション A（テーブル準備〜初期データ挿入） postgres=> CREATE TABLE hoge.account(number INT PRIMARY KEY UNIQUE, name VARCHAR(100) NOT NULL, amount INT NOT NULL); postgres=> INSERT INTO hoge.account VALUES(10000001, '佐藤一郎 ', 100000); postgres=> INSERT INTO hoge.account VALUES(11000001, '佐藤二朗 ', 80000); postgres=> SELECT * FROM hoge.account; number | name | amount ----------+----------+-------10000001 | 佐藤一郎 | 100000 11000001 | 佐藤二朗 | 80000 (2 rows) 35

書き込みスキュー異常の回避 ・トランザクション A（開始〜 SELECT...FOR UPDATE） postgres=> BEGIN; postgres=*> SELECT * FROM hoge.account WHERE number = 10000001 FOR UPDATE; number | name | amount ----------+----------+-------10000001 | 佐藤一郎 | 100000 (1 row) postgres=*> SELECT * FROM hoge.account WHERE number = 11000001 FOR UPDATE; number | name | amount ----------+----------+-------11000001 | 佐藤二朗 | 80000 (1 row) 36

書き込みスキュー異常の回避 ・トランザクション B（開始〜 SELECT...FOR UPDATE） postgres=> BEGIN; postgres=*> SELECT * FROM hoge.account WHERE number = 10000001 FOR UPDATE; number | name | amount ----------+----------+-------10000001 | 佐藤一郎 | 100000 (1 row) postgres=*> SELECT * FROM hoge.account WHERE number = 11000001 FOR UPDATE; number | name | amount ----------+----------+-------11000001 | 佐藤二朗 | 80000 (1 row) 37

書き込みスキュー異常の回避 ・トランザクション A（合計20万未満なので 佐藤一郎の口座を +2万してコミット ） postgres=*> UPDATE hoge.account SET amount = amount + 20000 WHERE number = 10000001; postgres=*> COMMIT; postgres=> SELECT * FROM hoge.account; number | name | amount ----------+----------+-------10000001 | 佐藤一郎 | 120000 11000001 | 佐藤二朗 | 80000 (2 rows) ・トランザクション B（合計20万未満なので 佐藤二朗の口座を +2万してコミット →失敗） postgres=*> UPDATE hoge.account SET amount = amount + 20000 WHERE number = 11000001; postgres=*> COMMIT; ERROR: change conflicts with another transaction, please retry: (OC000) ※ロールバック後再実行したときには合計 20万なので条件を満たさず →加算せず終了 38

まとめ 39

● OCC は PCC と挙動が異なる ○ ロックしないので COMMIT 時に更新の競合を判定 ■ 必要ならアプリケーションでリトライを実装 ● BEGIN と COMMIT / ROLLBACK の時刻で競合判断 ○ この時間範囲と更新対象行が重なっていれば競合とみなす ■ 「BEGIN 後の最初の更新（挿入）から開始」ではない点に注意 ■ 競合しても先行側が失敗→ ROLLBACK していれば COMMIT は成功する ● 書き込みスキューに注意 ○ SELECT ... FOR UPDATE で対象行に更新フラグを立てて回避 40