PolarDB-Xの分離レベルを確認してみる


# PolarDB-Xとは
PolarDB-XはAlibaba Cloudで提供されているクラウドネイティブ分散型RDBです。
GCPのSpannerやPingCAPのTiDBと同じカテゴリの水平方向にスケーリングするNewSQLに分類されるデータベースです。

# PolarDB-Xの分離レベル
https://www.alibabacloud.com/help/en/polardb-x/latest/compatibility-with-the-mysql-ecosystem

日本語にまとめると：
- MySQLと同じように４つの分離レベルがサポートできる
    - READ UNCOMMITTED
    - READ COMMITTED
    - REPEATABLE READ
    - SERIALIZABLE
- デフォルトの分離レベルは`Repeatable Read`です

MySQLの分離レベルについては以下をご参考ください。
https://browser.alibaba-inc.com/?Url=https%3A%2F%2Fblog.amedama.jp%2Fentry%2Fmysql-innodb-tx-iso-levels

# PolarDB-Xの分離レベルの検証
## 前提：
- Alibaba Cloudのコンソール画面上ですでにPolarDB-Xのインスタンスが作られている
- インターネットからアクセスできるアドレスがすでに作られている
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/28664/d3bc2136-c12f-d820-5b5a-1d0f86c07237.png)
## データの準備
- ローカルからPolarDB-Xにログインする
```
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11206986
Server version: 5.6.29 Tddl Server (ALIBABA)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
```
- database作成する
```
mysql> CREATE DATABASE transfer_test MODE='AUTO';
```
- `account`と`user`のテーブルを作成する
```
use transfer_test;

CREATE TABLE account (
   pk bigint not null auto_increment,
   id varchar(64) not null,
   balance double not null,
   gmt_create timestamp default current_timestamp,
   gmt_modified timestamp default current_timestamp on update current_timestamp,
   PRIMARY KEY(pk),
   key(id)
);

CREATE TABLE user (
   pk bigint not null auto_increment,
   name varchar(64) not null,
   addr varchar(128) not null,
   gmt_create timestamp default current_timestamp,
   gmt_modified timestamp default current_timestamp on update current_timestamp,
   PRIMARY KEY(pk),
   key(name)
);
```
- データをinsertする
```
mysql> INSERT INTO user(name, addr) VALUES("Alice", "hz"), ("Bob", "sh");

mysql> INSERT INTO account(id, balance) VALUES("Alice", 100), ("Bob", 100);

mysql> select * from user;
+------+-------+------+---------------------+---------------------+
| pk   | name  | addr | gmt_create          | gmt_modified        |
+------+-------+------+---------------------+---------------------+
|    1 | Alice | hz   | 2022-07-26 15:38:04 | 2022-07-26 15:38:04 |
|    2 | Bob   | sh   | 2022-07-26 15:38:04 | 2022-07-26 15:38:04 |
+------+-------+------+---------------------+---------------------+

mysql> select * from account;
+------+-------+---------+---------------------+---------------------+
| pk   | id    | balance | gmt_create          | gmt_modified        |
+------+-------+---------+---------------------+---------------------+
|    2 | Bob   |   100.0 | 2022-07-26 15:38:08 | 2022-07-26 15:38:08 |
|    1 | Alice |   100.0 | 2022-07-26 15:38:08 | 2022-07-26 15:38:08 |
+------+-------+---------+---------------------+---------------------+
```
- デフォルト分離レベルの確認
```
SELECT @@global.transaction_isolation,@@session.transaction_isolation,@@tx_isolation;
+--------------------------------+---------------------------------+-----------------+
| @@global.transaction_isolation | @@session.transaction_isolation | @@tx_isolation  |
+--------------------------------+---------------------------------+-----------------+
| REPEATABLE-READ                | REPEATABLE-READ                 | REPEATABLE-READ |
+--------------------------------+---------------------------------+-----------------+
```

## プロンプトを2つ用意
ここからは複数のトランザクションを扱うので、ターミナルを 2 つ用意してどちらも MySQL サーバにログインしておく。
それぞれのプロンプトを区別するために、次のようにして表示を切りかえる。 
- ひとつ目は "mysql1" という名前にする。
```
mysql1> PROMPT mysql1>
PROMPT set to 'mysql1>'
mysql1>
```
- もうひとつ目は "mysql2" という名前にする。
```
mysql> PROMPT mysql2>
PROMPT set to 'mysql2>'
mysql2>
```
# コミットされた結果は他のセッションに反映されることを確認
## mysql1
```
mysql1>use transfer_test;
mysql1>BEGIN;
mysql1>UPDATE account SET balance = balance - 30 WHERE id = "Alice";
mysql1>SELECT * FROM account;
+------+-------+---------+---------------------+---------------------+
| pk   | id    | balance | gmt_create          | gmt_modified        |
+------+-------+---------+---------------------+---------------------+
|    2 | Bob   |   100.0 | 2022-07-26 15:38:08 | 2022-07-26 15:38:08 |
|    1 | Alice |    70.0 | 2022-07-26 15:38:08 | 2022-07-26 15:43:02 |
+------+-------+---------+---------------------+---------------------+
```
## mysql2
- mysql1のトランザクションがRUNNINGであることが分かる
```
mysql2>SHOW TRANS;
+------------------+------+-------------+---------+------------+
| TRANS_ID         | TYPE | DURATION_MS | STATE   | PROCESS_ID |
+------------------+------+-------------+---------+------------+
| 14a8ea1e25802000 | TSO  |       76304 | RUNNING |   10956289 |
+------------------+------+-------------+---------+------------+

mysql2>SELECT * FROM account;
+------+-------+---------+---------------------+---------------------+
| pk   | id    | balance | gmt_create          | gmt_modified        |
+------+-------+---------+---------------------+---------------------+
|    1 | Alice |   100.0 | 2022-07-26 17:15:50 | 2022-07-26 17:15:50 |
|    2 | Bob   |   100.0 | 2022-07-26 17:15:50 | 2022-07-26 17:15:50 |
+------+-------+---------+---------------------+---------------------+

```
## mysql1
```
mysql1>ROLLBACK;

mysql1>SELECT * FROM account;
+------+-------+---------+---------------------+---------------------+
| pk   | id    | balance | gmt_create          | gmt_modified        |
+------+-------+---------+---------------------+---------------------+
|    2 | Bob   |   100.0 | 2022-07-26 17:15:50 | 2022-07-26 17:15:50 |
|    1 | Alice |   100.0 | 2022-07-26 17:15:50 | 2022-07-26 17:15:50 |
+------+-------+---------+---------------------+---------------------+
```
## mysql2
- mysql1のトランザクションが終了したことが分かる
```
mysql2>SHOW TRANS;
Empty set (0.08 sec)

mysql2>SELECT * FROM account;
+------+-------+---------+---------------------+---------------------+
| pk   | id    | balance | gmt_create          | gmt_modified        |
+------+-------+---------+---------------------+---------------------+
|    1 | Alice |   100.0 | 2022-07-26 17:15:50 | 2022-07-26 17:15:50 |
|    2 | Bob   |   100.0 | 2022-07-26 17:15:50 | 2022-07-26 17:15:50 |
+------+-------+---------+---------------------+---------------------+
```

## mysql1
```
mysql1>BEGIN;

mysql1>UPDATE account SET balance = balance - 30 WHERE id = "Alice";

mysql1>UPDATE account SET balance = balance + 30 WHERE id = "Bob";

mysql1>SELECT * FROM account;
+------+-------+---------+---------------------+---------------------+
| pk   | id    | balance | gmt_create          | gmt_modified        |
+------+-------+---------+---------------------+---------------------+
|    2 | Bob   |   130.0 | 2022-07-26 17:15:50 | 2022-07-26 17:50:52 |
|    1 | Alice |    70.0 | 2022-07-26 17:15:50 | 2022-07-26 17:50:52 |
+------+-------+---------+---------------------+---------------------+
```

## mysql2
- mysql1のトランザクションはまだcommittしていないため、mysql2のselect結果は変わっていない
```
mysql2>SHOW TRANS;
+------------------+------+-------------+---------+------------+
| TRANS_ID         | TYPE | DURATION_MS | STATE   | PROCESS_ID |
+------------------+------+-------------+---------+------------+
| 14a8eb70b2802000 | TSO  |       70390 | RUNNING |   10956289 |
+------------------+------+-------------+---------+------------+
1 row in set (0.08 sec)

mysql2>SELECT * FROM account;
+------+-------+---------+---------------------+---------------------+
| pk   | id    | balance | gmt_create          | gmt_modified        |
+------+-------+---------+---------------------+---------------------+
|    2 | Bob   |   100.0 | 2022-07-26 17:15:50 | 2022-07-26 17:15:50 |
|    1 | Alice |   100.0 | 2022-07-26 17:15:50 | 2022-07-26 17:15:50 |
+------+-------+---------+---------------------+---------------------+
```

## mysql1
- mysql1でのコミットはmysql1に反映された。
```
mysql1>COMMIT;

mysql1>SELECT * FROM account;
+------+-------+---------+---------------------+---------------------+
| pk   | id    | balance | gmt_create          | gmt_modified        |
+------+-------+---------+---------------------+---------------------+
|    2 | Bob   |   130.0 | 2022-07-26 17:15:50 | 2022-07-26 17:50:52 |
|    1 | Alice |    70.0 | 2022-07-26 17:15:50 | 2022-07-26 17:50:52 |
+------+-------+---------+---------------------+---------------------+
```
## mysql2
- mysql1でのコミットはmysql2にも反映された。
```
mysql2>SHOW TRANS;
Empty set (0.08 sec)

mysql2>SELECT * FROM account;
+------+-------+---------+---------------------+---------------------+
| pk   | id    | balance | gmt_create          | gmt_modified        |
+------+-------+---------+---------------------+---------------------+
|    2 | Bob   |   130.0 | 2022-07-26 17:15:50 | 2022-07-26 17:50:52 |
|    1 | Alice |    70.0 | 2022-07-26 17:15:50 | 2022-07-26 17:50:52 |
+------+-------+---------+---------------------+---------------------+
```

# 一つのセッションのコミットは別のセッションのコミットに影響しないこと(つまり``Repeatable Read``)を確認
## mysql1
```
mysql1>BEGIN;
Query OK, 0 rows affected (0.08 sec)

mysql1>UPDATE account SET balance = balance - 30 WHERE id = "Alice";
Query OK, 1 row affected (0.09 sec)

mysql1>UPDATE account SET balance = balance + 30 WHERE id = "Bob";
Query OK, 1 row affected (0.10 sec)

mysql1>SELECT * FROM account;
+------+-------+---------+---------------------+---------------------+
| pk   | id    | balance | gmt_create          | gmt_modified        |
+------+-------+---------+---------------------+---------------------+
|    2 | Bob   |   160.0 | 2022-07-26 17:15:50 | 2022-07-26 17:55:10 |
|    1 | Alice |    40.0 | 2022-07-26 17:15:50 | 2022-07-26 17:55:09 |
+------+-------+---------+---------------------+---------------------+
```

## mysql2
- mysql2にもトランザクションを開始する
```
mysql2>BEGIN;

mysql2>SHOW TRANS;
+------------------+------+-------------+---------+------------+
| TRANS_ID         | TYPE | DURATION_MS | STATE   | PROCESS_ID |
+------------------+------+-------------+---------+------------+
| 14a8ec6be9802000 | TSO  |       65125 | RUNNING |   10956289 |
+------------------+------+-------------+---------+------------+

mysql2> SELECT * FROM account;
+------+-------+---------+---------------------+---------------------+
| pk   | id    | balance | gmt_create          | gmt_modified        |
+------+-------+---------+---------------------+---------------------+
|    2 | Bob   |   130.0 | 2022-07-26 17:15:50 | 2022-07-26 17:50:52 |
|    1 | Alice |    70.0 | 2022-07-26 17:15:50 | 2022-07-26 17:50:52 |
+------+-------+---------+---------------------+---------------------+
```

## mysql1
```
mysql1>COMMIT;

mysql1>SELECT * FROM account;
+------+-------+---------+---------------------+---------------------+
| pk   | id    | balance | gmt_create          | gmt_modified        |
+------+-------+---------+---------------------+---------------------+
|    1 | Alice |    40.0 | 2022-07-26 17:15:50 | 2022-07-26 17:55:09 |
|    2 | Bob   |   160.0 | 2022-07-26 17:15:50 | 2022-07-26 17:55:10 |
+------+-------+---------+---------------------+---------------------+
```

## mysql2
- mysql1はコミットされたにも関わらず、mysql2では確認されていない。
- なぜならmysql2は別のトランザクションが実行している途中で、他のトランザクションの結果は自分のトランザクションに反映されないのは`Repeatable Read`の仕様です。
```
mysql2>SELECT * FROM account;
+------+-------+---------+---------------------+---------------------+
| pk   | id    | balance | gmt_create          | gmt_modified        |
+------+-------+---------+---------------------+---------------------+
|    2 | Bob   |   130.0 | 2022-07-26 17:15:50 | 2022-07-26 17:50:52 |
|    1 | Alice |    70.0 | 2022-07-26 17:15:50 | 2022-07-26 17:50:52 |
+------+-------+---------+---------------------+---------------------+

mysql2>ROLLBACK;

mysql2>SELECT * FROM account;
+------+-------+---------+---------------------+---------------------+
| pk   | id    | balance | gmt_create          | gmt_modified        |
+------+-------+---------+---------------------+---------------------+
|    2 | Bob   |   160.0 | 2022-07-26 17:15:50 | 2022-07-26 17:55:10 |
|    1 | Alice |    40.0 | 2022-07-26 17:15:50 | 2022-07-26 17:55:09 |
+------+-------+---------+---------------------+---------------------+
```

# まとめ
- 上記の検証でPolarDB-Xのデフォルト分離レベルは`Repeatable Read`であることが分かった。
- PolarDB-Xの分離はMySQLと同じ動きです。