MySQL vs. TiDB-分散トランザクションの比較検証(11): 同時実行制御(MySQL)

##### [前回] [MySQL vs. TiDB-分散トランザクションの比較検証(10): 同時実行制御(TiDB)](https://qiita.com/mingchun_zhao/items/081fed8c7b6fd936c939)

## はじめに
今回はMySQLに対し、XA分散トランザクションの同時実行制御検証です。

## まずは、トランザクション分離レベルの理解から

複数トランザクションから同時にデータ更新が行われ競合発生した場合、
分離レベルにより、どこまでデータ整合性が担保されるかが決まるようです。

- Wikipediaから、[トランザクション分離レベルとは](https://ja.wikipedia.org/wiki/%E3%83%88%E3%83%A9%E3%83%B3%E3%82%B6%E3%82%AF%E3%82%B7%E3%83%A7%E3%83%B3%E5%88%86%E9%9B%A2%E3%83%AC%E3%83%99%E3%83%AB)
    - データベース管理システム(RDBMS)上で、一括処理(トランザクション)が複数同時に行われた場合
    - どれほどの一貫性、正確性で実行するかを4段階で定義したもの
    - 隔離レベル、独立性レベルとも呼ばれる
    - トランザクションを定義づけるACID特性のうち、`I`(Isolation、分離性, 独立性)に関する概念である

- `ANSI SQL-92`標準で定義されて四つの分離レベル
    - Read Uncommitted
    - Read Committed
    - Repeatable Read
    - Serializable

- MySQLの分離レベル
    - InnoDBのデフォルト分離レベルは`REPEATABLE READ`
    - `SQL-92`標準に記載された4つのトランザクション分離レベルがすべて提供される
- TiDBの分離レベル
    - MySQLとの互換性のため`Repeatable Read`として知られている`Snapshot Isolation(SI)`レベルの整合性を実装
    - [ANSIやMySQLの`Repeatable Read`レベルとの差異あり](https://docs.pingcap.com/tidb/stable/transaction-isolation-levels#difference-between-tidb-and-ansi-repeatable-read)

## 検証シナリオ(前回TiDB検証と同じ)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2523098/20dcbf76-9ef7-18a8-d10b-dd0344545490.png)
同時に、二つの分散トランザクションから`振込み`ボタンが押された、
といったシナリオです。
- トランザクションAは、A銀行口座からB銀行口座へ10万円振込む
- トランザクションBは、B銀行口座からA銀行口座へ10万円振込む

## 検証環境
```
CPU: Intel(R) Core(TM) 3.30GHz(4コア8スレッド)
メモリ: 16 GB
OS: Ubuntu 20.04 LTS
DB: MySQL 8.0.28
```

ちなみに、Ubuntuは、Windows 10で以下のように管理者権限で導入しました。

```
> wsl --install -d Ubuntu-20.04
```

## 検証準備
- 端末を三つ用意します
    - 端末1: MySQL起動と準備作業
    - 端末2: トランザクションAを実行
    - 端末3: トランザクションBを実行
### 端末1から、MySQLを起動
- MySQLインストールおよびXAに必要な設定
以前の関連記事[XAの検証](https://qiita.com/mingchun_zhao/items/803bd99181974f63f4ce#%E3%81%84%E3%82%88%E3%81%84%E3%82%88xa%E3%81%AE%E6%A4%9C%E8%A8%BC%E3%81%A7%E3%81%99)をご参照ください。
- MySQLを起動
```
$ sudo service mysql start
```
### データベースとテーブルを作成

※ 前提: MySQLの作業ユーザ(今回は`user`)に、データベース作成などの権限が必要です。
今回は検証のため、便宜上すべての権限を付与:
```
mysql> GRANT ALL PRIVILEGES ON * . * TO 'user'@'localhost';
```

- MySQLに接続
```
$ sudo mysql -u user -p
```
- トランザクション分離レベルを確認
```
mysql> SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;
+--------------------------------+-------------------------+
| @@GLOBAL.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | REPEATABLE-READ         |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)
```
デフォルトの`REPEATABLE-READ`になっています。
- A銀行のデータベースと口座テーブルを作成
銀行データベース: `a_bank`
口座テーブル: `account`
を作成し、レコードをINSERT。
ユーザー: `foo`
残高: 50万円
```
CREATE DATABASE a_bank;
USE a_bank;
CREATE TABLE account(name VARCHAR(10) PRIMARY KEY, balance DECIMAL(10,2));
INSERT INTO account VALUES('foo', 500000);
COMMIT;
```
- B銀行のデータベースと口座テーブルを作成
銀行データベース: `b_bank`
口座テーブル: `account`
を作成し、レコードをINSERT。
ユーザー: `foo`
残高: 50万円
```
CREATE DATABASE b_bank;
USE b_bank;
CREATE TABLE account(name VARCHAR(10) PRIMARY KEY, balance DECIMAL(10,2));
INSERT INTO account VALUES('foo', 500000);
COMMIT;
```
- A、B銀行残高を確認
```
mysql> select * from a_bank.account;
+------+-----------+
| name | balance   |
+------+-----------+
| foo  | 500000.00 |
+------+-----------+
1 row in set (0.00 sec)

mysql> select * from b_bank.account;
+------+-----------+
| name | balance   |
+------+-----------+
| foo  | 500000.00 |
+------+-----------+
1 row in set (0.00 sec)
```
## 検証スタート
### 端末2のセッションから、トランザクションAの振込み開始
- MySQLに接続し、データベース`a_bank`を指定
```
$ sudo mysql -u user -p
mysql> USE a_bank;
```
- トランザクション開始
```
mysql> XA START 'a_bank';
```
- A銀行から10万円引き出す
```
mysql> update a_bank.account set balance=balance-100000 where name='foo';
Rows matched: 1  Changed: 1  Warnings: 0
```
- B銀行に10万円振込む
```
mysql> update b_bank.account set balance=balance+100000 where name='foo';
```

### ここで、端末3のセッションから、トランザクションBの振込み開始
- MySQLに接続し、データベース`b_bank`を指定
```
$ sudo mysql -u user -p
mysql> USE b_bank;
```
- トランザクション開始
```
mysql> XA START 'b_bank';
```
- B銀行から10万円引き出す
```
mysql> update b_bank.account set balance=balance-100000 where name='foo';
# 1分程、待ち状態。。。
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
```
`UPDATE`がロック待ち状態となり、1分後ぐらい経つとタイムアウトします。
相手(トランザクションA)の更新中は、ロック待ちに陥りますね。

### 端末2のセッションから、トランザクションAをIDLE状態に
- `XA END`実行し、トランザクションをIDLE状態に
```
mysql> XA END 'a_bank';
```
### 再度、端末3のセッションから、トランザクションBを続行
- B銀行から10万円引き出す
```
mysql> update b_bank.account set balance=balance-100000 where name='foo';
# 1分程、待ち状態。。。
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
```
ロック待ちの状況は変わりません。

### 端末2のセッションから、トランザクションAをPREPARED状態に
- `XA PREPARE`実行し、トランザクションを準備
```
mysql> XA PREPARE 'a_bank';
Query OK, 0 rows affected (0.02 sec)
```
- `XA RECOVER`実行し、PREPARED状態のXAトランザクションを確認
```
mysql> XA RECOVER;
+----------+--------------+--------------+--------+
| formatID | gtrid_length | bqual_length | data   |
+----------+--------------+--------------+--------+
|        1 |            6 |            0 | a_bank |
+----------+--------------+--------------+--------+
1 row in set (0.00 sec)
```
### 再度、端末3のセッションから、トランザクションBを続行
- B銀行から10万円引き出す
```
mysql> update b_bank.account set balance=balance-100000 where name='foo';
# 1分程、待ち状態。。。
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
```
今度も、ロック待ちの状況は変わりません。
### 端末2のセッションから、トランザクションAをコミット
- `XA COMMIT`実行し、トランザクションをコミット
```
mysql> XA COMMIT 'a_bank';
```
- 再度`XA RECOVER`実行し、PREPARED状態のXAトランザクションを確認
```
mysql> XA RECOVER;
Empty set (0.00 sec)
```
コミットされたので、PREPARED状態のXAトランザクションは存在しません。
- A、B銀行残高を確認
```
mysql> select * from a_bank.account;
+------+-----------+
| name | balance   |
+------+-----------+
| foo  | 400000.00 |
+------+-----------+
1 row in set (0.00 sec)

mysql> select * from b_bank.account;
+------+-----------+
| name | balance   |
+------+-----------+
| foo  | 600000.00 |
+------+-----------+
1 row in set (0.00 sec)
```
A銀行からB銀行へ10万円の振込み成功しています。

### 再度、端末3のセッションから、トランザクションBの振込み実施
- B銀行から10万円引き出す
```
mysql> update b_bank.account set balance=balance-100000 where name='foo';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
```
別のトランザクションAがコミットされた後ですので、
問題なくUPDATE実行できました。
- A銀行に10万円振込む
```
mysql> update a_bank.account set balance=balance+100000 where name='foo';
```
- `XA END`実行し、トランザクションをIDLE状態に
```
mysql> XA END 'b_bank';
```
- `XA PREPARE`実行し、トランザクションを準備
```
mysql> XA PREPARE 'b_bank';
```
- `XA RECOVER`実行し、PREPARED状態のXAトランザクションを確認
```
mysql> XA RECOVER;
+----------+--------------+--------------+--------+
| formatID | gtrid_length | bqual_length | data   |
+----------+--------------+--------------+--------+
|        1 |            6 |            0 | b_bank |
+----------+--------------+--------------+--------+
1 row in set (0.00 sec)
```

- `XA COMMIT`実行し、トランザクションをコミット
```
mysql> XA COMMIT 'b_bank';
Query OK, 0 rows affected (0.02 sec)
```
- A、B銀行残高を確認
```
mysql> select * from a_bank.account;
+------+-----------+
| name | balance   |
+------+-----------+
| foo  | 500000.00 |
+------+-----------+
1 row in set (0.00 sec)

mysql> select * from b_bank.account;
+------+-----------+
| name | balance   |
+------+-----------+
| foo  | 500000.00 |
+------+-----------+
1 row in set (0.00 sec)
```
銀行Bから銀行Aへ10万円の振込み成功しています。
二つのトランザクション実行結果、
A、B銀行残高ともに元とおりに戻ってしまいましたけど。

## 終わりに

MySQLに対して、XA分散トランザクションの同時実行制御を検証しました。
トランザクション分離レベルも理解しました。
次回は、分散トランザクションのデッドロック(deadlock)を検証します。
お楽しみに。

##### [次回] [MySQL vs. TiDB-分散トランザクションの比較検証(12): デッドロック(TiDB)](https://qiita.com/mingchun_zhao/items/ccbbd39955b6b762ab07)