LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@dennis_wang(dennis wang)

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

Last updated at Posted at 2022-07-26

PolarDB-Xとは

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

PolarDB-Xの分離レベル

日本語にまとめると：

  • 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

データの準備

  • ローカルから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と同じ動きです。
1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?