Amazon Aurora DSQL を使ってみる (3) 楽観的同時実行制御(OCC)の確認

1. はじめに

• これまでの記事「Amazon Aurora DSQL を使ってみる (1) 基本的な操作」、「Amazon Aurora DSQL を使ってみる (2) リージョン間同期の遅延確認」にて、基本的な操作方法及びリージョン間同期の遅延速度について検証した。
• Aurora DSQLの気になるポイントとして、「us-east-1側とus-east-2側で同時に同じレコードを更新したらどうなるか？」がある。
• 基本的には「楽観的同時実行制御(OCC)だから、事前にロックをとらずに、COMMITするときに競合の有無を確認して対応する」という仕組みとのことだが、簡単なユースケースを用いて動作確認を行う。

2. やったこと

• Aurora DSQLの検証用テーブル内のレコードに対し、us-east-1側、us-west-1側のエンドポイントから同時に更新処理を行い、挙動を確認する。
• 比較対象として、従来のRDS for PostgreSQL に対し同じ内容のテーブルを作成し、2つのPSQLクライアントから同時に更新処理を行い、Aurora DSQLとの挙動の違いを確認する。

3. 構成図

• Aurora DSQL を作成したのと同じリージョンにSQL文実行用のEC2インスタンス(Amazon Linux 2023, t2.micro)をそれぞれ用意する。
• 比較参考用に、us-east-1に従来のRDS for PostgreSQL(16.3) を作成する。

4. 手順

4.1 AUTOCOMMIT設定

• PSQLクライアントでSQLを実行する際、デフォルト設定だとAUTOCOMMIT=ONになっており、1行毎に自動でCOMMITされてしまう。今回の検証ではUPDATEなどをした後、自動でCOMMITせずに途中で止める必要があるため、AUTOCOMMITされないようにする。
• 以下の方法が可能だが、方法2のほうが明示的で間違いがないため、検証は方法2で実施する。
  • 方法1: AUTOCOMMIT設定をOFFにする。普通にUPDATEなどを行い、最後にCOMMITする。
  • 方法2: AUTOCOMMIT設定はONのままで、BEGIN -> UPDATEなど -> COMMITの順で入力する。

方法1.sql

# AUTOCOMMITをOFFにする
postgres=> \echo :AUTOCOMMIT
on
postgres=> \set AUTOCOMMIT off
postgres=> \echo :AUTOCOMMIT
off
# 普通にUPDATEして最後にCOMMITする
postgres=> UPDATE fruits_list SET number = 20 WHERE id = 1;
postgres=*> COMMIT;

方法2.sql

# AUTOCOMMIT設定はONのままでも、BEGINで始めるとCOMMITするまでが1つのトランザクションになり、行ごとにはCOMMITされなくなる
postgres=> BEGIN;
postgres=*> UPDATE fruits_list SET number = 50 WHERE id = 1;
postgres=*> COMMIT;

4.2 テーブルとレコードの準備

• 検証用のテーブルとレコードを事前に作成する（内容はAurora DSQLとRDS for PostgreSQLで同じものにする）。

createtableanddata.sql

postgres=> CREATE TABLE fruits_list (id INTEGER not null, name VARCHAR(50) not null, number INTEGER not null, PRIMARY KEY(id));
postgres=> INSERT INTO fruits_list (id, name, number) VALUES
(1, 'apple', 10),
(2, 'orange', 10),
(3, 'banana', 10);
postgres=> SELECT * FROM fruits_list;
 id |  name  | number 
----+--------+--------
  1 | apple  |     10
  2 | orange |     10
  3 | banana |     10
(3 rows)

• 上記のテーブルの、appleの行のnumberの値を同時に更新する場合の挙動を確認する。

4.3 Aurora DSQLでの動作確認

us-east-1のPSQLクライアントからAurora DSQLのus-east-1のエンドポイントに接続してTransaction Aを、us-east-2のPSQLクライアントからAurora DSQLのus-east-2のエンドポイントに接続してTransaction Bを実行する。

• ①us-east-1側(Transaction A) で apple の number を 20 にUPDATEして、COMMITはしないでおく。

①us-east-2側(Transaction A)でupdate.sql

postgres=> BEGIN;
postgres=*> UPDATE fruits_list SET number = 20 WHERE id = 1;

• ②us-east-2側ではまだ値が更新されていないことを確認する。

②us-east-2側でSELECT.sql

postgres=> select * from fruits_list;
 id |  name  | number 
----+--------+--------
  1 | apple  |     10
  2 | orange |     10
  3 | banana |     10
(3 rows)

• ③us-east-2側のTransaction B で apple の number を 50 にUPDATEしてCOMMITする。値が更新されていることを確認する。

③us-east-2側(Transaction B)でupdate.sql

postgres=*> BEGIN;
postgres=*> UPDATE fruits_list SET number = 50 WHERE id = 1;
postgres=*> COMMIT;
postgres=> SELECT * FROM fruits_list;
 id |  name  | number 
----+--------+--------
  1 | apple  |     50
  2 | orange |     10
  3 | banana |     10
(3 rows)

• ④us-east-1側のTransaction A でCOMMITするが、競合チェック(対象行が別Transactionにより更新済)により、COMMITできずにエラーになる。

④us-east-1側(Transaction A)でCOMMIT.sql

postgres=*> COMMIT;
ERROR:  change conflicts with another transaction, please retry: (OC000)

• 上記のように、Aurora DSQLの場合は、先にCOMMITしたTransaction B のみが完了した状態になり、最終的なappleのnumberは50になる。Transaction A側はエラーに応じたリトライ処理の実装などを検討する必要がある。
• 今回は別々のリージョンのAurora DSQLエンドポイントへ接続してのTransactionが競合したユースケースとしているが、同一のエンドポイントに対して複数のTransactionが競合する場合も同様の挙動になる。

4.4 (参考) RDS for PostgreSQLでの動作確認

上記と比較して、従来のRDS for PostgreSQLではどうなるんだっけ？を確認する。us-east-1の２つのPSQLクライアントから、us-east-1ののRDS for PostgreSQLに対して接続し、Transaction A／Transaction Bを実行する。

• ①接続1本目(Transaction A) で apple の number を 20 にUPDATEして、COMMITはしないでおく。この時該当行のロックが取得される。

①接続1本目(Transaction A)でupdate.sql

postgres=> BEGIN;
postgres=*> UPDATE fruits_list SET number = 20 WHERE id = 1;
postgres=*> 

• ②接続2本目ではまだ値が更新されていないことを確認する。

②接続2本目でSELECT.sql

postgres=> select * from fruits_list;
 id |  name  | number 
----+--------+--------
  2 | orange |     10
  3 | banana |     10
  1 | apple  |     10
(3 rows)

• ③接続2本目(Transaction B)でapple の number を 50 でUPDATEしようとする。が、Transaction Aにより対象の行がロックされており処理待ちになる。(エラーにはならないが進まず、プロンプトが出ない)

②Transaction Bでupdate.sql

postgres=> BEGIN;
postgres=*> UPDATE fruits_list SET number = 50 WHERE id = 1;

• ④接続1本目(Transaction A)でCOMMITする。（ロックが開放される。）

Transaction AのCOMMIT.sql

postgres=*> COMMIT;
postgres=>

• ⑤ Transaction Aの完了後、接続2本目(Transaction B)のUPDATE処理が再開され完了する。その後COMMITする。

⑤Transaction BのCOMMIT.sql

postgres=*> COMMIT;
postgres=> select * from fruits_list;
 id |  name  | number 
----+--------+--------
  2 | orange |     10
  3 | banana |     10
  1 | apple  |     50
(3 rows)

• 従来のRDS for PostgreSQLの場合、Transaction Aが完了するまでは行がロックされTransaction Bが待ちになるが、Transaction Aが完了後、Transaction Bが再開され完了するため、appleのnumber値はいったん20になってから最後に50になる。

5. 所感

• ロックの制御は奥が深いので、まずは他人に簡単に紹介できるレベルを目的とした基本的な検証しかできなかった。SELECT FOR UPDATEの挙動など、ドキュメントの実行例を確認していたときに気になった不明点を引き続き確認したい。

6. 参考URL