これは、なにをしたくて書いたもの？
アプリケーションを作成していると、いわゆる排他の方法のひとつとして楽観的ロックをよく使うと思います。
たとえばこういう定義のテーブルがあったとします。
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
PostgreSQLもSET TRANSACTIONを使うことで、トランザクション分離レベルを変更できます。
PostgreSQL: Documentation: 16: 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
トランザクションの最先端研究 | 分離レベルの追跡・究明―TiDBの分離レベルを理解する（上） #初心者 - Qiita
環境
今回の環境はこちら。
$ 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)
$ 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 localhost:3306 ssl mydatabase SQL > show variables like 'transaction_isolation'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | +-----------------------+-----------------+ 1 row in set (0.0057 sec)
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ですが、一貫性が欲しいところでロック付けた読み取りをしたりすると
むしろ一貫性を損なうことになりそうなので気をつけた方がいいんだなと思いました。
いや、勉強になりましたね…。
アノマリーの話もそうなのですが、データベースに関する知識が浅いなと思ったのでもうちょっと勉強しようと思います。
