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：サーバーレスの新しいデータストア ● DynamoDB はよくできているが扱いが難しい面がある ○ アプリケーションと密結合なテーブル設計になりがち ● Aurora DSQL ならスケールする RDBMS として使える ○ RDBMS のテーブル設計の知見が生かせる ● 一方、Aurora DSQL は通常の RDBMS とは異なる部分も ○ OCC（楽観的同時実行制御）の採用など ○ ここでハマってしまうと「使えない」「難しい」となってしまう 5

Aurora DSQL でハマらないためには？ ● まず通常の RDBMS と異なる部分について理解する必要 がある →ここを伝えたい 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/

トランザクション処理おさらい 10

トランザクション処理とは ● 整合性・一貫性を保つために複数の処理をまとめて一つ の不可分な処理単位として扱う仕組みのこと ○ 例えば A さんの口座から B さんの口座へ 10 万円送金する場合 ■ A さんの口座の残高確認（10 万円未満なら処理中止） ■ A さんの口座から 10 万円減らす ■ B さんの口座に 10 万円加算する を一連の不可分な処理として扱う ○ 途中で障害などが起きたら一連の処理を「なかったこと」にする 11

RDBMS のトランザクション処理 ● 開始からコミットまでを「不可分な処理」として扱う ○ 何らかの理由で処理をなかったことにする場合はロールバック ● オートコミット設定によって動作が変わる ○ オートコミット ON の場合、1 つの SQL 文毎にコミット処理が 行われるが、BEGIN を発行するとトランザクション開始 ■ BEGIN 以降はオートコミットされない ○ オートコミット OFF の場合、最初の SQL 文が発行された時点か らトランザクション開始 12

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

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

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

Aurora DSQL での動作 16

おことわり ● 一部の例ではトランザクションの中で 1 つの SQL 文しか 発行しないケースを示しています ○ 目的：理解しやすくするため ● オートコミット設定 ON のケースを示しています ○ BEGIN を発行することで明示的にトランザクションを開始 17

スナップショット分離（Snapshot Isolation） ● 並行トランザクションで挿入・コミットしたデータ →見えない 18

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

スナップショット分離（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; 20

スナップショット分離（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) 21

OCC / 同じデータ行を更新しようとした場合 1. 最初にコミットしたトランザクションが成功する 2. コミット失敗・ロールバックしたトランザクションとは 競合しない 3. オートコミット ON 設定でトランザクションを実行する 場合、BEGIN の発行がトランザクションの始点になる 22

同じデータ行を更新しようとした場合 [1] ● 最初にコミットしたトランザクションが成功する 23

同じデータ行を更新しようとした場合 [1] ⚫BEGIN ◎UPDATE A ⚫BEGIN ◎UPDATE A ⚫BEGIN ● ◎UPDATE A COMMIT⭕ TxA COMMIT❌ TxB COMMIT❌ 並行トランザクションは「最初に COMMIT」したもの勝ち 24

同じデータ行を更新しようとした場合 [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) 25

同じデータ行を更新しようとした場合 [2] ● コミット失敗・ロールバックしたトランザクションとは 競合しない 26

同じデータ行を更新しようとした場合 [2] ⚫BEGIN ◎UPDATE A COMMIT⭕ ⚫BEGIN TxA ◎UPDATE A TxC ● ⚫BEGIN COMMIT❌ →ROLLBACK ◎UPDATE A TxB COMMIT⭕ TxA の COMMIT 成功⭕後に TxC が BEGIN し、 TxB の COMMIT 失敗❌・ROLLBACK 後に TxC が COMMIT →成功⭕ 27

同じデータ行を更新しようとした場合 [2] ・トランザクション 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; ・トランザクション C（開始〜データ更新） postgres=> BEGIN; postgres=*> UPDATE hoge.fuga SET val = 130 WHERE id = 1; 28

同じデータ行を更新しようとした場合 [2] ・トランザクション 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 | 130 (1 row) 29

同じデータ行を更新しようとした場合 [3] ● オートコミット ON 設定でトランザクションを実行する 場合、BEGIN の発行がトランザクションの始点になる 30

同じデータ行を更新しようとした場合 [3] ⚫BEGIN ◎UPDATE A COMMIT⭕ ⚫BEGIN TxC ● TxA ◎UPDATE A ⚫BEGIN COMMIT❌ →ROLLBACK ◎UPDATE A TxB COMMIT❌ TxA の COMMIT（成功）前に TxC が BEGIN → TxC は COMMIT 時に失敗❌ → BEGIN ～ COMMIT / ROLLBACK の期間が重なっていれば競合対象 31

同じデータ行を更新しようとした場合 [3] ・トランザクション 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; 32

同じデータ行を更新しようとした場合 [3] ・トランザクション 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) 33

更新対象行がトランザクション毎に異なる場合 1. BEGIN ～ COMMIT / ROLLBACK の期間が重なっていて も競合しない 2. 複数テーブル・複数行を更新するトランザクション間で は、更新対象行が重なるものだけが競合する 34

更新対象行がトランザクション毎に異なる場合 [1] ● BEGIN ～ COMMIT / ROLLBACK の期間が重なっていて も競合しない 35

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

更新対象行がトランザクション毎に異なる場合 [2] ● 複数テーブル・複数行を更新するトランザクション間で は、更新対象行が重なるものだけが競合する 37

更新対象行がトランザクション毎に異なる場合 [2] ⚫BEGIN ◎INSERT A ⚫BEGIN COMMIT⭕ ◎INSERT A TxC ● TxA ◎INSERT B ⚫BEGIN COMMIT❌ →ROLLBACK ◎INSERT B TxB COMMIT⭕ TxA と TxC は更新（挿入）対象が重ならない→非競合 TxB が COMMIT 失敗❌・ROLLBACK → TxC の COMMIT は成功⭕ 38

更新対象行がトランザクション毎に異なる場合 [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); 39

更新対象行がトランザクション毎に異なる場合 [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; 40

更新対象行がトランザクション毎に異なる場合 [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; 41

更新対象行がトランザクション毎に異なる場合 [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) 42

書き込みスキュー異常 ● 相互に同じデータを参照しつつ更新対象が重ならない並 行トランザクションの処理では、更新後データの矛盾が 生じる可能性がある ○ 例：〇〇pay 利用時に、家族全員の残高が合計 20 万円未満なら 利用者の口座に 2 万円分加算するプレゼント企画 → 2 人の家族がほぼ同時に〇〇pay を利用したらどうなるか？ 43

書き込みスキュー異常 ⚫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 TxA COMMIT⭕ TxB 「A+B が 20 万未満なら +2 万して UPDATE」 ◎SELECT A （アプリケーションのロジックで判定） ◎SELECT B ⚫BEGIN → 120000・ SELECT は競合しない 100000 UPDATE も競合しない（対象行が別） たとえば A=10 万・B=8 万のときに、本来なら TxA の処理で A が 12 万になるだけ のはずが TxB が並行で進み競合しなかった結果、誤って B も 10 万に 44

書き込みスキュー異常 ● これを回避するには？ ○ OCC ではロックを掛けられない →矛盾が生じうる並行トランザクションを意図的に失敗させる 45

書き込みスキュー異常の回避 ⚫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 TxA COMMIT❌ TxB ⚫BEGIN ● ◎SELECT A ◎SELECT B → 120000・ 80000 SELECT に ... FOR UPDATE を追加して A・B それぞれの行に更新フラグを立てる → TxB は競合扱い→ COMMIT 時に失敗❌（その後リトライしても B は 8 万のまま） 46

書き込みスキュー異常の回避 ・トランザクション 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) 47

書き込みスキュー異常の回避 ・トランザクション 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) 48

書き込みスキュー異常の回避 ・トランザクション 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) 49

書き込みスキュー異常の回避 ・トランザクション 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万なので条件を満たさず →加算せず終了 50

まとめ 51

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