よくある楽観的ロックで見るトランザクション分離レベルの違い（MySQL、PostgreSQL）

これは、なにをしたくて書いたもの？

アプリケーションを作成していると、いわゆる排他の方法のひとつとして楽観的ロックをよく使うと思います。

たとえばこういう定義のテーブルがあったとします。

create table mytable(
  id varchar(3),
  message varchar(100),
  version int,
  primary key(id)
);

こういうデータが入っているとします。

 MySQL  localhost:3306 ssl  practice  SQL > insert into mytable(id, message, version) values('id1', 'Hello', 1);
Query OK, 1 row affected (0.1130 sec)


 MySQL  localhost:3306 ssl  practice  SQL > select * from mytable;
+-----+---------+---------+
| id  | message | version |
+-----+---------+---------+
| id1 | Hello   |       1 |
+-----+---------+---------+
1 row in set (0.0005 sec)

ここから、messageカラムを更新するとします。

よくあるアプリケーションの楽観的ロックの実装だと、こういうSQLを実行すると思います。
※messageカラムも意図的に演算にしています

update
  mytable
set
  message = concat(message, ' from TX-A'),
  version = version + 1
where
  id = 'id1'
  and version = 1;

この時、同時に実行している別のトランザクションから更新をかけたとします。

update
  mytable
set
  message = concat(message, ' from TX-B'),
  version = version + 1
where
  id = 'id1'
  and version = 1;

この時、トランザクション分離レベルとしてなにを指定しているのかで、どのようなことが起こるのかを確認してみたいというのが今回のお題です。

特にREPEATABLE READの時がどうなるか気になるんですよね。

確認のメインはMySQLとしますが、比較のためにPostgreSQLでも試しておこうと思います。

MySQLとPostgreSQLで使えるトランザクション分離レベル

最初にトランザクション分離レベルについて確認しておきます。

トランザクション分離レベル	説明
READ UNCOMMITTED	他のトランザクションの未コミットのデータを読み取る
READ COMMITTED	他のトランザクションのコミット済みのデータを読み取る
REPEATABLE READ	トランザクションが開始されるまでにコミットされたデータのみを参照するが、他のトランザクションが追加または削除したデータを読み取る
SERIALIZABLE	トランザクションを順次実行したかのような振る舞いをするが、同時実行性が最も低い

MySQLで設定できるトランザクション分離レベルは以下です。

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

デフォルトはREPEATABLE READです。

MySQL :: MySQL 8.4 Reference Manual :: 17.7.2.1 Transaction Isolation Levels

SET TRANSACTIONを使うことで、トランザクション分離レベルを変更できます。

MySQL :: MySQL 8.4 Reference Manual :: 15.3.7 SET TRANSACTION Statement

PostgreSQLで設定できるトランザクション分離レベルは以下です。

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

ただし、READ UNCOMMITTEDはREAD COMMITTEDのように動作するとされています。というか、ドキュメントには内部的に3つの
分離レベルしか実装されていない、と書かれていますね。デフォルトはREAD COMMITTEDです。

PostgreSQL: Documentation: 16: 13.2. Transaction Isolation

13.2. トランザクションの分離

PostgreSQLもSET TRANSACTIONを使うことで、トランザクション分離レベルを変更できます。

PostgreSQL: Documentation: 16: SET TRANSACTION

SET TRANSACTION

BEGIN TRANSACTIONにISOLATION LEVELを付与することでも可能なようです。

ところで、トランザクション分離レベルを変更するとどのようなことが発生するのでしょうか？

A Critique of ANSI SQL Isolation Levels

ANSI SQL-92ではトランザクションにおいて発生しうる事象を、以下のように定義しています。

名称	発生する現象
P1： Dirty Read	他のトランザクションの未コミットのデータを読み取ってしまう
P2： Non-Reatable Read	他のトランザクションがコミットしたデータや行が削除されたことを読み取ってしまう（最初に読み取った結果から変わってしまう）
P3： Phantom （Read）	結果が複数行になるクエリーを実行する際に、別のトランザクションの変更（追加行）を検出してしまう（最初に読み取った結果の集合から変わってしまう）

ISO/IEC 9075:1992, Database Language SQL（ANSI SQL-92 Standard)

トランザクション分離レベルと発生する現象の組み合わせは、以下のようになっています。

このANSI SQL-92で定義された分離レベルでは特徴を整理しきれていないとして、整理し直したとする論文です。

[cs/0701157] A Critique of ANSI SQL Isolation Levels

PDFはこちら。

A Critique of ANSI SQL Isolation Levels

この論文でまとめられている事象はこちら。

名称	発生する現象
P0： Dirty Write	他のトランザクションの未コミットのデータを上書きしてしまう
P1： Dirty Read	他のトランザクションの未コミットのデータを読み取ってしまう
P4C： Cursor Lost Update	カーソルで読み込んでいるデータ（Locking Read）に対して、他のトランザクションが変更したデータを上書きしてしまう
P4： Lost Update	他のトランザクションがコミットしたデータを上書きしてしまう
P2： Fuzzy Read	他のトランザクションが変更した（コミットした）データや行が削除されたことを読み取ってしまう（最初に読み取った結果から変わってしまう）
P3： Phantom	結果が複数行になるクエリーを実行する際に、別のトランザクションの変更（追加行）を検出してしまう（最初に読み取った結果の集合から変わってしまう）
A5A： Read Skew	他のトランザクションの未コミットのデータを読み取ってしまう（P2との違いは、複数のデータを読み取る時に最初から他のトランザクションのコミット済みのデータを見てしまい、一貫性がなくなってしまうこと）
A5B： Write Skew	あるトランザクションT1がxの値を読み取ってyの値を変更し、別のトランザクションT2がyの値を読み取ってxの値を変更する時、一貫性のない変更になってしまうこと（たとえばxまたはyの値をその逆にコピーする処理を考えた時（初期値をx=10、y=50）に、T1 read（x=10）、T2 read（y=50）、T1 write（y=10）、T2 write（x=50）、結果x=50、y=10となり、本来xとyは等しくなる処理のはずなのに一貫性がなくなる）

定義としてはこうなっているみたいです。

P0: w1[x]...w2[x]...((c1 or a1) and (c2 or a2) in any order)
P1: w1[x]...r2[x]...((c1 or a1) and (c2 or a2) in any order)
P4C: rc1[x]...w2[x]...w1[x]...c1 (Lost Update)
P4: r1[x]...w2[x]...w1[x]...c1 (Lost Update)
P2: r1[x]...w2[x]...((c1 or a1) and (c2 or a2) in any order)
P3: r1[P]...w2[y in P]...((c1 or a1) and (c2 or a2) any order)  
A5A: r1[x]...w2[x]...w2[y]...c2...r1[y]...(c1 or a1)  (Read Skew)
A5B: r1[x]...r2[y]...w1[y]...w2[x]...(c1 and c2 occur)  (Write Skew) 

表記の意味はこうなっているみたいです。

• w1[x] … トランザクション1がデータxに書き込んだ
• r2[x] … トランザクション2がデータxを読み取った
• r1[P]、w1[P] … トランザクション1による、述語Pを満たす範囲への読み取り、書き込み
• c1 … トランザクション1のコミット
• a1 … トランザクション2のアボート（ロールバック）
• rc1[x] … トランザクション1でカーソルの現在位置のレコードを読み取った
• wc1[x] … トランザクション1でカーソルの現在位置のレコードに書き込んだ

ちなみに、ANSI SQL-92ではP1、P2、P3はA1、A2、A3として以下の定義になっています。

A1: w1[x]...r2[x]...(a1 and c2 in either order) (Dirty Read)
A2: r1[x]...w2[x]...c2...r1[x]...c1 (Fuzzy or Non-Repeatable Read)
A3: r1[P]...w2[y in P]...c2....r1[P]...c1 (Phantom) 

よく見るとA2とA3はc1、c2（コミット）がついていますが、この論文のP2、P3ではコミットが外れているので範囲が広くなっているようです。

この論文ではトランザクション分離レベルと発生する現象の組み合わせは、以下のようになっています。
※ANSI SQL-92からCursor StabilityとSnapshot Isolationという分離レベルが追加されています

で、実際のところこれらの問題とMySQLやPostgreSQLの各トランザクション分離レベルの組み合わせでどのような動作になるのかは、
以下のページを参考に。

MySQL/Postgres におけるトランザクション分離レベルと発生するアノマリーを整理する

実は「A Critique of ANSI SQL Isolation Levels」の論文を見ている段階でもともとの趣旨からはちょっと脱線気味になっていて、 このあたりは十分に読み解けていないのでまた別の機会に読み込もうと思います…。

参考

その他、トランザクション分離レベルや発生する問題（アノマリー）の話。

トランザクション分離レベルの古典的論文 A Critique of ANSI SQL Isolation Levels を読む - Hatena Developer Blog

Isolation Levelの階層 #ポエム - Qiita

いろんなAnomaly #ポエム - Qiita

トランザクションの最先端研究 | 分離レベルの追跡・究明―TiDBの分離レベルを理解する（上） #初心者 - Qiita

環境

今回の環境はこちら。

MySQL。

$ mysqlsh myuser@localhost:3306/mydatabase --sql


 MySQL  localhost:3306 ssl  mydatabase  SQL > select version();
+-----------+
| version() |
+-----------+
| 8.4.0     |
+-----------+
1 row in set (0.0010 sec)

PostgreSQL。

$ psql -h localhost -U myuser mydatabase
ユーザー myuser のパスワード:
psql (16.3 (Ubuntu 16.3-1.pgdg22.04+1))
SSL接続(プロトコル: TLSv1.3、暗号化方式: TLS_AES_256_GCM_SHA384、圧縮: オフ)
"help"でヘルプを表示します。

mydatabase=> select version();
                                                              version
-----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 16.3 (Ubuntu 16.3-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
(1 行)

mydatabase=>

デフォルトのトランザクション分離レベルを確認する

最初に、それぞれのトランザクション分離レベルを確認しておきましょう。

MySQL。

 MySQL  localhost:3306 ssl  mydatabase  SQL > show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.0057 sec)

PostgreSQL。

mydatabase=> show default_transaction_isolation;
 default_transaction_isolation
-------------------------------
 read committed
(1 行)

では、確認していきましょう。

MySQL

最初はMySQLで。

READ UNCOMMITTED

まずはREAD UNCOMMITTEDから。

トランザクション開始（TX-A）。

-- TX-A
 MySQL  localhost:3306 ssl  mydatabase  SQL > start transaction;
Query OK, 0 rows affected (0.0007 sec)
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.0008 sec)

MySQL Shellではトランザクション内ではプロンプトに「★」という表示が付きます。

トランザクション開始（TX-B）。

-- TX-B
 MySQL  localhost:3306 ssl  mydatabase  SQL > start transaction;
Query OK, 0 rows affected (0.0009 sec)
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.0008 sec)

データ読み取り（TX-A）。

-- TX-A
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > select * from mytable where id = 'id1';
+-----+---------+---------+
| id  | message | version |
+-----+---------+---------+
| id1 | Hello   |       1 |
+-----+---------+---------+
1 row in set (0.0016 sec)

データ読み取り（TX-B）。

-- TX-B
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > select * from mytable where id = 'id1';
+-----+---------+---------+
| id  | message | version |
+-----+---------+---------+
| id1 | Hello   |       1 |
+-----+---------+---------+
1 row in set (0.0014 sec)

データ書き込み（TX-A）。

-- TX-A
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > update mytable set message = concat(message, ' from TX-A'), version = version + 1 where id = 'id1' and version = 1;
Query OK, 1 row affected (0.0015 sec)

Rows matched: 1  Changed: 1  Warnings: 0

データ書き込み（TX-B）。

-- TX-B
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1;

（待機）

このまま更新できずに待ってしまいます。

コミット（TX-A）。

-- TX-A
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > commit;
Query OK, 0 rows affected (0.0972 sec)

コミットすると、TX-Bの方が進みます。更新できていませんが。

 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1;
Query OK, 0 rows affected (30.0974 sec)

Rows matched: 0  Changed: 0  Warnings: 0

これは、他のトランザクションのコミット済みのデータが見えてしまうので（READ UNCOMMITTEDなのでコミットしなくても
見えてしまうはずですが）、検索条件に一致しなくなり更新できなくなったということかと。

確認（TX-A）。

-- TX-A
 MySQL  localhost:3306 ssl  mydatabase  SQL > select * from mytable where id = 'id1';
+-----+-----------------+---------+
| id  | message         | version |
+-----+-----------------+---------+
| id1 | Hello from TX-A |       2 |
+-----+-----------------+---------+
1 row in set (0.0020 sec)

コミット（TX-B）。

-- TX-B
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > commit;
Query OK, 0 rows affected (0.0011 sec)

最終的な結果は、こちらですね。

 MySQL  localhost:3306 ssl  mydatabase  SQL > select * from mytable where id = 'id1';
+-----+-----------------+---------+
| id  | message         | version |
+-----+-----------------+---------+
| id1 | Hello from TX-A |       2 |
+-----+-----------------+---------+
1 row in set (0.0010 sec)

READ COMMITTED

READ COMMITTED。

トランザクション開始（TX-A）。

-- TX-A
 MySQL  localhost:3306 ssl  mydatabase  SQL > start transaction;
Query OK, 0 rows affected (0.0011 sec)
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > set session transaction isolation level read committed;
Query OK, 0 rows affected (0.0011 sec)

トランザクション開始（TX-B）。

-- TX-B
 MySQL  localhost:3306 ssl  mydatabase  SQL > start transaction;
Query OK, 0 rows affected (0.0006 sec)
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > set session transaction isolation level read committed;
Query OK, 0 rows affected (0.0007 sec)

データ読み取り（TX-A）。

-- TX-A
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > select * from mytable where id = 'id1';
+-----+---------+---------+
| id  | message | version |
+-----+---------+---------+
| id1 | Hello   |       1 |
+-----+---------+---------+
1 row in set (0.0019 sec)

データ読み込み（TX-B）。

-- TX-B
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > select * from mytable where id = 'id1';
+-----+---------+---------+
| id  | message | version |
+-----+---------+---------+
| id1 | Hello   |       1 |
+-----+---------+---------+
1 row in set (0.0009 sec)

データ書き込み（TX-A）。

-- TX-A
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > update mytable set message = concat(message, ' from TX-A'), version = version + 1 where id = 'id1' and version = 1;
Query OK, 1 row affected (0.0023 sec)

Rows matched: 1  Changed: 1  Warnings: 0

データ書き込み（TX-B）。こちらは、このまま待ちになります。

-- TX-B
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1;

（待機）

コミット（TX-A）。

-- TX-A
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > commit;
Query OK, 0 rows affected (0.0173 sec)

すると、TX-Bが進みます。空振りになりますが。

-- TX-B
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1;

Query OK, 0 rows affected (27.2145 sec)

Rows matched: 0  Changed: 0  Warnings: 0

こちらも他のトランザクションのコミット済みのデータが見えてしまうからですね。

コミット（TX-B）。

-- TX-B
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > commit;
Query OK, 0 rows affected (0.0009 sec)

最終結果はこちらですね。

 MySQL  localhost:3306 ssl  mydatabase  SQL > select * from mytable where id = 'id1';
+-----+-----------------+---------+
| id  | message         | version |
+-----+-----------------+---------+
| id1 | Hello from TX-A |       2 |
+-----+-----------------+---------+
1 row in set (0.0014 sec)

REPEATABLE READ

REPEATABLE READ。

トランザクション開始（TX-A）。

-- TX-A
 MySQL  localhost:3306 ssl  mydatabase  SQL > start transaction;
Query OK, 0 rows affected (0.0009 sec)
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.0010 sec)

トランザクション開始（TX-B）。

-- TX-B
 MySQL  localhost:3306 ssl  mydatabase  SQL > start transaction;
Query OK, 0 rows affected (0.0007 sec)
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.0007 sec)

データ読み取り（TX-A）。

-- TX-A
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > select * from mytable where id = 'id1';
+-----+---------+---------+
| id  | message | version |
+-----+---------+---------+
| id1 | Hello   |       1 |
+-----+---------+---------+
1 row in set (0.0014 sec)

データ読み取り（TX-B）。

-- TX-B
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > select * from mytable where id = 'id1';
+-----+---------+---------+
| id  | message | version |
+-----+---------+---------+
| id1 | Hello   |       1 |
+-----+---------+---------+
1 row in set (0.0019 sec)

データ書き込み（TX-A）。

-- TX-A
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > update mytable set message = concat(message, ' from TX-A'), version = version + 1 where id = 'id1' and version = 1;
Query OK, 1 row affected (0.0021 sec)

Rows matched: 1  Changed: 1  Warnings: 0

データ書き込み（TX-B）。そのまま待ちになります。

-- TX-B
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1;

（待機）

コミット（TX-A）。

-- TX-A
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > commit;
Query OK, 0 rows affected (0.0188 sec)

すると、TX-Bが進みます。空振りになりましたね…？

-- TX-B
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1;
Query OK, 0 rows affected (17.8448 sec)

Rows matched: 0  Changed: 0  Warnings: 0

REPEATABLE READだと、他のトランザクションのコミットしたデータは見えないはずですが、どうなっているんでしょう。

コミット（TX-B）。

-- TX-B
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > commit;
Query OK, 0 rows affected (0.0015 sec)

最終結果。

 MySQL  localhost:3306 ssl  mydatabase  SQL > select * from mytable where id = 'id1';
+-----+-----------------+---------+
| id  | message         | version |
+-----+-----------------+---------+
| id1 | Hello from TX-A |       2 |
+-----+-----------------+---------+
1 row in set (0.0013 sec)

ちょっと気になるので、もう1度確認してみます。

トランザクション開始（TX-A）。

-- TX-A
 MySQL  localhost:3306 ssl  mydatabase  SQL > start transaction;
Query OK, 0 rows affected (0.0007 sec)
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.0009 sec)

トランザクション開始（TX-B）。

-- TX-B
 MySQL  localhost:3306 ssl  mydatabase  SQL > start transaction;
Query OK, 0 rows affected (0.0009 sec)
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.0008 sec)

データ読み取り（TX-A）。

-- TX-A
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > select * from mytable where id = 'id1';
+-----+---------+---------+
| id  | message | version |
+-----+---------+---------+
| id1 | Hello   |       1 |
+-----+---------+---------+
1 row in set (0.0014 sec)

データ読み取り（TX-B）。

-- TX-B
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > select * from mytable where id = 'id1';
+-----+---------+---------+
| id  | message | version |
+-----+---------+---------+
| id1 | Hello   |       1 |
+-----+---------+---------+
1 row in set (0.0015 sec)

データ更新（TX-A）。

-- TX-A
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > update mytable set message = concat(message, ' from TX-A'), version = version + 1 where id = 'id1' and version = 1;
Query OK, 1 row affected (0.0022 sec)

Rows matched: 1  Changed: 1  Warnings: 0

この時、TX-Bからデータを見てみます。

--TX-B
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > select * from mytable where id = 'id1';
+-----+---------+---------+
| id  | message | version |
+-----+---------+---------+
| id1 | Hello   |       1 |
+-----+---------+---------+
1 row in set (0.0019 sec)

TX-Aの変更したデータは見えていません。

TX-Aをコミットしてみます。

-- TX-A
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > commit;
Query OK, 0 rows affected (0.0147 sec)

もう1度TX-Bで読み込み。

-- TX-B
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > select * from mytable where id = 'id1';
+-----+---------+---------+
| id  | message | version |
+-----+---------+---------+
| id1 | Hello   |       1 |
+-----+---------+---------+
1 row in set (0.0023 sec)

TX-Aのコミット前のデータが見えています。

データ書き込み（TX-B）。

-- TX-B
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1;
Query OK, 0 rows affected (0.0021 sec)

Rows matched: 0  Changed: 0  Warnings: 0

空振りますね。

どうなっているんでしょう？？？

ここで、select文にfor updateを付けてみます。

-- TX-B
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > select * from mytable where id = 'id1' for update;
+-----+-----------------+---------+
| id  | message         | version |
+-----+-----------------+---------+
| id1 | Hello from TX-A |       2 |
+-----+-----------------+---------+
1 row in set (0.0015 sec)

なんと、他のトランザクションのコミット済みのデータが見えてしまいました。

versionカラムも含めてみましょう。

-- TX-B
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > select * from mytable where id = 'id1' and version = 1 for update;
Empty set (0.0012 sec)



 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > select * from mytable where id = 'id1' and version = 2 for update;
+-----+-----------------+---------+
| id  | message         | version |
+-----+-----------------+---------+
| id1 | Hello from TX-A |       2 |
+-----+-----------------+---------+
1 row in set (0.0014 sec)

これと同じ挙動になったので、TX-Bのupdate文は空振りになったと考えるのがよさそうです。

コミット（TX-B）。

-- TX-B
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > commit;
Query OK, 0 rows affected (0.0011 sec)

最終結果。

 MySQL  localhost:3306 ssl  mydatabase  SQL > select * from mytable where id = 'id1';
+-----+-----------------+---------+
| id  | message         | version |
+-----+-----------------+---------+
| id1 | Hello from TX-A |       2 |
+-----+-----------------+---------+
1 row in set (0.0011 sec)

トランザクション分離レベルのダウングレード

こちらを見ると、ロック付きの読み取り（Locking Read）ではトランザクション分離レベルのダウングレード（READ COMMITTED相当に
なってしまう）が行われるようなのですが、今回の動きはこちらを再現したことになりそうですね。

MySQL/Postgres におけるトランザクション分離レベルと発生するアノマリーを整理する

ちょっと驚きました…。

REPEATABLE READ WITH CONSISTENT SNAPSHOT

MySQLでは、トランザクション分離レベルがREPEATABLE READ WITH の時に限りWITH CONSISTENT SNAPSHOTをつけることで
Snapshot Isolationと同等のトランザクション分離レベルにすることができるようです。

 MySQL  localhost:3306 ssl  mydatabase  SQL > start transaction with consistent snapshot;
Query OK, 0 rows affected (0.0014 sec)

MySQL :: MySQL 8.4 Reference Manual :: 15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements

ちょっと試したみたのですが、今回の内容だとREPEATABLE READと動きに差がなかったので結果は割愛します。

SERIALIZABLE

SERIALIABLE。

トラザクション開始（TX-A）。

-- TX-A
 MySQL  localhost:3306 ssl  mydatabase  SQL > start transaction;
Query OK, 0 rows affected (0.0010 sec)
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > set session transaction isolation level serializable;
Query OK, 0 rows affected (0.0010 sec)

トランザクション開始（TX-B）。

-- TX-B
 MySQL  localhost:3306 ssl  mydatabase  SQL > start transaction;
Query OK, 0 rows affected (0.0011 sec)
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > set session transaction isolation level serializable;
Query OK, 0 rows affected (0.0003 sec)

データ読み込み（TX-A）。

-- TX-A
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > select * from mytable where id = 'id1';
+-----+---------+---------+
| id  | message | version |
+-----+---------+---------+
| id1 | Hello   |       1 |
+-----+---------+---------+
1 row in set (0.0013 sec)

データ読み込み（TX-B）。

-- TX-B
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > select * from mytable where id = 'id1';
+-----+---------+---------+
| id  | message | version |
+-----+---------+---------+
| id1 | Hello   |       1 |
+-----+---------+---------+
1 row in set (0.0016 sec)

データ書き込み（TX-A）。

-- TX-A
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > update mytable set message = concat(message, ' from TX-A'), version = version + 1 where id = 'id1' and version = 1;
Query OK, 1 row affected (0.0023 sec)

Rows matched: 1  Changed: 1  Warnings: 0

データ書き込み（TX-B）。

-- TX-B
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1;

（待機）

コミット（TX-A）。

-- TX-A
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > commit;
Query OK, 0 rows affected (0.0550 sec)

TX-Bは進み、空振りになります。

-- TX-B
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1;
Query OK, 0 rows affected (24.9590 sec)

Rows matched: 0  Changed: 0  Warnings: 0

コミット（TX-B）。

-- TX-B
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > commit;
Query OK, 0 rows affected (0.0014 sec)

最終結果。

 MySQL  localhost:3306 ssl  mydatabase  SQL > select * from mytable where id = 'id1';
+-----+-----------------+---------+
| id  | message         | version |
+-----+-----------------+---------+
| id1 | Hello from TX-A |       2 |
+-----+-----------------+---------+
1 row in set (0.0019 sec)

REPEATABLE READと同じ動作になりましたね。

ちなみに、SERIALIZABLEであってもロック付きの読み込みだと他のトランザクションのコミット済みのデータを読み取ることができます…。

-- TX-B
 MySQL  localhost:3306 ssl  mydatabase  ★  SQL > select * from mytable where id = 'id1' for update;
+-----+-----------------+---------+
| id  | message         | version |
+-----+-----------------+---------+
| id1 | Hello from TX-A |       2 |
+-----+-----------------+---------+
1 row in set (0.0012 sec)

PostgreSQL

最後はPostgreSQLで。

READ UNCOMMITTED

READ UNCOMMITTED。ドキュメントを見る限りREAD COMMITTEDと同じ動作のようですが、一応やっておきます。

トランザクション開始（TX-A）。

-- TX-A
mydatabase=> start transaction;
START TRANSACTION
mydatabase=*> set transaction isolation level read uncommitted;
SET

PostgreSQLの場合、トランザクション内ではプロンプトに「*」という表示が付きます。

トランザクション開始（TX-B）。

-- TX-B
mydatabase=> start transaction;
START TRANSACTION
mydatabase=*> set transaction isolation level read uncommitted;
SET

データ読み取り（TX-A）。

-- TX-A
mydatabase=*> select * from mytable where id = 'id1';
 id  | message | version
-----+---------+---------
 id1 | Hello   |       1
(1 行)

データ読み取り（TX-B）。

-- TX-B
mydatabase=*> select * from mytable where id = 'id1';
 id  | message | version
-----+---------+---------
 id1 | Hello   |       1
(1 行)

データ書き込み（TX-A）。

-- TX-A
mydatabase=*> update mytable set message = concat(message, ' from TX-A'), version = version + 1 where id = 'id1' and version = 1;
UPDATE 1

データ書き込み（TX-B）。ここでは待ちになります。

-- TX-B
mydatabase=*> update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1;

（待機）

コミット（TX-A）。

-- TX-A
mydatabase=*> commit;
COMMIT

この時、TX-Bは空振りします。

-- TX-B
mydatabase=*> update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1;
UPDATE 0

コミット（TX-B）。

-- TX-B
mydatabase=*> commit;
COMMIT

最終結果。

mydatabase=> select * from mytable where id = 'id1';
 id  |     message     | version
-----+-----------------+---------
 id1 | Hello from TX-A |       2
(1 行)

READ COMMITTED

READ COMMITTED。

トランザクション開始（TX-A）。

-- TX-A
mydatabase=> start transaction;
START TRANSACTION
mydatabase=*> set transaction isolation level read committed;
SET

トランザクション開始（TX-B）。

-- TX-B
mydatabase=> start transaction;
START TRANSACTION
mydatabase=*> set transaction isolation level read committed;
SET

データ読み取り（TX-A）。

-- TX-A
mydatabase=*> select * from mytable where id = 'id1';
 id  | message | version
-----+---------+---------
 id1 | Hello   |       1
(1 行)

データ読み取り（TX-B）。

-- TX-B
mydatabase=*> select * from mytable where id = 'id1';
 id  | message | version
-----+---------+---------
 id1 | Hello   |       1
(1 行)

データ書き込み（TX-A）。

-- TX-A
mydatabase=*> update mytable set message = concat(message, ' from TX-A'), version = version + 1 where id = 'id1' and version = 1;
UPDATE 1

データ書き込み（TX-B）。

-- TX-B
mydatabase=*> update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1;

（待機）

コミット（TX-A）。

-- TX-A
mydatabase=*> commit;
COMMIT

TX-Bは動き出し、空振りします。

-- TX-B
mydatabase=*> update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1;
UPDATE 0

コミット（TX-B）。

-- TX-B
mydatabase=*> commit;
COMMIT

最終結果。

mydatabase=> select * from mytable where id = 'id1';
 id  |     message     | version
-----+-----------------+---------
 id1 | Hello from TX-A |       2
(1 行)

ここまでは、MySQLと同じですね。

REPEATABLE READ

REPEATABLE READ。

トランザクション開始（TX-A）。

-- TX-A
mydatabase=> start transaction;
START TRANSACTION
mydatabase=*> set transaction isolation level repeatable read;
SET

トランザクション開始（TX-B）。

-- TX-B
mydatabase=> start transaction;
START TRANSACTION
mydatabase=*> set transaction isolation level repeatable read;
SET

データ読み取り（TX-A）。

-- TX-A
mydatabase=*> select * from mytable where id = 'id1';
 id  | message | version
-----+---------+---------
 id1 | Hello   |       1
(1 行)

データ読み取り（TX-B）。

-- TX-B
mydatabase=*> select * from mytable where id = 'id1';
 id  | message | version
-----+---------+---------
 id1 | Hello   |       1
(1 行)

データ書き込み（TX-A）。

-- TX-A
mydatabase=*> update mytable set message = concat(message, ' from TX-A'), version = version + 1 where id = 'id1' and version = 1;
UPDATE 1

データ書き込み（TX-B）。

-- TX-B
mydatabase=*> update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1;

（待機）

コミット（TX-A）。

-- TX-A
mydatabase=*> commit;
COMMIT

すると、TX-B側はエラーになります。

-- TX-B
mydatabase=*> update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1;
ERROR:  could not serialize access due to concurrent update

PostgreSQLだとこういう挙動になって矛盾を防ごうとするんですね。ある意味、データベース側で楽観的排他制御のようなことを
やってくれる印象を持ちますね。

この状態になると、TX-B側はロールバックするしかありません。

-- TX-B
mydatabase=!> select * from mytable where id = 'id1';
ERROR:  current transaction is aborted, commands ignored until end of transaction block

ちなみに、コミットしてもロールバックするんですね。

-- TX-B
mydatabase=!> commit;
ROLLBACK

最終結果。

mydatabase=> select * from mytable where id = 'id1';
 id  |     message     | version
-----+-----------------+---------
 id1 | Hello from TX-A |       2
(1 行)

ちなみに、MySQLと違ってロック付きで読み取りをしても他のトランザクションがコミットしたデータが見えるということはありません。

-- TX-B
mydatabase=*> select * from mytable where id = 'id1';
 id  |     message     | version
-----+-----------------+---------
 id1 | Hello from TX-A |       2
(1 行)

mydatabase=*> select * from mytable where id = 'id1' for update;
 id  |     message     | version
-----+-----------------+---------
 id1 | Hello from TX-A |       2
(1 行)

SERIALIZABLE

SERIALIZABLE。

トランザクション開始（TX-A）。

-- TX-A
mydatabase=> start transaction;
START TRANSACTION
mydatabase=*> set transaction isolation level serializable;
SET

トランザクション開始（TX-B）。

-- TX-B
mydatabase=> start transaction;
START TRANSACTION
mydatabase=*> set transaction isolation level serializable;
SET

データ読み取り（TX-A）。

-- TX-A
mydatabase=*> select * from mytable where id = 'id1';
 id  | message | version
-----+---------+---------
 id1 | Hello   |       1
(1 行)

データ読み取り（TX-B）。

-- TX-B
mydatabase=*> select * from mytable where id = 'id1';
 id  | message | version
-----+---------+---------
 id1 | Hello   |       1
(1 行)

データ書き込み（TX-A）。

-- TX-A
mydatabase=*> update mytable set message = concat(message, ' from TX-A'), version = version + 1 where id = 'id1' and version = 1;
UPDATE 1

データ書き込み（TX-B）。

-- TX-B
mydatabase=*> update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1;

（待機）

コミット（TX-A）。

-- TX-A
mydatabase=*> commit;
COMMIT

TX-B側は失敗します。

-- TX-B
mydatabase=*> update mytable set message = concat(message, ' from TX-B'), version = version + 1 where id = 'id1' and version = 1;
ERROR:  could not serialize access due to concurrent update

ロールバック（TX-B）。

-- TX-B
mydatabase=!> rollback;
ROLLBACK

最終結果。

mydatabase=> select * from mytable where id = 'id1';
 id  |     message     | version
-----+-----------------+---------
 id1 | Hello from TX-A |       2
(1 行)

おわりに

よくある楽観的ロックをテーマに、MySQLとPostgreSQLで各トランザクション分離レベルでどのような動作になるかを確認してみました。

MySQLのREPEATABLE READでのトランザクション分離レベルのダウングレードがなかなか衝撃的で、この分離レベルで期待する
読み取り一貫性を突き破っていてかなり驚きました。
一方で、PostgreSQLは更新を止めてしまうんだな、と。

よくあるRDBMSのうち、MySQLのみデフォルトがREPEATABLE READですが、一貫性が欲しいところでロック付けた読み取りをしたりすると
むしろ一貫性を損なうことになりそうなので気をつけた方がいいんだなと思いました。

いや、勉強になりましたね…。

アノマリーの話もそうなのですが、データベースに関する知識が浅いなと思ったのでもうちょっと勉強しようと思います。