CLOVER🍀

That was when it all began.

よくある楽観的ロックで見るトランザクション分離レベルの違い(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) 

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

ちなみに、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ですが、一貫性が欲しいところでロック付けた読み取りをしたりすると
むしろ一貫性を損なうことになりそうなので気をつけた方がいいんだなと思いました。

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

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

TiDBでユーザーを作成する

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

これまでTiDBを使う時には、tiup playgroundで起動してそのままrootユーザー(しかもパスワードなし)で接続していたのですが、
通常のユーザーアカウントを作成する方法も押さえておいた方がよいかなと思いまして。

それから、このあたりのドキュメントがTiDBにどうまとまっているのかを押さえておくことも目的ですね。

TiDBにユーザーを追加する

結論から言うと、MySQLと同じくCREATE USER文を使います。

CREATE USER | TiDB SQL Statement Reference | PingCAP Docs

アクセス元を指定してユーザーを作成するのもの同じなようですね。

とはいえ、MySQLと完全に同じではないようで、以下が非互換のようです。

  • WITH MAX_QUERIES_PER_HOURTiDB、WITH MAX_UPDATES_PER_HOUR、WITH MAX_USER_CONNECTIONSオプションはサポートしていない
  • DEFAULT ROLEオプションをサポートしていない

CREATE USER / MySQL compatibility

TiDBとMySQLとのセキュリティ面での互換性は、以下のページに記載されています。

Security Compatibility with MySQL | PingCAP Docs

WITH MAX_QUERIES_PER_HOURTiDB、WITH MAX_UPDATES_PER_HOUR、WITH MAX_USER_CONNECTIONSオプションが
未サポートなことも、ここで出てきます(DEFAULT ROLEの話はありませんが…)。

また、アカウント管理に関する情報はこちらにまとまっています。

TiDB User Account Management | PingCAP Docs

デフォルトで作成されるrootアカウントについては、こちらに記載がありますね。

TiDB User Account Management / Reserved user accounts

というわけで、動作確認しておきましょう。

環境

今回の環境はこちら。

$ tiup --version
1.15.2 v1.15.0-nightly-20
Go Version: go1.21.11
Git Ref: master
GitHash: 423dd0d00e17aaea6b907cdcedaf63dd2411a2a8

TiDBは以下のコマンドで起動し、172.17.0.2で動作しているものとします。

$ tiup playground v8.1.0 --host 0.0.0.0

TiDBのバージョン。

 MySQL  localhost:4000 ssl  SQL > select version();
+--------------------+
| version()          |
+--------------------+
| 8.0.11-TiDB-v8.1.0 |
+--------------------+
1 row in set (0.0016 sec)

使用するMySQLクライアント。

$ mysqlsh --version
mysqlsh   Ver 8.0.37 for Linux on x86_64 - for MySQL 8.0.37 (MySQL Community Server (GPL))

クライアントであるMySQL Shellは、TiDBが動作しているサーバーおよび別のサーバーから利用するものとします。

TiDBにユーザーを作成する

まずはTiDBに接続します。TiDBの接続はtiup clientでも行えますが、今回はすべてMySQL Shellで行います。

$ mysqlsh root:@localhost:4000 --sql

ついでなので、新しいユーザー用のデータベースも作成しましょう。

 MySQL  localhost:4000 ssl  SQL > create database mydatabase;
Query OK, 0 rows affected (0.4298 sec)

Create a Database | PingCAP Docs

ユーザーを作成。

 MySQL  localhost:4000 ssl  SQL > create user myuser@'%' identified by 'myuser-password';
Query OK, 0 rows affected (1.2969 sec)

%はワイルドカードです。今回はどのサーバーからの接続も許可することにします。

作成したユーザーに、最初に作成したデータベースのすべての権限を与えます。

 MySQL  localhost:4000 ssl  SQL > grant all privileges on mydatabase.* to myuser@'%';
Query OK, 0 rows affected (0.9754 sec)

GRANT <privileges> | TiDB SQL Statement Reference | PingCAP Docs

作成したユーザーで接続してみます。今回はデータベースを直接指定します。

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

テーブルが作成できることなどを確認。

 MySQL  localhost:4000 ssl  mydatabase  SQL > create table t1(c1 int, primary key(c1));
Query OK, 0 rows affected (0.3252 sec)
 MySQL  localhost:4000 ssl  mydatabase  SQL > insert into t1(c1) values(1);
Query OK, 1 row affected (0.0157 sec)
 MySQL  localhost:4000 ssl  mydatabase  SQL > select * from t1;
+----+
| c1 |
+----+
|  1 |
+----+
1 row in set (0.0037 sec)

OKですね。

続いては、別のサーバーからアクセスしてみましょう。

$ mysqlsh myuser@172.17.0.2:4000/mydatabase --sql

アクセスできました。

 MySQL  172.17.0.2:4000 ssl  mydatabase  SQL > select * from t1;
+----+
| c1 |
+----+
|  1 |
+----+
1 row in set (0.0018 sec)

ここで、接続しているユーザーを確認しておきます。

 MySQL  localhost:4000 ssl  mydatabase  SQL > show full processlist;
+------------+--------+------------------+------------+---------+------+------------+-----------------------+
| Id         | User   | Host             | db         | Command | Time | State      | Info                  |
+------------+--------+------------------+------------+---------+------+------------+-----------------------+
| 3047161876 | myuser | 172.17.0.3:38318 | mydatabase | Sleep   |   58 | autocommit | NULL                  |
| 3047161872 | myuser | [::1]:47606      | mydatabase | Query   |    0 | autocommit | show full processlist |
+------------+--------+------------------+------------+---------+------+------------+-----------------------+
2 rows in set (0.0008 sec)

よさそうです。

ところで

ところで、rootユーザーのパスワードは設定されていません。

なので、以下のコマンドでなにも聞かれずにログインできてしまいます。
※root:@の:の後はパスワードです

$ mysqlsh root:@localhost:4000 --sql

以下の指定にするとパスワードは聞かれますが、そのままEnterを押すとログインできてしまいます。

$ mysqlsh root@localhost:4000 --sql

というわけで、設定しておきましょう。

 MySQL  localhost:4000 ssl  SQL > set password='root-password';
Query OK, 0 rows affected (0.1260 sec)

SET PASSWORD | TiDB SQL Statement Reference | PingCAP Docs

こんなところでしょうか。

おわりに

TiDBでユーザーを作成してみました。MySQLと変わりませんね。

という一方で、ドキュメントのたどり方というか、どこになにがあるかがまだよくわかりませんね…。
こういうテーマをこなしつつ、覚えていきましょう。